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

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?

 

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

 

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;

 

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?

 

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.

 

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.

 

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

 

It wont work, no. If it did, it would hardly be hard to find the solution

 

this is the easiest way..

 

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.