Page 1 of 1

Help with Generating top 10 table

Posted: Thu May 18, 2006 4:33 pm
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!

Posted: Thu May 18, 2006 4:52 pm
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;
}

ok

Posted: Thu May 18, 2006 4:54 pm
by Milan
Will this get slow over the time with 1000+ users?

Posted: Thu May 18, 2006 4:57 pm
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>";

Re: ok

Posted: Thu May 18, 2006 5:12 pm
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.