James Allan James Allan - 1 year ago 70
MySQL Question

Giving an order to my numbers natsort(); doesn't work well

I'm having problems while I'm loading content from my database.
In my DB I have a table where it holds my gallery images.
In my row called title is set up as text because the values I wanted to use are: hashtag and then a number like this #1 #2 ... and so on.

So I'm loading my content like this...

$sql = "SELECT * FROM gallery ORDER BY title DESC LIMIT $start_from, $per_page";
$run = mysqli_query($conn,$sql);

while ($rows = mysqli_fetch_assoc($run))

echo'....... ';}

but the problem is that when I order by title I'm getting these numbers

hastag 90

hastag 9-2

hastag 9-1

So I'm using ORDER BY title DESC why isn't my biggest number (hashtag 706) the first?

here is an picture of it...

I tried to use
but it didn't work because it keeps the same thing.

here is one more pic of my problem but in an other page ...

How can I resolve this?

Answer Source

9-2 isn't a number. It's a STRING, so that means your title field is a text type (char, varchar), and STRING sorting rules apply. That means the strings are compared "character-by-character", and not "number-by-number".


comparison a) "9" > "7" -> true
comparison b) "-" > "" -> true
comparison c) "2" > "" -> true



comparison a) "9" > "7" -> true   (ascii 57 > ascii 55)
comparison b) "-" > "0" -> false  (ascii 45 > ascii 48)
comparison c) not necessary, 9-2 is already "bigger" than 706

So 9-2 is a BIGGER string than 706, because 9>7 is always true.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download