James Allan James Allan - 1 year ago 69
MySQL Question

Giving an order to my numbers natsort(); doesnt work well. PHP, MYSQL

I'm having problems while im loading content from my Database.
In my DB i have a table where it holds my gallery imgs.
In my row called title is setup as text cuz the values i wanted to use hashtag and then a number like this #1 #2 ...an so on.
So im 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 im getting this numbers

hastag 90

hastag 9-2

hastag 9-1

So im using ORDER BY title DESC why my biggest number that is hashtag 706 isn't the first?

here is an picture of it...
i tried to use natsort(); but it didn't work cuz 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