[SOLVED] Mathematical Query

[Crustyfur]

Again I haven't been php'ing for long and I have hit a problem. I am querying a mysql db and I want to display some info based on the results of comparing feilds in another table.

 

Part of the page with show a league table entry. Games played, how many have been lost, won or drawn and the for and against scores. Now I have half of this sorted and working, however, the part that works out how many games have been won lost or drawn is where I am at.

 

Each game is stored in the db. It has the score for and the score against. So can php work out how many games have been W, L or D and make a count accordingly?

 

This is where I am at.

 

<?php
echo "<table width=\"100%\" border=\"1\" cellpadding=\"3\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\">
<tr>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Played</b></td>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Won</b></td>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Drawn</b></td>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Lost</b></td>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>For</b></td>
<td bgcolor=\"#01CDFF\" width=\"13%\" align=\"center\"><b>Against</b></td>
<td bgcolor=\"#01CDFF\" width=\"22%\" align=\"center\"><b>League Points</b></td>
</tr>";

$query = "SELECT COUNT(fid), SUM(ffor), SUM(fagg) FROM hh_fixtures WHERE ftype = '2'";

$result = mysql_query($query);

if (mysql_num_rows($result) > 0)

while($row = mysql_fetch_row($result))
{

echo "<tr>
<td width=\"13%\" align=\"center\">". $row[0] ."</td>
<td width=\"13%\" align=\"center\"></td>
<td width=\"13%\" align=\"center\"></td>
<td width=\"13%\" align=\"center\"></td>
<td width=\"13%\" align=\"center\">". $row[1] ."</td>
<td width=\"13%\" align=\"center\">". $row[2] ."</td>
<td width=\"22%\" align=\"center\"></td>
</tr>";

?>

[Barand]

this should help

 

the table used

DROP TABLE IF EXISTS `test2`.`hh_fixtures`;
CREATE TABLE `hh_fixtures` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `teamID` int(10) unsigned NOT NULL,
  `ffor` int(10) unsigned NOT NULL,
  `fagg` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`)
)

 

the data

ID Tm F  A
--+--+--+--
1, 1, 2, 1
2, 1, 1, 4
3, 1, 2, 2
4, 1, 1, 0
5, 1, 0, 3
6, 2, 2, 2
7, 2, 3, 2
8, 2, 0, 1
9, 2, 1, 1
10, 2, 1, 0

 

the query

SELECT teamID
      , COUNT(*) as played
      , SUM(IF(ffor > fagg, 1,0)) as won
      , SUM(IF(ffor = fagg, 1,0)) as drawn
      , SUM(IF(ffor < fagg, 1,0)) as lost
      , SUM(ffor) as totalfor
      , SUM(fagg) as totalag
      , SUM(CASE WHEN ffor > fagg THEN 3 WHEN ffor = fagg THEN 1 ELSE 0 END) as points
FROM hh_fixtures
GROUP BY teamID

 

the results

[pre]

teamID| played| won  | drawn| lost| totalfor| totalag| points

1    | 5    |  2  |  1  |  2  |  6      |  10    |  7

2    | 5    |  2  |  2  |  1  |  7      |  6    |  8

[/pre]

[Crustyfur]

Barand, your a star. Works like a treat.

 

THANKS!

[Crustyfur]

Famous last words..

 

Just a small bug. Above code is counting fixtures which not been played yet. How can I exclude these?

 

Thanks again.

[Barand]

...

FROM hh_fixtures

WHERE datecol <= CURDATE()

GROUP BY teamID

[Crustyfur]

Thank you barand. I am very greatful.

 

Chris

[Crustyfur]

Just when I thought it was safe.

 

The website I am building is for a netball team. I have just been told their league scoring and it isn't as I expected. i.e. 3 points win 1 point draw and 0 points for a loss. It is in fact:

 

5 for a Win

3 for a Draw

1 for a loss but 50% of the score

0 for a loss below 50% of the score

 

How do I get the below code to reflect the last two scoring options:

 

SUM(CASE WHEN ffor > fagg THEN 3 WHEN ffor = fagg THEN 1 ELSE 0 END) as points

 

Thanks again.

[Barand]

SUM(CASE WHEN ffor > fagg THEN 5 WHEN ffor = fagg THEN 3 ELSE IF(ffor < fagg/2,0,1) END) as points