MySQL SELECT data when the field name is 'order'

[Manion]

I'm using PHP code to extract data from a MySQL database. One of the field names in the database uses the name 'order'. Unfortunately, I need to ORDER BY 'order'. I get SQL syntax errors if I try to SELECT or ORDER BY 'order'.

I can't rename the field. Is there a way to get MySQL to accept this field name in a query?

 

[vacunita]

Order is a reserved word as you have seen. you should avoid using it if you can as it will just cause additional head aches. But if that is just not possible you'll need to surround it with back ticks.

... ORDER BY `order` ...

Note back ticks are not the same thing as single quotes.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ingresman]

You could try select order as oo from.... order by oo;
I presume you have to backtick order in any query ?
It makes me wonder sometimes why people use reserved words?, or (whie I'm at it !) people put spaces in table and column names in access data bases !

 

[guelphdad]

ingresman that won't work, it will throw an error because you can't have the reserved word ORDER before the FROM clause.

 

[ingresman]

Sorry should have been back earlier, Yes I did say I presumed that you has to backtick order in any query so this works:

mysql> select `order` as oo from reserved order by oo;
+----+
| oo |
+----+
| 1 |
+----+
1 row in set (0.00 sec)

But then again

mysql> select `order` from reserved order by `order`;
+-------+
| order |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

As I say why does any one use reserved words !!??

 

[guelphdad]

As I say why does any one use reserved words !!??

Obvious, they've never visited that page in the manual.

 

[ingresman]

sighhhhhh

 

[pointertowhat]

It's great fun when it happens - had to work on a db where someone had named a column 'Date'.