SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Wheelersburg
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with Order by query

    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 Code:
     <?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
    http://chaseschwalbach.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2004
    Location
    Wheelersburg
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ty. I didn't know it made a difference

    I changed it to INT and it worked perfect..

    Thanks again
    http://chaseschwalbach.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •