How can I write SQL for a table that shares the sa

Question

This question already has an answer here:

• Syntax error due to using a reserved word as a table or column name in MySQL 1 answer

The following query will not execute

mysql_query("SELECT * FROM order WHERE orderID = 102;");

It produces the following error message:

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 'order WHERE orderID = 102' at line 2

How can I write SQL that will successfully query this table?

Answer 1

Drives me crazy that people assume queries will work, and then don't even bothering asking the database to explain why things blew up. Try this:

$result = mysql_query("SELECT * FROM order WHERE orderID = 102;") or die(mysql_error());

as well, unless there's more code than what you're showing above, you HAVE to capture the return value of the query call, since that return value is your result handle, from which you fetch results. Without that statement handle, you're basically wasting the DB server's (and your own) time.

Answer 2

Try
mysql_query("SELECT * FROM order WHERE orderID = 102;") or die("MySQL ERROR: ".mysql_error());

Answer 3

Order is a reserved word. Don't use reserved words as table or field names; or wrap it in the escape characters such as ` for mysql. Personally I just avoid using them as they generally cause more headache than they are worth in the long run.

Example:

mysql_query("SELECT * FROM `order` WHERE orderID = 102;");

MORE INFO - you can get more info about reserved word here https://dev.mysql.com/doc/refman/5.5/en/keywords.html