Search code examples
phpmysqlwordpressquotation-marks

MySQL Update Returning Failed

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?

Solution

  • 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.