Help with Generating top 10 table

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

Help with Generating top 10 table

Post by Milan »

Hi to all,

I have a users table where users have rating in the form of "9.43" for example. How can i create a routine or a procedure that will populate a "topten" table with current 10 top users.

for the reference, database name is "TESTBASE", user database is "users" and rating filed is "rating" and i want to populate a table "topten"
with fields "username","rating","completed posts" and "logo"

I know it might take a few beers to figure this out so i will paypal the money for 6 pack to the person that helps me as a thanks!
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You don't want a top ten table.. just select the top ten from the users table.

Code: Select all

$sql = "select username, rating, completedposts, logo from users order by rating desc limit 10";
$result = mysql_query($sql,$databaseLink);
while ($record = mysql_fetch_object($result)) {
    echo $record->username;
}
Milan
Forum Commoner
Posts: 97
Joined: Wed May 17, 2006 6:08 pm

ok

Post by Milan »

Will this get slow over the time with 1000+ users?
User avatar
andym01480
Forum Contributor
Posts: 390
Joined: Wed Apr 19, 2006 5:01 pm

Post by andym01480 »

Doh someone beat me to it. Shouldn't be slow for 1000+ users by the way!

Assuming you are already connected to the database in your script this should work!

Code: Select all

$query="SELECT * FROM users ORDER BY rating desc LIMIT 10"; //selects only 10 users with highest rating in order
$toptenquery=mysql_query($query) or DIE ("Query didn't work");

echo "Top ten users";
echo "<table>";
echo "<tr><td>Position</td><td>User</td><td>Rating</td></tr>";
for ($x=1;$x<=10,$x++){
$result=mysql_fetch_array($toptenquery);
echo "<tr><td>$x</td><td>$result['user']</td><td>$result['rating']</td></tr>";
}
echo"</table>";
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: ok

Post by onion2k »

Milan wrote:Will this get slow over the time with 1000+ users?
I do that sort of thing on tables with several million records and it's still incredibly quick. 1000 is nothing in database terms.
Post Reply