I've got a query set up that generates a resultset of players and their statistics, and this is displayed back out in a table via PHP:
Query: [php]$sql = 'SELECT nickname AS name, count( * ) AS played, sum( score ) AS agg, max( score ) AS highest, min( score ) AS lowest, avg( score ) AS average'
. ' FROM stats_players, stats_scoreslist, stats_matches'
. ' WHERE stats_players.playerid = stats_scoreslist.playerid AND stats_scoreslist.matchid = stats_matches.matchid AND stats_matches.season = 2004'
. ' GROUP BY nickname'
. ' ORDER BY average DESC , name ASC';
$result=mysql_query($sql) or die(mysql_error());
$rows="";
$position=1;
while ($row=mysql_fetch_array($result)) {
$playername=$row["name"];
$played=$row["played"];
$agg=$row["agg"];
$high=$row["highest"];
$low=$row["lowest"];
$avg=$row["average"];
$rows.="<tr><td><center>".$position.'</td><td><center>'.$playername.'</td><td><center>'.$played.'</td><td><center>'.$agg.'</td><td><center>'.$high.'</td><td><center>'.$low.'</td><td><center>'.$avg.'</td></tr>';
$position++;
}
//mysql_close()[/php]
PHP table generation:
<table width="550" border="1">
<tr>
<th scope="col">Pos</th>
<th scope="col">Player</th>
<th scope="col">Played</th>
<th scope="col">Aggregate</th></th>
<th scope="col">Highest</th>
<th scope="col">Lowest</th>
<th scope="col">Average</th>
</tr>
<?=$rows?>
</table>
This all works fine, but I now have a new requirement (a new column at the end) that relies on calculations between rows in the resultset/table.
I'm stumped as to how to do this - my experience allows me to use the SQL query to generate the table, but where a new result depends on the values in the previous result, I'm not sure what to do.
To outline what I need to work out, here's a few lines from the current table.
Player Played Aggregate Highest Lowest Average
1 Andy U 16 641 49 31 40.0625
2 Clive 16 636 45 32 39.7500
3 Rob 18 711 49 33 39.5000
I need to have another column at the end (except for position 1) that takes the difference between pos 1 average and pos 2 average, multiplies it by pos 2's games played, and adds that to pos 1's average. Just to give you some idea of context, it shows what score that person has to get to catch the person above them.
Anyone have any idea how this could be accomplished? I don't know enough about it, but I get the impression temporary DB tables might be involved. Is there a way you can get PHP to do calculations? I suspect that would be better if it can.
Anyway, pplease, anyone advice/ideas??
Unsolved 
