Displaying a leaderboard problem

[wrongspeed]

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_stats

rider_name  |  total_points

J Lorenzo  |  45

V Rossi  |  8

etc.  - many riders

 

teams

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?

 

regards

 

~wrongspeed~

[fenway]

Why not order the results in mysql?

[wrongspeed]

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!!

[blacknight]

$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 ...

[kickstart]

Hi

 

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:-

 

<?php

$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

 

Keith