Query within a query help please?

Pilly

Hi,
Im trying to make a league table

I have a table for example, this is for 1 game played.
Name GF GA Points Game Result
Chucky 1 0 3 FIFA W
Pilly 0 1 0 FIFA L

My code is

<body bgcolor="#384C53">
<?
include ("config.php");
mysql_select_db($dbname);
$_GET['Game'];

$sql4 = "SELECT * , Count(points_table.Name) AS TCount, Sum(Points) As TPoints, Sum(GA) as GA, Sum(GF) as GF
FROM points_table
WHERE Game = '$Game'

GROUP BY Name
ORDER BY TPoints DESC
";

$res4 = mysql_query($sql4) or die(mysql_error());
if(mysql_num_rows($res4) > 0) {

 echo "<table border='1' cellspacing='0' bordercolor='#000066' width='80%' height='10' cellpadding='0'>";
 echo "<tr>";
 echo "<td height='20' width='120' bgcolor='#283F44' bordercolor='#FFFFFF'><font face='Verdana' size='1' color ='FFFFFF'><b>Name</font></b></td>";
 echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'><font face='Verdana' size='1' color ='FFFFFF'><b>Games Played</font></b></td>";
 echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'><font face='Verdana' size='1' color ='FFFFFF'><b>Won</font></b></td>";
 echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'><font face='Verdana' size='1' color ='FFFFFF'><b>Drawn</font></b></td>";
 echo "</tr>";

while($row=mysql_fetch_array($res4)) {
$Name = $row['Name'];
$Played = $row['TCount'];

$sub_result = mysql_query("SELECT COUNT(Result) AS WCount FROM points_table WHERE Result = 'W'");
while( $sub_row = mysql_fetch_array($sub_result) )
{
$Won = $row[WCount];
}

}
else {
echo " No records found";
}
?>

Now Im trying to count all the wins/draws/losses for each person. each stored in the table as W/D/L

Any ideas on how can i complete this???

trexx

try this...

select
  name,
  sum(if (result = 'W', 1, 0)) win,
  sum(if (result = 'D', 1, 0)) draw,
  sum(if (result = 'L', 1, 0)) lose
from game
group by name

Pilly

is it a problem with the query? As i cant get anything outputed from this sub query?

trexx

wrong table name... =)

select
  name,
  sum(if (result = 'W', 1, 0)) win,
  sum(if (result = 'D', 1, 0)) draw,
  sum(if (result = 'L', 1, 0)) lose
from points_table 
where game="FIFA"
group by name

try to execute this query directly in your database and look what happens. it should output...

NAME     W D L         

chucky   1 0 0
pilly    0 0 1

(that's what i have as output in my test table filled with the values from your example)

Pilly

Yes that works, now how do i incorporate this within the first query, and get this outputted within the first query?

is the field called $win ???

trexx

ok... now i see... you don't have to do a "subquery". you can select all you need with one single query.

select
  name,
  count(*)                     tcount,
  sum(points)                  tpoints,
  sum(ga)                      ga,
  sum(gf)                      gf,  

  sum(if (result = 'W', 1, 0)) win,
  sum(if (result = 'D', 1, 0)) draw,
  sum(if (result = 'L', 1, 0)) lose
from points_table 
where game="FIFA"
group by name

to access the result set, use this

$q = '[the query above]';
$rs = mysql_query($q);
while ($row = mysql_fetch_assoc($rs)) {
   echo $row['name'];
   echo $row['win'];
   ...
}

Pilly

Many thanks

Ive changed the code to

<body bgcolor="#384C53">
<?
include ("config.php");
mysql_select_db($dbname);
$_GET['Game'];

$sql4 = " SELECT
Name,
count(*) AS TCount,
Sum(Points) AS TPoints,
sum(GA) AS GA,
sum(GF) AS GF,

sum(if (result = 'W', 1, 0)) Win,
sum(if (result = 'D', 1, 0)) Draw,
sum(if (result = 'L', 1, 0)) Lose
from points_table
where game='$Game'
group by name
";
$rs = mysql_query($sql4);
//$res4 = mysql_query($sql4) or die(mysql_error());
if(mysql_num_rows($rs) > 0) {
echo "<table border='1' cellspacing='0' bordercolor='#000066' width='80%' height='10' cellpadding='0'>";
echo "<tr>";
echo "<td height='20' width='120' bgcolor='#283F44' bordercolor='#FFFFFF'>Name</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>Games Played</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>Won</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>Drawn</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>Lost</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>GF</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>GA</td>";
echo "<td height='20' width='80' bgcolor='#283F44' bordercolor='#FFFFFF'>Points</td>";
echo "</tr>";
while ($row = mysql_fetch_assoc($rs)) {
$Name = $row['Name'];
$Played = $row['TCount'];
$Won = $row['Win'];
$Drawn = $row['Draw'];
$Lose = $row['Lose'];
$GF = $row['GF'];
$GA = $row['GA'];
$Points = $row['TPoints'];

echo "<td height='30' width='20%' bgcolor='#EFF3F7' bordercolor='#FFFFFF'><a href='League.php?Game=$Game'>".$Name." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$Played." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$Won." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$Drawn." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$Lose." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$GF." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$GA." </a></td>";
echo "<td height='20' width='80' bgcolor='#EFF3F7' bordercolor='#FFFFFF'>".$Points." </a></td>";
echo "</tr>"; }
?>
<tr><td colspan="6" bgcolor="#283F44" height="25" background="tile_sub.gif"></td></tr>
</table>
<?
}
else {
echo " No records found - Possible Cause To Early!!";
}
?>

My problem is that Ive added more results and the W/D/L is still staying the same although the Points, GF/GA are ok.

NAME W D L

chucky 1 0 0
pilly 0 0 1

Can you explain this part to me so i can work if this is the problem???

sum(if (result = 'W', 1, 0)) win,
sum(if (result = 'D', 1, 0)) draw,
sum(if (result = 'L', 1, 0)) lose

trexx

maybe the results you've added are for another game?

what the statements does is this: it groups the rows by name. now we have some rows for each user. it goes through these rows and looks for each row if the result is W. if thats true it selects 1 or otherwise 0 for the column win. the same goes for D and draw and L and lose. in the end it sums the columns win, draw and lose.

could you post the data that's in your table? (a CSV file would be great)

Pilly

Just checked the data, the W/L?D wasnt set.

It works.

Many many thanks for your help

trexx

ok... you've got no value at all for result in the other columns ... that's the reason why you see the same values. if you want to count the rows with no value for result too you should add another line to our statement.

select
  name,
  count(*)                       tcount,
  sum(points)                    tpoints,
  sum(ga)                        ga,
  sum(gf)                        gf,  

  sum(if (result = 'W', 1, 0))   win,
  sum(if (result = 'D', 1, 0))   draw,
  sum(if (result = 'L', 1, 0))   lose,
  sum(if (result is null, 1, 0)) empty
from points_table 
where game="FIFA"
group by name

so your column result can be either W, L, D or NULL.

trexx

ok... great =) your welcome!