CoderYordi CoderYordi - 22 days ago 7
MySQL Question

PHP ORDER BY doesn't do what it's supposed to

I am creating some kind of program which first stores numeric values, later on, there will be characters and words in that column so I stored my numbers in a VARCHAR.

Now, I'm using this code:

SELECT * FROM biedingen WHERE bod_refferentie=$item ORDER BY bod_bedrag DESC LIMIT 5


And the output is:

9

27


Is there a way to get me to store the numeric values in a varchar, but still be able to order them using kind of the same method as above?

Answer

It appears that your bod_bedrag column is varchar or some other non numeric type. Hence, MySQL is sorting the column as text rather than numerically. This explains why single digits appear before double digits, etc.

One workaround here is to cast the bod_bedrag column to a numeric type during ordering:

SELECT *
FROM biedingen
WHERE bod_refferentie=$item
ORDER BY CAST(bod_bedrag AS SIGNED) DESC
LIMIT 5

One nifty alternative recognizes that sorting varchar numbers actually works for numbers of the same length. What I mean by this is that if you had 100, 305, 900 and sorted ascending, you would get this order. Here is another option which avoids the cast:

SELECT *
FROM biedingen
WHERE bod_refferentie=$item
ORDER BY CHAR_LENGTH(bod_bedrag) DESC,
         bod_bedrag DESC
LIMIT 5