Page 1 of 1
How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 5:44 am
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);
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 5:59 am
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>";
}
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 6:13 am
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.
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 7:53 am
by simonmlewis
Sorry what do you mean "WHERE userid IN (...)"???
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 8:12 am
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
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 8:25 am
by simonmlewis
No I need to show the username, which is in the members db table.
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 8:34 am
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.
Re: How do I create a top 20 of hits/points?
Posted: Wed Oct 14, 2015 8:40 am
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.