User Name Password Register
DaniWeb IT Discussion Community
All
What is DaniWeb IT Discussion Community?
You're currently browsing the PHP section within the Web Development category of DaniWeb, a massive community of 397,768 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,471 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our PHP advertiser: Lunarpages PHP Web Hosting
Views: 17009 | Replies: 5
Reply
Join Date: Sep 2004
Location: Cheltenham, UK
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

PHP/SQL query help

  #1  
Feb 9th, 2005
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??
AddThis Social Bookmark Button
Reply With Quote  
Join Date: Sep 2004
Location: Cheltenham, UK
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

Re: PHP/SQL query help

  #2  
Feb 17th, 2005
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!
Reply With Quote  
Join Date: Feb 2005
Location: Long Beach
Posts: 5
Reputation: cjgraphix is an unknown quantity at this point 
Rep Power: 0
Solved Threads: 0
cjgraphix's Avatar
cjgraphix cjgraphix is offline Offline
Newbie Poster

Re: PHP/SQL query help

  #3  
Feb 28th, 2005
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.
Reply With Quote  
Join Date: Oct 2004
Location: San Francisco, CA
Posts: 338
Reputation: paradox814 is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 1
paradox814's Avatar
paradox814 paradox814 is offline Offline
Posting Whiz

Re: PHP/SQL query help

  #4  
Mar 1st, 2005
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
Reply With Quote  
Join Date: Sep 2004
Location: Cheltenham, UK
Posts: 20
Reputation: TimmyRaa is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 0
TimmyRaa's Avatar
TimmyRaa TimmyRaa is offline Offline
Newbie Poster

Re: PHP/SQL query help

  #5  
Mar 1st, 2005
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!
Reply With Quote  
Join Date: Mar 2005
Location: Colorado
Posts: 50
Reputation: barnamos is an unknown quantity at this point 
Rep Power: 4
Solved Threads: 0
barnamos's Avatar
barnamos barnamos is offline Offline
Junior Poster in Training

Re: PHP/SQL query help

  #6  
Mar 22nd, 2005
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.
Reply With Quote  
Reply

Only community members can participate in forum threads. You must register or log in to contribute.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

 

DaniWeb PHP Marketplace
Thread Tools Display Modes

Similar Threads
Other Threads in the PHP Forum

All times are GMT -4. The time now is 4:18 am.
Forum system based on vBulletin Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
©2003 - 2008 DaniWeb® LLC