ORDER BY NUMBER 1,10,11 incorrect

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

ORDER BY NUMBER 1,10,11 incorrect

Post by jonnyfortis »

i want to order an item by the number in the database field 1,2,3,4,5,6 iup to 15 but it is displaying 1,10,11 and up which i dont want

ORDER BY beau_products.order ASC

the order column is varchar (10)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY NUMBER 1,10,11 incorrect

Post by Celauran »

It's using string ordering because you're storing numbers as strings. Don't do that. Use INT, SMALLINT, TINYINT, etc. Use the right column type for your data.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: ORDER BY NUMBER 1,10,11 incorrect

Post by Celauran »

In the interim you can use CAST, but you really want to correct this.

Code: Select all

SELECT foo FROM table_name ORDER BY CAST(`order` AS UNSIGNED) ASC
jonnyfortis
Forum Contributor
Posts: 462
Joined: Tue Jan 10, 2012 6:05 am

Re: ORDER BY NUMBER 1,10,11 incorrect

Post by jonnyfortis »

Celauran wrote:In the interim you can use CAST, but you really want to correct this.

Code: Select all

SELECT foo FROM table_name ORDER BY CAST(`order` AS UNSIGNED) ASC
thanks. i will change the column to int, thanks again
Post Reply