"Top 10" from database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

"Top 10" from database

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What database are you using? This isn't PHP Code, moved to Databases.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

Mysql. I'm assuming I would use php to accomplish this?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Look up the LIMIT and ORDER BY clauses.

Yes, it could be done with PHP, but it is not PHP specific.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

a simple loop often works quite effectively.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

SELECT * FROM `accounts` WHERE `score` > 0 ORDER BY `score` DESC LIMIT 100
all the results in a single query.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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?
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post by Citizen »

Nevermind, I seem to have left my query inside of the loop :oops:
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

Post 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";
}
?>
Post Reply