MySQL Update Returning Failed

Question

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?

I have also tried changing the syntax many times (replacing ` with ', etc) — Devanarayanan, Mar 09 '22 at 10:09
NB period looks like it is enclosed in single quotes making it a string when it should be enclosed in bacticks (or nothing) to identify a column — P.Salmon, Mar 09 '22 at 10:16
You need to read this, and then check your own code carefully: [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) . N.B. P.Salmon's comment has already given you the solution, but you should read this link to give you proper understanding overall. — ADyson, Mar 09 '22 at 10:19
@P.Salmon My update query is now added. Changed period into `period` still doesn't work. Now at shows error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '2021 – Nov 2021’ AND (total <> `0`)' at line 1 — Devanarayanan, Mar 09 '22 at 10:43
SET total = '0’ - total isn't defined in the published table.. and ‘Sep 2021 – Nov 2021’ the quotes don't look like single quotes. — P.Salmon, Mar 09 '22 at 10:44
I don' think its syntax error, because at first I used $wpdb->update. WordPress automatically generates query — Devanarayanan, Mar 09 '22 at 10:45
you also tried to run the same query from phpMyAdmin which also fails. - is it still failing? — P.Salmon, Mar 09 '22 at 10:46
I think SELECT COUNT(*) FROM \`utility_period\` WHERE \`period\` = 'Sep 2021 - Nov 2021'; it should work — Aravind Emmadishetty, Mar 09 '22 at 10:55

score 0 · Answer 1 · answered Mar 09 '22 at 11:28

Thanks, everyone. It's working now. I was not able to identify the issue, but now again used wpdb and the issue was resolved.

         $replace = [
             "total" => $new_total
           ];
  
        $where = ["period" => $period];
  
        $update = $wpdb->update("utility_period", $replace, $where);

O. Jones · Accepted Answer · 2022-03-09T11:41:46.357

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

 UPDATE `utility_period` SET total = '0’ WHERE `period` = ‘Sep 2021 – Nov 2021’

should be this

 UPDATE `utility_period` SET `total` = '0' WHERE `period` = 'Sep 2021 – Nov 2021'

or this

 UPDATE utility_period SET total = '0' WHERE period = 'Sep 2021 – Nov 2021'

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.

MySQL Update Returning Failed

2 Answers2