odd error in sql syntax

Question

I have a few lines of code that should make a report about the coupons in OpenCart 1.4.9, but I can't get over 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
 'order where coupon_id = 16' at line 1.

$getcouponinfo1 = mysql_query("select * from order where coupon_id = $coupon_id")
      or die(mysql_error());

The rest of the code can be found here.

score 7 · Accepted Answer · answered Mar 03 '12 at 13:39

7

order is a keyword in SQL, you need to quote that table name.

select * from `order` where ...

answered Mar 03 '12 at 13:39

Mat

202,337
40
393
406

1

take a look at this link. there are some reserved words in the sql. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html so you need to follow @Mat's answer – Subail Sunny Mar 03 '12 at 13:47

score 2 · Answer 2 · answered Mar 03 '12 at 13:38

2

"order" IS A MySQL reserved word... If you need to call your table "order" then you need to enclose it in backticks (`) in your sql queries.

Your error message is not related to the query that you originally posted (prior to editing your question), but to this query:

select * from order where coupon_id = $coupon_id

and to the following query where you sum the value of the order

answered Mar 03 '12 at 13:38

Mark Baker

209,507
32
346
385

(OP didn't post any query at all, I edited it in and messed up the first copy/paste) – Mat Mar 03 '12 at 13:54
Apologies to OP then, and it's the correct query now, so no harm done – Mark Baker Mar 03 '12 at 13:55

score 2 · Answer 3 · edited Mar 04 '12 at 08:44

2

order is a reserved keyword.

You should use `order` instead.

edited Mar 04 '12 at 08:44

Pranav Hosangadi

23,755
7
44
70

answered Mar 03 '12 at 13:39

Sergey Benner

4,421
2
22
29

2

backticks (`) not double quotes (") – Mark Baker Mar 03 '12 at 13:42
@MarkBaker I know i but the backticks are reserved in SO to highlight the text.It would simply highlight it. I tried it first but couldn't find a way to escape it correctly.... – Sergey Benner Mar 03 '12 at 13:48
@SergeyBenner: you can put backticks, but it's a bit convoluted (see my edit) – Mat Mar 03 '12 at 13:53

score -1 · Answer 4 · edited Mar 04 '12 at 09:26

-1

Because order is a reserved word, it is not a good name for a table

edited Mar 04 '12 at 09:26

Pranav Hosangadi

23,755
7
44
70

answered Mar 03 '12 at 13:39

riso

232
1
11

Doesn't really answer the question, does it? – Pranav Hosangadi Mar 04 '12 at 09:26
@PranavHosangadi so, it's better to vote delete this answer, not edit it making whole question active, polluting the list? – Your Common Sense Mar 04 '12 at 09:38

odd error in sql syntax

4 Answers4