I am running a MySQL update query, but it always fails. I am using WordPress, I also tried to run the same query from phpMyAdmin which also fails.
This is my table:
id | period | x | y |
---|---|---|---|
1 | Sep 2021 – Nov 2021 |
I tried running get count:
SELECT COUNT(*) FROM `utility_period` WHERE 'period' = 'Sep 2021 - Nov 2021'
This returns 0 while the record exists in the table.
UPDATE Query:
UPDATE `utility_period` SET total = '0’ WHERE `period` = ‘Sep 2021 – Nov 2021’
Why is this happening?
2
Answers
Thanks, everyone. It's working now. I was not able to identify the issue, but now again used wpdb and the issue was resolved.
tl;dr:
'
is not‘
or’
. They’re hard to tell apart, but as a programmer you must.In MySQL, string literals are enclosed in single-quotes
'
.In this query you got skunked by a "helpful" word processing program that replaced those single quotes with
‘
and’
. They’re very hard to tell apart, but‘
and’
are not useful as string-enclosing marks in SQL (or in most programming languages).And, the names of databases, tables, and columns are optionally enclosed in backticks, as your query does. The backticks are only required when an object name is also a keyword in SQL.
So this
should be this
or this
The SQL phrase
WHERE 'period' = 'Sep 2021 - Nov 2021'
determines whether the text string constant'period'
is equal to the constant'Sep 2021 - Nov 2021'
. It is not. You used'
around that column name rather than backticks.Your use of
$wpdb
means you put the responsibility for handling your string literals on that module. It does it correctly.