If Statement Help

[Pilly]

Im trying to total the wins and losses on one field.

Heres what ive got so far.

<?
include ("config.php3");

$connection = mysql_connect("$dbhost","$dbusername","$dbpasswd") or die ("Couldn't connect to server.");
$db = mysql_select_db("$database_name", $connection) or die("Couldn't select database.");

$sql = "SELECT , count() AS cnt
FROM phplog
GROUP BY field9
Order BY cnt
DESC
";

$counter = 0;
$LoseCounter = 0;
$WinCounter = 0;

$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) > 0) {

// Outputs Table etc... (Removed)

while($row=mysql_fetch_array($res)) {

  $Name   = $row['field6'];

   if ( $Name == "Win") {
		$WinCounter= $WinCounter + 1;
   } 

   if ( $Name == "Lose") {
		$LoseCounter= $LoseCounter + 1;
   } 


 echo "<tr>";
 $counter= $counter +1;
 $ID1   = $row['field9'];


           /// Displays results in Table (removed)

            Echo $LoseCounter;
            Echo $WinCounter;
            echo $Counter;


 echo "</tr>";
} echo "</table>";

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

Ive removed the html etc, so the thread isnt to long. THe results from $losecounter and $wincounter are way out. What am i doing wrong. Is there an easier way to do this?

[cpalermo]

I think that your SQL statement is not returning what you think it is. Have you tried to run this statement directly in MySQL? You should try that, I think you will get something compeletey different than you expect.

[Pilly]

The query is ok.
Got...
Name Played Wins Losses
Tate 5 0 0

Now the field is called field6 and it is text, and i write Win or lose in the field. I just want to be able to total all the Wins and all the Loses.

[cpalermo]

Well, if the SQL query is ok, then the php is wrong. It seems that you have 5 total entries for field Tate in your table. When you use GROUP BY in the SQL command, you will only get one row returned for Tate (because you grouped by Field9).

Yet your php code seems to be expecting that your query will return all 5 rows, and trying to loop through them all test the Field6 to add 1 to $WinCounter each time it sees a "Win", and 1 to $LoseCounter each time it sees a "Lose" - but it can't because you've only returned 1 row.

[cpalermo]

...here is something to try:

I'd rewrite both the php and sql.

SQL

SELECT Field9 as Field_Name,
count(*) AS Total_Games_On_Field,
SUM(CASE WHEN Field6 = 'Win' THEN 1 ELSE 0 END) AS Wins,
SUM(CASE WHEN Field6 = 'Lose' THEN 1 ELSE 0 END) AS Losses,
FROM phplog
GROUP BY Field9;

This will give you 1 row returned for each field that your games are played on. Each row will consist of 4 columns(Field_Name, Total_Games_On_Field, Wins, Losses), and you can just print them out.

PHP

$res = mysql_query($sql) or die(mysql_error());
if(mysql_num_rows($res) > 0) {

// Outputs Table etc... (Removed)

while($row=mysql_fetch_array($res)) {

echo "Field: " . $row['Field_Name'] . "<br>";
echo "Total Games: " . $row['Total_Games_On_Field'] . "<br>";
echo "Wins: " . $row['Wins] . "<br>";
echo "Losses: " . $row['Losses'] . "<br>";

}

HTH