MySQL: Having A Column Name With A Reserved Keyword Is A Tricky Business

Next time you wonder why your insert into sql statement isn’t working, it might be due to the fact that you’re using a reserved keywords for your table’s column name.

First, an example. All variables inside values() are predefined before they are being used.

$query = "insert into products (NAME,SALEPRICE,PRICE,RETAILPRICE,BUYURL,CONDITION) 
values('$NAME','$SALEPRICE','$PRICE','$RETAILPRICE','$BUYURL','$CONDITION') 
ON DUPLICATE KEY UPDATE SALEPRICE='$SALEPRICE', PRICE='$PRICE', RETAILPRICE='$RETAILPRICE', BUYURL='$BUYURL', CONDITION='$CONDITION';";

At first sight, everything seems fine. However, the above SQL statement will return an error when run.

That’s because CONDITION is a reserved mysql keyword!

Since a reserved keyword (CONDITION) was chosen (either intentionally or accidentally) to be a table’s column name, a little more attention is needed when writing a SQL statement.

The Solution
And it’s quite simple anyway. Just encapsulate the reserved keyword with backticks `, ex. `CONDITION`. So the correct, error-free SQL statement above would be as follow:

$query = "insert into products (NAME,SALEPRICE,PRICE,RETAILPRICE,BUYURL,`CONDITION`) 
values('$NAME','$SALEPRICE','$PRICE','$RETAILPRICE','$BUYURL','$CONDITION') 
ON DUPLICATE KEY UPDATE SALEPRICE='$SALEPRICE', PRICE='$PRICE', RETAILPRICE='$RETAILPRICE', BUYURL='$BUYURL', `CONDITION`='$CONDITION';";

I had struggled quite a bit while trying to find out why the first SQL didn’t execute properly. Plus, MySQL error message doesn’t even tell you the fact that a “column with a reserve keyword” wasn’t parsed properly. It all brighten up after reading the mysql list of reserved keywords.

* On a side node, this is also the reason why it’s better to surround your column names with backticks `. I once wonder why tools like phpMyAdmin shows SQL queries with backticks surrounding columns, and now I know why.

Hope this helps.