Number sorting problem

[Anon]

I'm having a problem sorting using numbers in PHP.

I want the list to sort like this:

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20

But, when I sort, it comes out like this:

1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 3, 4, 5, 6, 7, 8, 9

It's sorting from the first postion instead of the last. Is there a way to fix this?

The code, if it helps, is this:

$users = mysql_query("SELECT id,employee,sortid FROM access ORDER BY sortid ASC");
while($data=mysql_fetch_row($users)){
print '<option>' .$data[1]. '</option>';
}

The field that contains the numbers to sort with is 'sortid'.

Thanks for the help,

Zimm

[Anon]

In PHP you can use the natsort() function. I don't know of mySQL has a similar function. If not just pull out the data unsorted then sort it with natsort().

[Anon]

I'm going to make a wild guess that "sortid" is defined as varchar, not int. So mysql (not php) is sorting the strings (not numbers) the way you told it to.

To fix it, you either need to sort on a numeric column or right justify the character string stored in sortid (I don't have a mysql database available but you should be able to use SQL functions DATALENGTH() and SPACE() to pad sortid with blanks on the left).

[Anon]

Thanks,

I took the latter and changed the collumn from char to int and it sorted in the correct order now.

:o)