explosive web programming w/ j_blotus
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!
If you enjoyed this article, please consider sharing it!
30
