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

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.

$avg = $agg/$played;

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

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

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!

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.

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.