SUM a column from a MySQL Query

[ceeandcee]

As you can see from this page: http://www.cornwallfantasyhockey.com/cfhl/schedule3/
I have created an output table of 11 players. I would now like to total the GP and PTS column.

Here is what I have so far: any thoughts?

<?php

$result = mysql_query("SELECT * FROM stats1011 where 1A = 'BULL' OR 1B = 'BULL' order by field(pos,'LW','C','RW','D','G'), Last ASC")
or die(mysql_error());  
   

?>


<?php
echo "<table width='150' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<tr bgcolor='000000'>
<td align='center'colspan='5'><img src='/images/bar_bull.jpg' width='150' height='19' border='0'></td>
</tr>

<tr>
<td colspan='3' align='left' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>Player</b></td>
<td align='center' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td align='center' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>

</tr>
";

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	


	echo "<tr><td width='15' align='center' bgcolor='FFFF99'><font face='arial' size='1' color='000000'><b>";
	echo $row['Pos']; 
	echo "</b></td><td width='100' align='left' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['Last'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['Team'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['GP1011_wk1'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['PTS1011_wk1'];
	
} 
echo "<tr><td colspan='3' align='left' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>TOTALS</b></td>";
echo "<td align='center' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'>";
echo $sum['GP1011_wk1'];
echo "</td><td align='center' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'>";
echo $sum['PTS1011_wk1'];
echo "</td></table>";

?>

[hielo]

assuming your fields are named `GP` and `PTS`:

<?php

$result = mysql_query("SELECT * FROM stats1011 where 1A = 'BULL' OR 1B = 'BULL' order by field(pos,'LW','C','RW','D','G'), Last ASC")
or die(mysql_error());  
   

?>


<?php
echo "<table width='150' border='1' cellspacing='0' cellpadding='1' bgcolor='ffffff'>";
echo "<tr> 
<tr bgcolor='000000'>
<td align='center'colspan='5'><img src='/images/bar_bull.jpg' width='150' height='19' border='0'></td>
</tr>

<tr>
<td colspan='3' align='left' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>Player</b></td>
<td align='center' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>GP</b></td>
<td align='center' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>PTS</b></td>

</tr>
";

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
	// Print out the contents of each row into a table
	


	echo "<tr><td width='15' align='center' bgcolor='FFFF99'><font face='arial' size='1' color='000000'><b>";
	echo $row['Pos']; 
	echo "</b></td><td width='100' align='left' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['Last'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['Team'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['GP1011_wk1'];
	echo "</td><td width='15' align='center' bgcolor='ffffff' align='center'><font face='arial' size='1' color='000000'>";
	echo $row['PTS1011_wk1'];
	
} 
$totals=mysql_query("SELECT SUM(`GP`) as GP1011_wk1, SUM(`PTS`) as PTS1011_wk1 FROM `stats1011` where 1A = 'BULL' OR 1B = 'BULL' ") or die(mysql_error());
$sum=mysql_fetch_assoc($totals);
echo "<tr><td colspan='3' align='left' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'><b>TOTALS</b></td>";
echo "<td align='center' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'>";
echo $sum['GP1011_wk1'];
echo "</td><td align='center' width='100' bgcolor='000000'><font face='arial' size='1' color='ffffff'>";
echo $sum['PTS1011_wk1'];
echo "</td></table>";

?>

[ceeandcee]

Thanks Hielo...I will try this in the morning. One further question, once I get these values, is there a way to store them and use them elsewhere throughout the site? (For example, I will need these totals to update the standings page)

[hielo]

Make sure that your page STARTS with session_start(); THEN (later on) you can assign to/retrieve values from $_SESSION.

In your case, instead of $sum use $_SESSION - ex:

$_SESSION['sum']=mysql_fetch_assoc($totals);
...
echo $_SESSION['sum']['GP1011_wk1'];
...
echo $_SESSION['sum']['PTS1011_wk1'];
...

Lastly, on the OTHER php page(s) where you want to use those values, you MUST also call session_start() at the beginning of the file first, THEN you can echo the values.

[ceeandcee]

Thank you Hielo!