Multiple Queries from Multiple Tables in while loop?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Multiple Queries from Multiple Tables in while loop?

Post 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:
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Multiple Queries from Multiple Tables in while loop?

Post by John Cartwright »

Post what you've tried for starters.
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Multiple Queries from Multiple Tables in while loop?

Post 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 :)
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Multiple Queries from Multiple Tables in while loop?

Post by oscardog »

Bump, really need a hand :banghead:
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Queries from Multiple Tables in while loop?

Post 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.
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Multiple Queries from Multiple Tables in while loop?

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Queries from Multiple Tables in while loop?

Post by Eran »

Show your updated code
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Multiple Queries from Multiple Tables in while loop?

Post 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>
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple Queries from Multiple Tables in while loop?

Post 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
oscardog
Forum Contributor
Posts: 245
Joined: Thu Oct 23, 2008 4:43 pm

Re: Multiple Queries from Multiple Tables in while loop?

Post 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
Post Reply