If you need to sort varchar data as int, there is one very nice function in MySQL. Problem I had to solve was to sort values as below in a human logical way. Values were similar to this (and I wanted it to be sorted like below):
1; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7; 11; 14; 14a; 16;
The problem was that if I put this data to MySQL database as varchar and sort by this column, MySQL returned it this way:
1; 11; 14; 14a; 16; 2; 2a; 3; 4; 5; 5a; 5b; 5c; 6; 7;
My second thought was to put is as INTEGER values… It would be sorted as it should but then I would loose values like
2a; 5a; 5b; 5c; 14a;
as it would be converted by MySQL to integer values (2; 5; 14;).
Then I have thought it would be best to put this data to database as a varchar values and sort it as int! After some googling I have found great function that have helped me! The solution was CAST function. To sort it the way I wanted I have just added to my MySQL query this sorting method (pic_number is my field with value to sort):
ORDER BY CAST(`pic_number` AS SIGNED)
and for reverse order
ORDER BY CAST(`pic_number` AS SIGNED) DESC
And it worked like a charm!
Function is as simple to use as:
CAST(expr AS TYPE)
Other possible conversion types you may need are:
- SIGNED [INTEGER]
- UNSIGNED [INTEGER]
You may read more about this types in MySQL docs here. This function saved me a lot of coding in PHP. I hope it have helped you as much as it helped me!