Page 1 of 1

Performing simple multiplication on two query results

Posted: Sun Oct 18, 2009 2:30 am
by PHP_Idiot
Hi Guru's

I have two mysql queries that return a bunch of different records and figures. but I want to multiply one figure from one query by another fingure from the second query. I'm sure it can't be that hard, but this is a first for me so any help would be massively appreciated!

Here are the two queries: (they return exactely what I need, I'll post a follow up showing the output of each...)

Code: Select all

$top40 = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points AS Venue_Points, SubSelect.count_results AS Venue_Play_Count, SubSelect3.sum_points3 AS Total_Points
FROM Player, ( 
 
SELECT Player1.MembershipNo, Venue1.VenueName, SUM( Position1.Points ) AS sum_points, COUNT( Player1.MembershipNo ) AS count_results
FROM Player Player1, Results Results1, Position Position1, Venue Venue1
WHERE Player1.MembershipNo = Results1.MembershipNo
AND Results1.Position = Position1.Position
AND Venue1.VenueID = Results1.VenueID
AND Results1.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player1.MembershipNo, Venue1.VenueName)SubSelect, ( 
 
SELECT Player3.MembershipNo, SUM( Position3.Points ) AS sum_points3
FROM Player Player3, Results Results3, Position Position3
WHERE Player3.MembershipNo = Results3.MembershipNo
AND Results3.Position = Position3.Position
AND Results3.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player3.MembershipNo)SubSelect3
WHERE Player.MembershipNo = SubSelect.MembershipNo
AND SubSelect.sum_points = ( 
SELECT MAX( SubSelect1.sum_points2 ) 
FROM ( 
 
SELECT Player2.MembershipNo, Venue2.VenueName, SUM( Position2.Points ) AS sum_points2
FROM Player Player2, Results Results2, Position Position2, Venue Venue2
WHERE Player2.MembershipNo = Results2.MembershipNo
AND Results2.Position = Position2.Position
AND Venue2.VenueID = Results2.VenueID
AND Results2.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Player2.MembershipNo, Venue2.VenueName ) SubSelect1
WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo)
AND Player.MembershipNo = SubSelect3.MembershipNo
AND SubSelect.sum_points >=25
ORDER BY SubSelect.sum_points DESC 
LIMIT 0 , 40"; 
$result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error());
 
$AvePlayers = "SELECT Venue.VenueName, COUNT( Results.MembershipNo ) , COUNT( DISTINCT Results.Date ) , cast( coalesce( COUNT( Results.MembershipNo ) / COUNT( DISTINCT Results.Date ) ) AS decimal( 10, 1 ) ) AS 'Average'
FROM Position, Player, Results, Venue
WHERE Player.MembershipNo = Results.MembershipNo
AND Results.Position = Position.Position
AND Venue.VenueID = Results.VenueID
AND Results.Date
BETWEEN '$BeginDate'
AND '$EndDate'
GROUP BY Venue.VenueName
ORDER BY Average DESC 
LIMIT 0 , 30";
$result2 = mysql_query($AvePlayers) or die("Couldn't execute query because: ".mysql_error());

Re: Performing simple multiplication on two query results

Posted: Sun Oct 18, 2009 2:32 am
by PHP_Idiot
What I need to do is multiply the players Chips from the first table, by the average players from the corresponding venue in the second table.
EG. Multiple Joe's Points by the GB Poker Club average, and Janes by The Pub's average.
Then store the result in a variable I can use again in a later query!

First (HUGE) query outputs this (for the top 40 players):
Pos First Last Venue Chips Played
1 Joe Bloggs GB Poker Club 1250 4
2 Jane Smith The Pub 1025 3

The Second smaller query outputs this (for all venues):
Venue Total Players Games Played Ave Players/Game
The Pub 28 2 14
GB Poker CLub 24 2 12

Re: Performing simple multiplication on two query results

Posted: Sun Oct 18, 2009 3:54 am
by PHP_Idiot
Ok So far I have the below code following the queries:

Code: Select all

 
$r2 = mysql_fetch_array($result2);
 
 
 
 
echo <<<html
<table  border="1" width="480" cellpadding="1" cellspacing="1">
    <tr><td align="center"><strong>Pos.</strong></td>
        <td align="center"><strong>First</strong></td>
        <td align="center"><strong>Last</strong></td>
        <td align="center"><strong>Venue</strong></td>
        <td align="center"><strong>Points</strong></td>
        <td align="center"><strong>Played</strong></td>
        <td align="center"><strong>GBPC Points</strong></td>
        
    </tr>
html;
 
//Now start the loop.
 
 
$pos=1;
 
 
while($r = mysql_fetch_array($result)){
    //and echo each new row
    $GBPCPoints = $r['Venue_Points'] * $r2['Average'];
    echo <<<html
    <tr><td align="center">$pos</td>
        <td align="center">{$r['FirstName']}</td>
        <td align="center">{$r['LastName']}</td>
        <td align="center">{$r['Venue']}</td>
        <td align="center">{C}</td>
        <td align="center">{$r['Venue_Play_Count']}</td>
        <td align="center">$GBPCPoints</td>
        
    </tr>
html;
$pos++; }
This is almost working, but it is multiplying all $r['Venue_Points'] by the first returned $r2['Average'] and not the corresponding average!

Any ideas how I can get it to multiply all $r['Venue_Points'] by the $r2['Average'] of the correct venue?

Re: Performing simple multiplication on two query results

Posted: Mon Oct 19, 2009 2:52 am
by PHP_Idiot
Got the answer on another forum, for anyone interested it was resolved by having another WHILE loop before the one I had, where all the rows from $result2 are fetched and stored in a hash array that is indexed into by $r['Venue'] inside the WHILE loop I already had

Code: Select all

$average_by_venue = array(); 
 
while ($r2 = mysql_fetch_array($result2) ) { 
  $average_by_venue[ $r2['VenueName'] ] = $r2['Average']; 
} 
... 
... then in the loop I have 
... 
          $GBPCPoints = $r['Venue_Points'] * $average_by_venue[$r['Venue']]; 
...