Recently a question popped up over on the CakePHP google group where someone had a problem sorting on a column of “registration numbers”. This was his table sorted order by “Reg No ASC“:

-

ID Name Reg No
1 Product A 1
2 Product B 123
3 Product C 2

-
1, 123, 2. That’s not correct right? Well it is if we are sorting strings! The problem here is that MySQL will not do a “natural sort on a text type column like char, varchar, or text. The solution to this is to change the column type to a numeric value, especially considering he is storing numbers anyway! Once you change “Reg No” to int, the column will sort correctly.

-

ID Name Reg No
1 Product A 1
3 Product C 2
2 Product B 123

Always remember to check your schema!

Tagged with: