How do I create a top 20 of hits/points?

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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I create a top 20 of hits/points?

Post by simonmlewis »

I have a DB table that will have entries of userid, time, hits, points, teamname.
There will be hundreds of rows.

Each one will have a date entered as well.

But we need to see an "overall" top 20 list. So it needs to add up all the points of each userid, then display then in order (highest first), and only show the top 20.

Now I'm sure it's ORDER BY point LIMIT 0,20.
But I don't really know how to gather it all up, and then show it.

This is the code whereby I search by date... so you can see it needs a JOIN.

Code: Select all

$result = mysql_query ("SELECT m.id, m.username, m.firstname, m.lastname, l.time, l.hits, l.points, l.teamname, l.gun, l.dateevent FROM members AS m INNER JOIN leaderboard AS l ON l.userid = m.id WHERE l.dateevent = '$dateevent' ORDER BY l.points DESC", $dbmembers);
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I create a top 20 of hits/points?

Post by simonmlewis »

This isn't using JOIN because I'm not sure how to with the sum(points)....

But it works.

Code: Select all

echo "<div class='leaderboard-rowdt'><table cellspacing='0' cellpadding='4' width='75%'><tr><td>Position</td><td>Nickname</td><td>Time</td><td>Hits</td><td>Points</td><td>Team Name</td></tr>";

$result = mysql_query ("SELECT sum(points) AS points, id, time, hits, userid FROM leaderboard GROUP BY userid ORDER BY points DESC LIMIT 0,20", $dbmembers);
  while ($row = mysql_fetch_object($result))
      {
      $count ++;
      echo "<tr";
      if ($count <= 10) { echo " bgcolor='#ffff99'";}
      echo "><td>$count</td><td>";
      $resultuser = mysql_query ("SELECT username FROM members WHERE id ='$row->userid'", $dbmembers);
  while ($rowuser = mysql_fetch_object($resultuser))
      {
      echo "$rowuser->username";
      }
      echo "</td><td>$row->time</td><td>$row->hits</td><td>$row->points</td></tr>";
      }
      echo "</table></div>";
}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I create a top 20 of hits/points?

Post by Celauran »

By moving that second query out of the loop and using WHERE userid IN (...) you can reduce the total number of queries from 21 to 2.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I create a top 20 of hits/points?

Post by simonmlewis »

Sorry what do you mean "WHERE userid IN (...)"???
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I create a top 20 of hits/points?

Post by Celauran »

Code: Select all

SELECT a, b, c FROM table WHERE id = 1 OR id = 2
is the same as

Code: Select all

SELECT a, b, c FROM table WHERE id IN (1, 2)
You've already fetched all the userids. Use those to populate your IN
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I create a top 20 of hits/points?

Post by simonmlewis »

No I need to show the username, which is in the members db table.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I create a top 20 of hits/points?

Post by Celauran »

Right. That's what I'm saying.

Code: Select all

$result = mysql_query ("SELECT sum(points) AS points, id, time, hits, userid FROM leaderboard GROUP BY userid ORDER BY points DESC LIMIT 0,20", $dbmembers);
Collect your userid into an array, then use that to get all your usernames in one query.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I create a top 20 of hits/points?

Post by Celauran »

Something like this

Code: Select all

$result = mysql_query ("SELECT sum(points) AS points, id, time, hits, userid FROM leaderboard GROUP BY userid ORDER BY points DESC LIMIT 0,20", $dbmembers);
$members = array();
while ($row = mysql_fetch_assoc($result)) {
	$members[$row['userid']] = $row;
}
$users = mysql_query("SELECT id, username FROM members WHERE id IN (".implode(',', array_keys($members)).")");
while ($row = mysql_fetch_object($users)) {
	$members[$row->id]['username'] = $row->username;
}
Now your members array contains the data you wanted from both tables, and you got it in 2 queries instead of 20.
Post Reply