-4

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?

Anthony Pegram
  • 123,721
  • 27
  • 225
  • 246
ZiggidyCreative
  • 335
  • 3
  • 16
  • 5
    Please stop using `mysql_*` functions. They are deprecated. Use MySQLi or PDO instead. Also, please read MySQL documentation that corresponds mysql version installed on your server to learn how queries should look like. Also try using `mysql_error` to get error message. – Robik May 22 '12 at 17:23
  • You need to provide more information than what you have. What errors are you getting? – j08691 May 22 '12 at 17:23
  • You should give more details on what you are trying to do. And give proof that you've researched for a solution before asking. – Xtian Macedo May 22 '12 at 17:23
  • 5
    1) First off 1st post give the new guy a break. educate/help don't criticize. I'm sure you enjoy being told not to use * in select statements. 2) address the problem. it's a simple select; there can't be much going wrong. As far as the question being closed and the reason: GOOD GRIEF. people run into reserved word issues ALL the time. This WILL likely help someone in the future. – xQbert May 22 '12 at 17:30
  • @xQbert I do disagree with the downvotes, and I think this is a legit question. However, in it's current state, I don't know if this question will help anyone else in the future. The title is unlikely to turn up in a good search. If you could add the error message that this code generates that might be something that would salvage it. – Bill the Lizard May 22 '12 at 17:56
  • 1
    This has sparked a [meta discussion](http://meta.stackexchange.com/questions/175064/useful-question-marked-useless). With the revisions, I think it's worthy of reopening (and I have voted to do so) because it's such a common syntax error. But prior to that, it was simply not a good question and it was correctly closed. – Tim M. Apr 04 '13 at 05:29

3 Answers3

7

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

Ashwani Tiwari
  • 1,497
  • 18
  • 28
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • The real question is, why does MySQL allow you to create a table with a reserved keyword for a name when you won't be able to use it? – Bill the Lizard May 22 '12 at 17:57
  • 2
    @BilltheLizard You can use it, you just have to escape it using the back tick `. – xQbert May 22 '12 at 19:55
  • 1
    Ah, I could have found that out on my own if I'd just finished reading your answer. :) Does MySQL force you to use the backtick when you create a table named Order as well? – Bill the Lizard May 22 '12 at 20:01
  • Yes, otherwise you'll have a syntax error, too. – hakre May 22 '12 at 22:04
  • 1
    Pardon me, but I moved the code he used in his question as his solution to your answer which was marked as his accepted answer. Feel free to edit if you disagree with this modification. – Anthony Pegram Apr 04 '13 at 05:08
  • I see no issues with that. my only concern is with the title change people don't know order is a key word and thus often don't know to search for protected key words. thus the original title actually made sense to me. If I knew it was a key word problem, I would know that I needed to likely escape it. since the author didn't know they didn't even know how to start the search; or ask for help. I guess my point is If I had the problem he did, I wouldn't know to use the words you did in the updated title. – xQbert Apr 04 '13 at 17:35
0

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
-1

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

Ajit
  • 336
  • 2
  • 12
  • 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 – ZiggidyCreative May 22 '12 at 17:24
  • As xQbert has mentioned, Order is reserved word mysql_query("SELECT * FROM `order` WHERE orderID = 102;") or die("MySQL ERROR: ".mysql_error()); – Ajit May 22 '12 at 17:27