PHP/SQL query help
 

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

Re: PHP/SQL query help
 

Anyone? Any ideas on how to perform further calculations on a set of results from a SQL statement? There's a Gmail invite and/or my eternal love winging its way to whoever comes up with the goods! :)

Re: PHP/SQL query help
 

It would really help to know what calculations you wanted to perform.

for instance... say you did not have the average in the database (and really I don't think you need it !) and you wanted to find it.

[php]$avg = $agg/$played;[/php]

let me know exactly what calcs you are looking to do and I can help you out.

Re: PHP/SQL query help
 

Or instead of doing it with PHP you could just have it done with the db... so there really is like cjgraphix says no reason to hold that in a table you might notice the Average in quotes that just returns the column name as Average instead of seeing the formula Aggregate/Highest beign returned

SELECT Played,Aggregate,Hightest,Lowest,Aggregate/Highest "Average"

As far as doing what your saying I don't know how to do that with mysql, try just doing it in php show me 2 examples with the dataset you provided and i will tell you how to do it with php

Re: PHP/SQL query help
 

Aye, it's a tricky one to explain, but thanks for the replies guys!

I'm not sure what more I can say in addition to my original post though. The current SQL query I'm using is there in that post, so you can see how I'm doing stuff already. Incidentally, I don't have the average stored in the DB - that's calculated through SQL dynamically.

The crux of this issue though is performing calculations on the result (or resultset I should say) of a SQL query.

For example, my current SQL query follows:

$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';

Which when run returns the following sample:

name     played    agg    highest lowest average

Andy U     16      641      49      31   40.0625

Clive      16      636      45      32   39.7500

Rob        18      711      49      33   39.5000

I need to add another column on the end of each row that dynamically performs a calculation on values within that row, and the row above it.

I'll step through row 2's calculation for example; I need to subtract row 2's average from row 1's average (40.0625 - 39.7500 = 0.3125), and multiply that by row 2's games played (0.3125 * 16 = 5), and add that value to row 1's average (5 + 40.0625 = 45.0625). This would be repeated for all the rows in the result.

I don't think it can be done with pure SQL, so I'm guessing we'd need to run it into an array in PHP (which is already is, I think - $result?) and add the extra column in there, and perform the calculations through PHP.

Is that any clearer?!? Again, thanks for persisting with me!

Re: PHP/SQL query help
 

If I understand.....
How about building an array on those values as you step through the result. That way you can call the key values that are one before the row you are in. Maybe drop the actual calculations in as array vals. So you would have an array, (multidimensional) that would store in myarray[playerposition] = array(player average, player games played, player steroids used, etc,). Then when going through row "x" you could call the values from the array as myarray[{x-1}][0] and do your magic on them.