1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

PHP & Mysql. Limiting results via [id] and displaying them via [rating] ?

Discussion in 'PHP' started by mynameisdi, Mar 18, 2009.

  1. #1
    Let's say that I have a database of videos. I want to have a "top videos" page.

    This means that I want to limit the results to, say, latest 50 videos -- and sort them by their rating.

    So that I would have a page that displays latest 50 videos in order of their rating.

    This is the code that I have for displaying videos "in order of upload":

    
    <?php
    
    // Create a connection to your database.
    
    // Query database and select the last 10 entries.
    $data = mysql_query("SELECT * FROM video ORDER BY id DESC LIMIT 50");
    while($row = mysql_fetch_array($data))
    {
    
    echo "
    
    ".$row[text]."
    
    ";
    
    }
    ?>
    
    Code (markup):
    So how can I sort them so that I'd have the 50 latest videos by ID, but sorted by rating?
     
    mynameisdi, Mar 18, 2009 IP
  2. yoavmatchulsky

    yoavmatchulsky Member

    Messages:
    57
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    48
    #2
    i dont think its possible.

    what you can do is get the latest 50 ordered by IDs and then use PHP to sort them by rating
     
    yoavmatchulsky, Mar 18, 2009 IP
  3. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #3
    Try something like this:
    replace:
    $data = mysql_query("SELECT * FROM video ORDER BY id DESC LIMIT 50");

    with
    $data = mysql_query(SELECT * FROM (SELECT * FROM video ORDER BY ID DESC LIMIT 50) AS ttbl ORDER BY rating ASC;
     
    PoPSiCLe, Mar 18, 2009 IP
  4. mynameisdi

    mynameisdi Banned

    Messages:
    977
    Likes Received:
    9
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You sir, are ABSOLUTELY right! I just needed by rating DESC, but that's easily changable. Displays exactly what I needed.

    I don't really understand what does "as ttbl" do in this query though?
     
    mynameisdi, Mar 18, 2009 IP
  5. SmallPotatoes

    SmallPotatoes Peon

    Messages:
    1,321
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    0
    #5
    If you are doing this type of subselect you have to give a name to the temporary virtual table that is created by the inner (second) select. "ttbl" is the name that is being given here. It can be anything, it doesn't matter what.
     
    SmallPotatoes, Mar 18, 2009 IP
  6. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #6
    Glad to hear it :) Feel free to rep me :)

    Yes, I should have explained what the "as ttbl" does - the "ttbl" name was just chosen as a short for "temporary table", btw - as SmallPotatoes said, it can be called anything you want.
     
    PoPSiCLe, Mar 18, 2009 IP
    mynameisdi likes this.
  7. basecore

    basecore Peon

    Messages:
    47
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #7
    I guess simple
    $data = mysql_query("SELECT * FROM video ORDER BY id DESC, rating ASC LIMIT 50");

    would work, can you check please ?

    It's much faster then the queries before, but I'm not sure if it would work as yo uwant
     
    basecore, Mar 19, 2009 IP
  8. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,623
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #8
    It wont work, no. If it did, it would hardly be hard to find the solution :)
     
    PoPSiCLe, Mar 19, 2009 IP
  9. ranacseruet

    ranacseruet Peon

    Messages:
    302
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #9
    this is the easiest way..
     
    ranacseruet, Mar 19, 2009 IP
  10. SmallPotatoes

    SmallPotatoes Peon

    Messages:
    1,321
    Likes Received:
    41
    Best Answers:
    0
    Trophy Points:
    0
    #10
    With only 50 items to sort, speed is not an issue. If the subselect had a giant result set, then it would matter.

    Your query will return the 50 videos with the highest ID numbers, and sort them by ID number. Only if two videos had the same ID number (which probably can't happen) would the rating come into play in ordering. That's not what the person wanted.
     
    SmallPotatoes, Mar 19, 2009 IP