MYSQL Syntax Error on UPDATE with 'Key' word

Question

I simply cannot get my head around this one.

I am writing a simple PHP script to update a specific database field.

This query works fine, no problems at all, however setting_id is liable to change it seems and so is not suitable/portable

mysql_query("UPDATE oc_setting SET value='" . $css . "'" . 'WHERE setting_id=13576',    $con) or die(mysql_error());

This query, does not seem to work under any circumstances, which is the query I need.

mysql_query("UPDATE oc_setting SET value='" . $css . "'" . 'WHERE key="sellya_custom_css"', $con) or die(mysql_error());

It leaves me this error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key="sellya_custom_css"' at line 1

Now I had a hunch it may have something with the word key perhaps being a reserved keyword or something so I tried wrapping it in quotes, and while the script did not come up with any errors, it did not update the field either.

I have tried countless of combinations with single quotes double quotes, concatenation combinations of various sorts to no avail.

Could someone please help me out here?

Thank you.

`key` is a reserved word and should have back ticks if you still want to use it — Hanky Panky, Apr 08 '13 at 04:31
Sorry, no clue. I didn't down vote. but a down vote is understandable since you are yourself saying it might be a problem with reserved words, but you didn't search Google for what to do when you have to use a reserved word in a query — Hanky Panky, Apr 08 '13 at 05:20

Jhonathan H. · Accepted Answer · 2013-04-08T05:17:26.907

5

KEY is a reserve word in mysql

better put KEY inside `` back ticks.

Source(link)

edited Apr 08 '13 at 05:17

answered Apr 08 '13 at 04:29

Jhonathan H.

2,734
1
19
28

perfect thank you! I thought that might be the case, but I was not aware that I had to use special quotes (``) to wrap it. – Melbourne2991 Apr 08 '13 at 04:32
@user1650757 yes it perfectly good to practice having it.. :D – Jhonathan H. Apr 08 '13 at 04:33
1

you should be referencing partitioning key but instead the list of reserved keywords in mysql. [MySQL Reserved Keywords List](http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html) – John Woo Apr 08 '13 at 04:35

score 2 · Answer 2 · edited May 23 '17 at 12:21

2

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use pdo or mysqli.

Now, to the question:

"UPDATE oc_setting SET value='" . $css . "'" . 'WHERE key="sellya_custom_css"'

in this query string, you are missing a space before WHERE. Change it to:

"UPDATE oc_setting SET value='" . $css . "'" . ' WHERE `key`="sellya_custom_css"'

key needs to be escaped in the query too.

edited May 23 '17 at 12:21

Community

1
1

answered Apr 08 '13 at 04:29

hjpotter92

78,589
36
144
183

Ok ill take the time to look into pdo and mysqli, after adding the space, I am still getting the same error. – Melbourne2991 Apr 08 '13 at 04:30

MYSQL Syntax Error on UPDATE with 'Key' word

2 Answers2