Page 1 of 1

"Top 10" from database

Posted: Tue Mar 28, 2006 1:30 pm
by Citizen
If I have a column "score" and I want to display the top 10 accounts with the highest integer values in "score", how would I go about doing that?

Posted: Tue Mar 28, 2006 1:32 pm
by feyd
What database are you using? This isn't PHP Code, moved to Databases.

Posted: Tue Mar 28, 2006 1:34 pm
by Citizen
Mysql. I'm assuming I would use php to accomplish this?

Posted: Tue Mar 28, 2006 1:36 pm
by feyd
Look up the LIMIT and ORDER BY clauses.

Yes, it could be done with PHP, but it is not PHP specific.

Posted: Tue Mar 28, 2006 1:50 pm
by Citizen
Thanks.

I think I have good idea about how to run a query to find the top 10, but how would I go about displaying them on my page with formatting?

Posted: Tue Mar 28, 2006 1:53 pm
by feyd
a simple loop often works quite effectively.

Posted: Tue Mar 28, 2006 2:14 pm
by Citizen
I've never done this sort of thing before or worked with loops. Are there any good (simple) examples or tutorials for me to read?

Posted: Tue Mar 28, 2006 2:26 pm
by feyd
There are many examples posted daily on the forum. More still on the PHP manual pages for while, foreach, for and so forth.

Look around for code posted with "while" and a mysql_fetch_* function.

Posted: Tue Mar 28, 2006 2:52 pm
by Citizen
Ok, here's the best that I can code up for now:
(for top 100 scorers)

Code: Select all

<? 
for($i = 0; $i < 100; $i++) { 
$sql="SELECT * FROM `accounts` WHERE `score` > 0 LIMIT 1 ORDER BY `score`";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
$username = $row["name"];
$score = $row["score"];
echo "Rank $i: $username with a high score of $score";
} 
?>
Any suggestions?

Posted: Tue Mar 28, 2006 2:56 pm
by feyd

Code: Select all

SELECT * FROM `accounts` WHERE `score` > 0 ORDER BY `score` DESC LIMIT 100
all the results in a single query.

Posted: Tue Mar 28, 2006 3:10 pm
by Citizen

Code: Select all

?php
for($i = 0; $i < 100; $i++) { 
$sql="SELECT * FROM `accounts` WHERE `points` > 0 ORDER BY `points` DESC LIMIT 100";
$result=mysql_query($sql);
$row = mysql_fetch_array($result);
$username = $row["name"];
$score = $row["points"];
echo "<p>Rank $i: $username with a high score of $score";
} 
?>
Hmm... this seems to only display the top result 100 times instead of displaying the 100 scores.

Any ideas?

Posted: Tue Mar 28, 2006 3:20 pm
by Citizen
Nevermind, I seem to have left my query inside of the loop :oops:

Posted: Tue Mar 28, 2006 3:21 pm
by jwalsh
That's because your query is inside the loop...

Code: Select all

<?php
$sql="SELECT * FROM `accounts` WHERE `points` > 0 ORDER BY `points` DESC LIMIT 100";
$result=mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
$username = $row["name"];
$score = $row["points"];
echo "<p>Rank $i: $username with a high score of $score";
}
?>