[SOLVED] DB Records Retrieved Out Of Order

[refiking]

I am trying to retrieve the records in order by the did field.  For some reason it retrieves the first one and then skips to the record with a did of 10.  Here is my code:

 

$sql = mysql_query("SELECT * FROM dregs WHERE lid = '$lid' AND sid = '$sid' AND tm = '' ORDER BY did LIMIT 3");

[haku]

Can you do an order by without saying which direction to order it in?

[p2grace]

Hmm not sure if this will work but it's worth a shot:

 

<?php
$sql = mysql_query("SELECT * FROM `dregs` WHERE `lid` = '$lid' AND `sid` = '$sid' AND `tm` = '' ORDER BY `did` LIMIT 0,3");
?>

[p2grace]

To answer your question yes, it defaults to asc order.

[refiking]

No. That didn't work, either.  When I did the order by desc, it went 9, 8, 7  and there are 50 entries.  I don't get it.  It skips 2-9 on ASC and skips 50-10 on desc.

[p2grace]

Try typing the query straight into phpmyadmin and see if it does the same thing

[refiking]

Yes.  It returned the same thing

[p2grace]

Then it must be something with the data.  The query is correct.

[refiking]

The data has the correct did I just quadruple checked.  There is a 1,2,3,4,5,6.........50 did record.

[haku]

It sounds like the numbers may be saved in your database as text - if that is the case, then when you order it, it will order like this♦:

 

1

10

11

12

13

14

15

16

17

18

19

2

20

21

22

...etc

 

They will only order as 1,2,3,4,5 etc if they are saved as numbers.

[p2grace]

What's the record type, is it integer?  It makes sense for it to do that if it isn't integer, it's probably varchar.

[p2grace]

Yeup haku is right, that's the only other explanation.

[refiking]

That was it!  It was varchar.  Changed it and it works perfectly!