order by ASC
in [General]
|
Prev: Shipping calculator
Next: Zend debugger doesn't work
From: John Taylor-Johnston on 16 Jan 2010 23:52 Ok, I think this is a MySQl question. Take pity on me? $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member', 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC"; rollnumber is a varchar(50). I need it to be a text field. ASC does not order the way I want. 1000 1001 998 999 I want it to order like this: 998 999 1000 1001 How do I trick it? I cannot think of a way in MySQL. Is there a way in PHP?
From: Robert Cummings on 16 Jan 2010 23:58 John Taylor-Johnston wrote: > Ok, I think this is a MySQl question. Take pity on me? > > $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member', > 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC"; > > rollnumber is a varchar(50). I need it to be a text field. ASC does not > order the way I want. > > 1000 > 1001 > 998 > 999 > > I want it to order like this: > > 998 > 999 > 1000 > 1001 > > How do I trick it? I cannot think of a way in MySQL. Is there a way in PHP? CAST it to an integer in the ORDER BY clause. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP
From: John Taylor-Johnston on 17 Jan 2010 00:24 It hates me: SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life Member') ORDER BY CAST(rollnumber AS int) SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life Member') ORDER BY CAST(`rollnumber` AS int) SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life Member') ORDER BY CAST('rollnumber' AS int) 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 'int) ASC' at line 1 I'll keep trying. Robert Cummings wrote: > CAST it to an integer in the ORDER BY clause. > > Cheers, > Rob. > > John Taylor-Johnston wrote: >> Ok, I think this is a MySQl question. Take pity on me? >> >> $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member', >> 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC"; >> >> rollnumber is a varchar(50). I need it to be a text field. ASC does >> not order the way I want. >> >> 1000 >> 1001 >> 998 >> 999 >> >> I want it to order like this: >> >> 998 >> 999 >> 1000 >> 1001 >> >> How do I trick it? I cannot think of a way in MySQL. Is there a way >> in PHP?
From: John Taylor-Johnston on 17 Jan 2010 00:28 Did some googling. This worked: ORDER BY CAST(`rollnumber` AS SIGNED) What is the difference? My problem in the meanwhile must be my version of MySQL? John Taylor-Johnston wrote: > It hates me: > > SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life > Member') ORDER BY CAST(rollnumber AS int) > SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life > Member') ORDER BY CAST(`rollnumber` AS int) > SELECT * FROM ... WHERE `type` IN ('Member', 'Affiliated', 'Life > Member') ORDER BY CAST('rollnumber' AS int) > > 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 'int) ASC' at line 1 > > I'll keep trying. > > Robert Cummings wrote: >> CAST it to an integer in the ORDER BY clause. >> >> Cheers, >> Rob. >> >> John Taylor-Johnston wrote: >>> Ok, I think this is a MySQl question. Take pity on me? >>> >>> $sql = "SELECT * FROM $db.`mailinglist` WHERE `type` IN ('Member', >>> 'Affiliated', 'Life Member') ORDER BY `rollnumber` ASC"; >>> >>> rollnumber is a varchar(50). I need it to be a text field. ASC does >>> not order the way I want. >>> >>> 1000 >>> 1001 >>> 998 >>> 999 >>> >>> I want it to order like this: >>> >>> 998 >>> 999 >>> 1000 >>> 1001 >>> >>> How do I trick it? I cannot think of a way in MySQL. Is there a way >>> in PHP? > >
From: Robert Cummings on 17 Jan 2010 00:37
John Taylor-Johnston wrote: > Did some googling. This worked: > ORDER BY CAST(`rollnumber` AS SIGNED) > > What is the difference? My problem in the meanwhile must be my version > of MySQL? You could have skipped quotes altogether. The difference is that you are referencing a field name, not a string value. Cheers, Rob. -- http://www.interjinn.com Application and Templating Framework for PHP |