[RESOLVED] Echo Data From Table by Sort

Ascendancy

I have a data table that holds all of the sites users submit, and I have a small query that sorts them by rating:

$ratingQuery = mysql_query("SELECT * from news ORDER BY Rating");

while($ratingRow = mysql_fetch_array($ratingQuery)) {
	//Here is code to show the stuff
}

However, I am not sure how to get specific data for the top 15 rows in the table. Say I wanted to get data from the columns "URL, Title, User, Rating". However, I need them to be different for each of the 15 rows, because I am getting the top 15 highest rating stories and then echoing the data from them onto the page. I was thinking of using an Array, but I am not sure how to go about doing that?

halojoy

I suppose you should try to limit 15 highest results, in query.
The rule (for fastest execution) is to let MySQL module do as much as possible.
And then do rest of the stuff using other PHP functions.

What I can see, then you just echo those rows - one by one.
If they are ordered by Rating ( DESC ), then it would possibly work.
Why not make a test?

$ratingQuery = mysql_query("SELECT * from news ORDER BY Rating");

while($ratingRow = mysql_fetch_array($ratingQuery)) {
	//Here is code to show the stuff
       echo $ratingRow['URL']. ' - ';
       echo $ratingRow['Title']. ' - ';
       echo $ratingRow['User']. ' - ';
       echo $ratingRow['Rating']. '<br />';
}

mikell

"I need them to be different for each of the 15 rows" makes me think you have something like the following:

URL, Title, User, Rating (ordered by rating descending)

google.com, google, jim, 10
google.com, google, bob, 9
google.com, google, tom, 9
yahoo.com, yahoo, kat, 8
blah.com, blah, jon, 5
...etc

and you're trying to figure out how to show google.com once in your top 15 list.

If that's the case, you could use GROUP BY with MAX() to get what you want.
For example, the following query would give you the top 15 unique URLs by Rating, ordered high to low:

SELECT URL, Title, User, MAX(Rating) as Rating from news GROUP BY URL ORDER BY MAX(Rating) DESC LIMIT 15

Ascendancy

Ah, okay I get it. Very helpful my friends, thanks a bunch!