-2

i am using ORDER BY in mysql SELECT query but i dont know ots not ordering the data.. if i use this query its showing the table but not ordering the data in ascending order

$result = mysql_query("SELECT *FROM learningmaterial ORDER BY 'order' ASC")or   die(mysql_error());

but if i use 

$result = mysql_query("SELECT *FROM learningmaterial ORDER BY order ASC")or die(mysql_error());

then it give error that the syntax of the query is not right...i've seen on various sites but i couldnot found anything unique in my code...i think its right,...please check the query and mend a solution. Thankx in advance :)

trouble creator
  • 127
  • 3
  • 5
  • 11
  • 1
    As everyone are saying, change order to some other name. It is a reserved word – FirmView Oct 06 '12 at 08:30
  • 1
    Keep in mind that the table may _not_ be under the control of the person querying it. Yes, it's a good idea to use column names that aren't reserved but not if it's going to involve a 3-week fight with the DBAs, or changes to 42 other programs that already query the `order` column. – paxdiablo Oct 06 '12 at 08:32
  • @paxdiablo good point, But, If the DBA or anyother person is using keywords for naming columns i will fight for whole life – FirmView Oct 06 '12 at 08:34
  • Mess not with DBAs because they're sensitive and quick to anger :-) And do you really want to anger someone who can hurt you with a `drop table` incantation? – paxdiablo Oct 06 '12 at 08:35
  • possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) – Ian Ringrose May 06 '14 at 10:18

4 Answers4

3

You need backticks, not single quotes (a):

... SELECT * FROM learningmaterial ORDER BY `order` ASC ...

By using single quotes, you're ordering the rows by a constant (each row gets the same constant) so effectively not ordering them at all.

By using a "naked" column name of order, you're confusing the SQL parser, since order is a reserved word.

(a): Of course, this problem goes away if you stop using reserved words as column names but I assume you did that for a reason (such as a bucket-load of programs already depending on the fact that the column is called order).

Myself, I tend not to use generic names for columns (such as order or date), preferring instead things that don't conflict with the language (such as order_num or start_date). That way I don't have to worry about escaping.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
1

You are using SQL reserved keyword order as a column name so use back-ticks to escape...like this

SELECT * FROM learningmaterial ORDER BY `order` ASC

I would suggest you to change the columnn name

Reference For List Of Reserved Keywords

Mr. Alien
  • 153,751
  • 34
  • 298
  • 278
  • anyone please tell me y im getting negative points for this question.. i knw im not a good coder.. but im a beginner..please tell me how can i get upvotes on this question, thanx :) – trouble creator Nov 07 '12 at 18:09
1

ORDER is a reserved sql syntax keyword. you cannot use it directly

SELECT *FROM learningmaterial ORDER BY `order` ASC
-------------------------------^---------

in second case

SELECT *FROM learningmaterial ORDER BY order ASC
---------------------------------^-------^--
//this is a sql error

it doesn't make any sense.

StaticVariable
  • 5,253
  • 4
  • 23
  • 45
1

Since Order is a reserved word, you need to wrap them using backticks not single quotes.

SELECT * FROM `learningmaterial` ORDER BY `order` ASC
Praveen Kumar Purushothaman
  • 164,888
  • 24
  • 203
  • 252
  • 5
    Aren't you just repeating what all the earlier answers said? – Barmar Oct 06 '12 at 08:36
  • Thaaanxxx Awlll..... im just a beginner..but i should know these things..i dont know it just slipped out of my mind... any ways thnx alot :) – trouble creator Oct 06 '12 at 10:34
  • 1
    anyone please tell me y im getting negative points for this question.. i knw im not a good coder.. but im a beginner..please tell me how can i get upvotes on this question, thanx :) – trouble creator Nov 07 '12 at 18:08