Problem with Order by query

[Schwalbach]

Hey guys,

Not sure if this should be in mysql or php, but I thought it included more php than mysql, so put it here..

I have four things in my database, and in their downloads column I have the numbers 0,9,12 and 15. I am making a top 5 downloads thing, and instead of coming out 15,12,9,0, it comes out as.. 9,15,12,0

Here is how I have it set up..

 <?php 
    $sql=mysql_query("select * from jamie_download ORDER BY downloads DESC");
    $numrows=mysql_num_rows($sql);
    for($i=0;$i<=$numrows;$i++) {
    while ( $row = mysql_fetch_array($sql)) {
    echo($row['name']); 
    }
    }
    ?>

What am I doing wrong here?

Thanks,
Chase

[r937]

what you did wrong (i'm guessing) is you didn't use a numeric datatype like INTEGER for your downloads column, but rather a string type like VARCHAR

you can use the following workaround, which isn't very efficient but nevertheless still works:

... ORDER BY 0+downloads desc

in this expression, mysql automatically converts the downloads string value to a number, in order to add it to 0

this would cause a "datatype mismatch" syntax error in any other database

sometime's mysql's sloppiness can come in handy!

[Schwalbach]

ty. I didn't know it made a difference

I changed it to INT and it worked perfect..

Thanks again

[r937]

well, when you think about it, integers sort numerically, and strings sort like, well, strings, one character at a time, from left to right

let me guess something else

when someone downloads something, you first do a SELECT, grab the download number, do the incrementing in php, and store the result back into the database with an UPDATE

now that you have a numeric field, you can double the efficiency of your application by skipping the SELECT and going right to the UPDATE

UPDATE yourtable SET downloads=downloads+1 where id = $id