DB query with sum function

[trekky]

So I have this football database query which is calculating a teams record versus another team. This works well but, it only displays a row with sum vs one or all teams combined. How do I proceed to display several rows vs more teams?

 

This is the main code:

 

<?php



$id = $_GET['id'];

$game = $_GET['game'];

$status = $_GET['status'];


$grab_totals = 

"SELECT '$game' as idt, '$status' as status, SUM(CASE WHEN (idh LIKE '%$id%' AND ida LIKE '%$game%' AND status LIKE '$status' AND gf > ga)

OR (ida LIKE '%$id%' AND idh LIKE '%$game%' AND status LIKE '$status' AND ga > gf) THEN 1 ELSE 0 END)

AS w, 

SUM(CASE WHEN (idh LIKE '%$id%' AND ida LIKE '%$game%' AND status LIKE '$status' AND gf < ga)

OR (ida LIKE '%$id%' AND idh LIKE '%$game%' AND status LIKE '$status' AND ga < gf) THEN 1 ELSE 0 END)

AS l, 

SUM(CASE WHEN (idh LIKE '%$id%' AND ida LIKE '%$game%' AND status LIKE '$status' AND gf = ga)

OR (ida LIKE '%$id%' AND idh LIKE '%$game%' AND status LIKE '$status' AND ga = gf) THEN 1 ELSE 0 END) 

AS d, 

SUM(CASE WHEN (idh LIKE '%$id%' AND ida LIKE '%$game%' AND status LIKE '$status') THEN gf

WHEN (ida LIKE '%$id%' AND idh LIKE '%$game%' AND status LIKE '$status') THEN ga

END) as gf, 

SUM(CASE WHEN (idh LIKE '%$id%' AND ida LIKE '%$game%' AND status LIKE '$status') THEN ga

WHEN (ida LIKE '%$id%' AND idh LIKE '%$game%' AND status LIKE '$status') THEN gf

END) as ga

FROM soccer GROUP BY '$id'";


$result = @mysql_query($grab_totals);

sql_query("$result", "$error_messages[04]");


$num_rows = mysql_num_rows($result);



IF (mysql_num_rows($result) == 0)

{

echo ("<SPAN CLASS='common_text'>

No records found.

</SPAN>");

}



ELSE

{

?>



<DIV ALIGN='CENTER'>

<TABLE BORDER=0>

<TR>

<TD WIDTH=200 CLASS='table_header' ALIGN='CENTER'>

<B>Team</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>P</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>W</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>D</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>L</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>GF</B>

</TD>

<TD WIDTH=40 CLASS='table_header' ALIGN='CENTER'>

<B>GA</B>

</TD>

</TR>


<?php



$row_counter = 0;



while ($row = mysql_fetch_array($result))

{

++$row_counter;

$idt = $row["idt"];

$w = $row["w"];

$d = $row["d"];

$l = $row["l"];

$gf = $row["gf"];

$ga = $row["ga"];

rounded ($row_counter);



$grab_totals2 = "SELECT * FROM soccer WHERE (status LIKE '$status' AND (idh LIKE '%$id%' OR ida LIKE '%$id%'))

		 AND (status LIKE '$status' AND (idh LIKE '%$game%' OR ida LIKE '%$game%'))";

$result2 = @mysql_query($grab_totals2);

$num_rows2 = mysql_num_rows($result2);




?>



<TR>



<TD ALIGN='CENTER' CLASS='<?php require ("s_team_config.php"); echo ($row_background); ?>'>

<?php echo ($idt); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($num_rows2); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($w); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($d); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($l); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($gf); ?>

</TD>



<TD ALIGN='CENTER' CLASS='<?php echo ($row_background); ?>'>

<?php echo ($ga); ?>

</TD>



</TR>

</table>





<?php

}

?>





<?php

}

?>

 

In this case, "game" is the opponent and I'd like to get a listing vs all available opponents..

[sasa]

look http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

[trekky]

Little more help on track please..