PHP_Idiot Posted October 17, 2009 Share Posted October 17, 2009 Hi Guru's I have two mysql queries that return a bunch of different records and figures. but I want to divide 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...) $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()); Link to comment https://forums.phpfreaks.com/topic/178060-solved-performing-simple-division-on-the-results-of-two-queries/ Share on other sites More sharing options...
PHP_Idiot Posted October 17, 2009 Author Share Posted October 17, 2009 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 Link to comment https://forums.phpfreaks.com/topic/178060-solved-performing-simple-division-on-the-results-of-two-queries/#findComment-938892 Share on other sites More sharing options...
PHP_Idiot Posted October 18, 2009 Author Share Posted October 18, 2009 You may have spotted the obvious mistake in the title!! It's not a division I need but a multiplication, though I doubt that changes things much! Link to comment https://forums.phpfreaks.com/topic/178060-solved-performing-simple-division-on-the-results-of-two-queries/#findComment-939056 Share on other sites More sharing options...
PHP_Idiot Posted October 18, 2009 Author Share Posted October 18, 2009 Ok So far I have the below code following the queries: $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? Link to comment https://forums.phpfreaks.com/topic/178060-solved-performing-simple-division-on-the-results-of-two-queries/#findComment-939074 Share on other sites More sharing options...
PHP_Idiot Posted October 19, 2009 Author Share Posted October 19, 2009 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 $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']]; ... Link to comment https://forums.phpfreaks.com/topic/178060-solved-performing-simple-division-on-the-results-of-two-queries/#findComment-939506 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.