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:

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?

CodePudding user response：

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);

CodePudding user response：

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.