PHP sorting problem using SQL "ORDER BY" statement

[peterjcs]

i have a sorting problem in php using the SQL command "ORDER BY".

-----------------
For example:
i want to sort this number descending: 30, 4, 50, 200.

so in actual case we should get: 200 , 50, 30 , 4.

But what i get when using the ORDER BY statement is: 50, 4, 30, 200

-------------------
After look a while at the incorrect result, i think the problem is because it sort using the first digit of the number.

Anyone know how to solve this problem?

Thank in advance.

[Redcore]

Have you tried "ORDER BY $variable DESC" ?

[peterjcs]

Just for your information, i already tried "ORDER BY $variable DESC" ?

Thank for your reply redcore.
-------------------------------------

I have found the solution. I just realize that i am using char datatype for the column. so i use this solution.

-------------------------------------------

To sort character column foo as an integer


ORDER BY foo + 0 DESC


ORDER BY CAST(foo AS UNSIGNED)

What happens when you add 0 to foo is that mysql is forced to convert foo into an integer. After that, it is sorted as an integer. The same happens when you explicitly cast foo to an integer using CAST(). "Cast" means "Change from one type to another".

The original problem is that your numbers were being sorted as strings, which is why it looked at the first character first, then second, and so on.

[KoosHopeloos]

@ peterjcs: thank you for posting the answer to your problem! It might be helpfull for someone with (more or less) the same problem.