Page 1 of 1

Multiple Queries from Multiple Tables in while loop?

Posted: Sat Oct 03, 2009 3:53 pm
by oscardog
Title say it all really. I have two tables, one stores 'batting stats' and one scores 'bowling stats' and no matter what I try I can't use a query from say bowlingstats table in a while loop using a query from the battingstats table.

Anyone know? :)

Thanks :banghead:

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sat Oct 03, 2009 4:06 pm
by John Cartwright
Post what you've tried for starters.

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sat Oct 03, 2009 4:16 pm
by oscardog

Code: Select all

<table width="100%">
<tr>
    <th>Name</th>
    <th>Games Played</th>
    <th>Overs Bowled</th>
    <th>Total Runs Scored</th>
    <th>Batting Average</th>
    <th>Runs Conceded</th>
    <!-- <th>Wickets Taken</th>
    <th>Bowling Average</th> -->
</tr>
<?php 
$playersResult = mysql_query("SELECT name, SUM(`battingscore`) AS `sumScore` FROM battingstats GROUP BY name");
 
while($playersRow = mysql_fetch_array($playersResult)) {
$playerTotalGamesResult = mysql_query("SELECT COUNT(`name`) AS `totalGames` FROM battingstats GROUP BY name");
$playerTotalGamesRow = mysql_fetch_array($playerTotalGamesResult);
$totalGames = $playerTotalGamesRow['totalGames'];
$battingAverage = $playersRow['sumScore'] / $totalGames;
 
$oversBowledResult = mysql_query("SELECT COUNT(`name` AS `oversBowled` FROM bowlingstats GROUP BY name");
$oversBowledRow = mysql_fetch_array($oversBowledResult);
$oversBowled = $oversBowledRow['oversBowled'];
 
$runsConcededResult = mysql_query("SELECT SUM(`runsconceded` AS `totalRuns` FROM bowlingstats GROUP BY name");
$runsConcededRow = mysql_fetch_array($runsConcededRow);
$runsConceded = $runsConcededRow['totalRuns'];
echo "<tr>";
echo "<td align=\"center\">" . $playersRow['name'] . "</td>";
echo "<td align=\"center\">" . $totalGames . "</td>";
echo "<td align=\"center\">" . $oversBowled . "</td>";
echo "<td align=\"center\">" . $playersRow['sumScore'] . "</td>";
echo "<td align=\"center\">" . $battingAverage . "</td>";
echo "<td align=\"center\">" . $runsConceded . "</td>";
echo "</tr>";
} ?>
</table>
Now wanna know something weird about the code? Obviously the playersRow ones work. But so does the first query which details games played and the batting average one also works... just not the bowling one. I guess it's because the games played and batting average are resources from the battingstats table.

Anyway there is the code :)

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 5:58 am
by oscardog
Bump, really need a hand :banghead:

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 6:04 am
by Eran
There's no reason to run the other queries, as they are, inside the loops. Since you use no filtering criteria that is derived inside the loop, it might as well be run once before the loop starts and values reused inside the loop. Aside from that, some of the queries are missing closing brackets ')' on functions such as SUM and COUNT.

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 7:40 am
by oscardog
pytrin wrote:There's no reason to run the other queries, as they are, inside the loops. Since you use no filtering criteria that is derived inside the loop, it might as well be run once before the loop starts and values reused inside the loop. Aside from that, some of the queries are missing closing brackets ')' on functions such as SUM and COUNT.
I fixed all the queries but it still wont display what I want, infact it throws no PHP error but outputs on results no matter what I try...

I don't fully understand what you mean about the queries being outside the loop, though :(

Oscardog

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 7:54 am
by Eran
Show your updated code

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 8:33 am
by oscardog
Well I tried making arrays of data, but i don't think it's the way I should be doing it :/

Code: Select all

<table width="100%">
<tr>
    <th>Name</th>
    <th>Games Played</th>
    <th>Overs Bowled</th>
    <th>Total Runs Scored</th>
    <th>Batting Average</th>
    <th>Runs Conceded</th>
    <!-- <th>Wickets Taken</th>
    <th>Bowling Average</th> -->
</tr>
<?php 
$playersResult = mysql_query("SELECT name, SUM(`battingscore`) AS `sumScore` FROM battingstats GROUP BY name");
$runsConcededResult = mysql_query("SELECT SUM(`runsconceded`) AS `totalRuns` FROM bowlingstats GROUP BY name");
$oversBowledResult = mysql_query("SELECT COUNT(`name`) AS `oversBowled` FROM bowlingstats GROUP BY name");
 
while($oversBowledRow = mysql_fetch_array($oversBowledResult)) {
    $oversBowled = $oversBowledRow['oversBowled'];
    $oversBowledArray[] = $oversBowled;
}
 
while($runsConcededRow = mysql_fetch_array($runsConcededResult)) {
    $runsConceded = $runsConcededRow['totalRuns'];
    $runsConcededArray[] = $runsConceded;
}
 
$loopVar = 0;
while($playersRow = mysql_fetch_array($playersResult)) {
$playerTotalGamesResult = mysql_query("SELECT COUNT(`name`) AS `totalGames` FROM battingstats GROUP BY name");
$playerTotalGamesRow = mysql_fetch_array($playerTotalGamesResult);
$totalGames = $playerTotalGamesRow['totalGames'];
$battingAverage = $playersRow['sumScore'] / $totalGames;
 
 
echo "<tr>";
echo "<td align=\"center\">" . $playersRow['name'] . "</td>";
echo "<td align=\"center\">" . $totalGames . "</td>";
echo "<td align=\"center\">" . $oversBowledArray[$loopVar] . "</td>";
echo "<td align=\"center\">" . $playersRow['sumScore'] . "</td>";
echo "<td align=\"center\">" . $battingAverage . "</td>";
echo "<td align=\"center\">" . $runsConcededArray[$loopVar] . "</td>";
echo "</tr>";
$loopVar++;
} ?>
</table>

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 8:54 am
by Eran
Is there a relationship between the 'battingstats' and 'bowlingstats' tables? it looks like the user name is used by both, but do you have a unique identifier such as 'user_id'? it's best if you posted the structure of your tables here

Re: Multiple Queries from Multiple Tables in while loop?

Posted: Sun Oct 04, 2009 9:02 am
by oscardog
pytrin wrote:Is there a relationship between the 'battingstats' and 'bowlingstats' tables? it looks like the user name is used by both, but do you have a unique identifier such as 'user_id'? it's best if you posted the structure of your tables here
The tables are not linked in anyway, no, the way this system works having user_id's isn't going to work very well. They don't register and I just think it would complicate things.

OScardog