Jump to content

Displaying a leaderboard problem


Recommended Posts

Hello.  I'm new to this forum as I have given up my google hunt for the right answer to this problem.  I'm sure it's simple but I can't get my head round it.


I have a webiste that has members that each have a team of motorbike riders.


I have 2 tables, rider_stats and teams


I want to display a leaderboard with the highest scoring team on top.


Table egs.



rider_name  |  total_points

J Lorenzo  |  45

V Rossi  |  8

etc.  - many riders



brig_bikers  |  wrongspeeds_riders

D Pedrosa  |  C Checa

V Rossi      |  J Tinmouth

each team has six riders


The way my site currently display a leaderboard is by using a SELECT for each team and then ordering the table manually in HTML.  For this I use

$result = mysql_query( "SELECT SUM(total_points)

FROM rider_stats, teams

WHERE team_smirnoff = rider_name" )

          or die("SELECT Error: ".mysql_error());

while ($row = mysql_fetch_assoc ($result)) {

extract ($row);

$smirnoff = array_sum($row);



Then I put

<td><a href="smirnoff">Team Smirnoff</a></td>

<td><? echo $smirnoff;?></td>

into the HTML to build the table.


I would like to not have to alter any code each weekend that there is bike racing.  All I want to do is update the scores on the db.


Any thoughts?





Link to comment
Share on other sites

Do you mean the ORDER BY function? 


Because I have to make a new query for every team I dont know how to have them ordered. 


I did come up with a solution though but it has created a bit more management of the db.  I now have a new table where each row has the team_name, rider_a, rider -b to _f, latest_points and total_points.  This means I can display a leaderboard with the code I know but now I have to update this table every race weekend and when users substitute riders at the intervals I have to change them here as well. premplayers.com if you're interested.


If you have a way of displaying a leaderboard without usuing this new table I'd like to know it!!

Link to comment
Share on other sites

$result = mysql_query( "SELECT ( select `rider`.`total_points` from`rider` WHERE `rider`.`team-name` = `team`.`rider_name` ) AS `rtotal`, `team`.`rider_name`, FROM `teams` AS team
LEFT JOIN `rider_stats` AS rider ORDER BY `rtotal` desc)


i think this should do what you want...

sum the points of all riders on a team and asoc it with 1 team .. then you can loop each team...

orderd by `rtotal` desc should give you a full list of all teamd and there total points...


with out testing it this is the best i can do rite now ...

Link to comment
Share on other sites



Not sure but it seems what you want is all the teams in total points order, and within that a list of all the riders. You should be able to do that with a single select.


To give you a rough idea:-



$result = mysql_query( "SELECT c.team_id, c.teamname, d.rider_name, d.total_points, e.TeamPoints
FROM teams c
INNER JOIN rider_stats d
ON c.team_id = d.team_id
INNER JOIN (SELECT a.team_id, SUM(total_points) AS TeamPoints
FROM teams a
INNER JOIN rider_stats b
ON a.team_id = b.team_id
GROUP BY a.team_id) e
ON c.team_id = e.team_id
ORDER BY e.TeamPoints DESC, d.total_points DESC" ) or die("SELECT Error: ".mysql_error());
$CurrTeam = '';
while ($row = mysql_fetch_assoc ($result)) 
if ($CurrTeam != $row['team_id'])
	echo 'Team '.$row['team_id'].' - '.$row['TeamPoints'].'<br />';
	$CurrTeam == $row['team_id'];
	echo 'Team '.$row['rider_name'].' - '.$row['total_points'].'<br />';
echo 'Team '.$row['rider_name'].' - '.$row['total_points'].'<br />';



The above is based on what I think you want and a guess on column names in the tables.


It is best to avoid doing selects within a loop.


All the best



Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.