Help tweaking League Standings query

[MargateSteve]

I have finally worked out how to get a football league table query up and running (mainly by stealing code from elsewhere and adapting it to work the way I want it to!) but need to try to tweak it for a couple of other pages.

 

The test page with it on is at http://www.margate-fc.com/content/test/table.php

 

The query is

$i = 1;

$ht = "g.home_team = t.team_id";
$at = "g.away_team = t.team_id";
$hw = "g.home_goals > g.away_goals";
$aw = "g.home_goals < g.away_goals";
$d = "g.home_goals = g.away_goals";
$hg ="g.home_goals";
$ag ="g.away_goals";

$table = mysql_query("SELECT 
t.team_name as Tm, @rownum := @rownum+1 AS rank


, (sum(CASE WHEN (".$ht." AND ".$hw.")OR(".$at." AND ".$aw.") THEN 3 ELSE 0 END)
+ sum(CASE WHEN (".$ht." OR ".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS P
, (sum(CASE WHEN (".$ht." AND ".$hw.") THEN 3 ELSE 0 END) 
+ sum(CASE WHEN (".$ht.") AND ".$d." THEN 1 ELSE 0 END)) AS HP
, (sum(CASE WHEN (".$at." AND ".$aw.") THEN 3 ELSE 0 END) 
+ sum(CASE WHEN (".$at.") AND ".$d." THEN 1 ELSE 0 END)) AS AP

, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." ) THEN 1 ELSE 0 END) as HGP
, sum(CASE WHEN ".$at." THEN 1 ELSE 0 END) as AGP

, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW
, sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW

, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$d.") THEN 1 ELSE 0 END) AS HD
, sum(CASE WHEN (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS AD

, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, sum(CASE WHEN (".$ht." AND ".$aw.") THEN 1 ELSE 0 END) AS HL
, sum(CASE WHEN (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS AL

, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$hg." END) as HGF
, SUM(CASE WHEN (".$at.") THEN ".$ag." END) as AGF

, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, SUM(CASE WHEN (".$ht.") THEN ".$ag." END) as HGA
, SUM(CASE WHEN (".$at.") THEN ".$hg." END) as AGA


, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)
- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." END) 
- SUM(CASE WHEN (".$ht.") THEN ".$ag." END)) as HGD
, (SUM(CASE WHEN (".$at.") THEN ".$ag." END) 
- SUM(CASE WHEN (".$at.") THEN ".$hg." END)) as AGD

from teams t
left join all_games g 
on t.team_id in (g.home_team,g.away_team)

WHERE comp = '1' AND home_goals IS NOT NULL AND date BETWEEN '2010-07-01' AND '2011-06-31'  
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc

 

and the html (although some of the css still is not done and some code still needs tidying) is

 <table width="" border="0" cellpadding="0" cellspacing="0" BORDER=1 RULES=ROWS FRAME=BOX>
<tr>
       <td></td><td></td>
       <td colspan="9" align="center" bgcolor="#00FF99">ALL</td>
       <td colspan="9" align="center" >Home</td>
       <td colspan="9" align="center">Away</td>
</tr>
<tr>
       <td class="hdcell" >POS</td>
       <td class="hdcell" >Team</td>
       <td width="30" class="hdcell">P</td>
       <td width="30" class="hdcell">W</td>
       <td width="30" class="hdcell">D</td>
       <td width="30" class="hdcell">L</td>
       <td width="30" class="hdcell">F</td>
       <td width="30" class="hdcell">A</td>
       <td width="30" class="hdcell">GD</td>
       <td width="30" class="hdcell">Pts</td>
       <td width="30" class="hdcell"></td>
       <td></td>
       <td width="30" class="hdcell">P</td>
       <td width="30" class="hdcell">W</td>
       <td width="30" class="hdcell">D</td>
       <td width="30" class="hdcell">L</td>
       <td width="30" class="hdcell">F</td>
       <td width="30" class="hdcell">A</td>
       <td width="30" class="hdcell">GD</td>
       <td width="30" class="hdcell">Pts</td>
       <td></td>
       <td width="30" class="hdcell">P</td>
       <td width="30" class="hdcell">W</td>
       <td width="30" class="hdcell">D</td>
       <td width="30" class="hdcell">L</td>
       <td width="30" class="hdcell">F</td>
       <td width="30" class="hdcell">A</td>
       <td width="30" class="hdcell">GD</td>
       <td width="30" class="hdcell">Pts</td>
</tr>
        <?php while ($row_table = mysql_fetch_assoc($table)){ 
	    
echo '<tr>
       <td style="text-align:left" width="30">'.$i.'</td>';
       echo '<td style="text-align:left">'.$row_table['Tm'].'</td>
       <td style="text-align:left">'.$row_table['GP'].'</td>
       <td style="text-align:left">'.$row_table['W'].'</td>
       <td style="text-align:left"> '.$row_table['D'].'</td>
       <td style="text-align:left"> '.$row_table['L']. '</td>
       <td style="text-align:left"> '.$row_table['GF']. '</td>
       <td style="text-align:left"> '.$row_table['GA']. '</td>
       <td style="text-align:left"> '.$row_table['GD']. '</td>
       <td style="text-align:left"> '.$row_table['P']. '</td>
       <td style="text-align:left"></td>
       <td style="text-align:left"></td>
       <td style="text-align:left">'.$row_table['HGP'].'</td>
       <td style="text-align:left">'.$row_table['HW'].'</td>
       <td style="text-align:left">'.$row_table['HD'].'</td>
       <td style="text-align:left"> '.$row_table['HL']. '</td>
       <td style="text-align:left"> '.$row_table['HGF']. '</td>
       <td style="text-align:left"> '.$row_table['HGA']. '</td>
       <td style="text-align:left"> '.$row_table['HGD']. '</td>
       <td style="text-align:left"> '.$row_table['HP']. '</td>
       <td style="text-align:left"></td>
       <td style="text-align:left">'.$row_table['AGP'].'</td>
       <td style="text-align:left">'.$row_table['AW'].'</td>
       <td style="text-align:left">'.$row_table['AD'].'</td>
       <td style="text-align:left"> '.$row_table['AL']. '</td>
       <td style="text-align:left"> '.$row_table['AGF']. '</td>
       <td style="text-align:left"> '.$row_table['AGA']. '</td>
       <td style="text-align:left"> '.$row_table['AGD']. '</td>
       <td style="text-align:left"> '.$row_table['AP']. '</td>
</tr>';
$i++;
       }
    ?>
      </table>

 

As I said, this works fine in the test page but there are two other things that I am looking to do with it and one way I hope to streamline the code.

 

Firstly the code bit.....

There is a bit of replication that I was hoping to eliminate by using the sum function but from what I can see this cannot be done with Alias's.

I was hoping to change the code

, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW
, sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW

to

 , sum(HW + PW) AS W
, sum(CASE WHEN (".$ht." AND ".$hw.") THEN 1 ELSE 0 END) AS HW
, sum(CASE WHEN (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS AW

but that just causes no records to be shown. Is there a way around this or is there no way to aggregate alias's?

 

Secondly, the other pages. There are 2 other pages that I want to use similar data on and I think what I want to do is quite simple.

 

On the front page of the site I want a stripped down version of the table which shows just five records, that of my team, Margate, and the two positions above and two below.

At the moment Margate are in 19th position so I would want it to show rows 17,18,19,20,21. If Margate were 10th I would want it to show 8,9,10,11,12.

I would imagine that there is a way of finding which position Margate is in then setting the OFFSET to that -2 but after extensive Googling (I did spot it in exactly the context I need some time back when I did not need it!) I cannot find anything as I basically do not know what I need to ask for!

 

The final thing is that each team has its own page and on that page I want to show their position in the League in a friendly style, such as 19th as opposed to 19. I would imagine that I would need to pull the whole league table in as opposed to using a query that matches the team otherwise it would only have one result and each team would show as being 1st. I would only want to show the position of the relevant team but obviously the whole query would need to be run to get that position. How would I then find the relative teams position?

I think that the ideal thing would be to use a VIEW but am working with a MySQL 4 database.

 

Any help or advice will, as always, be appreciated.

Thanks in advance

Steve

 

[jcbones]

Try:

SUM(HW) + SUM(PW) AS W

 

Which will add each column, then add them together.

[jcbones]

For adding the `th to the end of the numbers, you will have 1st, 2nd, 3rd, everything else is th.  So a simple switch would fix this.

 

switch(substr($pos,-1)) {
case 1:
    $position = $pos . 'st';
    break;
case 2:
    $position = $pos . 'nd';
     break;
  case 3:
    $position = $pos . 'rd';
    break;
  default:
    $position = $pos . 'th';
}

 

As for the ordering the queries.  I could help you more if I knew what all the letters were.  With a shared site, and a mid/large database(30,000/60,000+ rows), these calculations would possibly end in your site being suspended for memory hogging.

[MargateSteve]

Apologies for not replying sooner but my hard drive died. I have a new one now and once I have recovered my old data I will give this a go.

Thanks

Steve

[MargateSteve]

I have had a chance to play with these now and have had indifferent success!!

SUM(HW) + SUM(PW) AS W

This ends up in no records being returned. Anything I try to do that aggregates any alias's does exactly the same. It actually works as it is but I just wanted to tidy things up.

 

switch(substr($pos,-1)) {

case 1:

    $position = $pos . 'st';

    break;

case 2:

    $position = $pos . 'nd';

    break;

  case 3:

    $position = $pos . 'rd';

    break;

  default:

    $position = $pos . 'th';

}

This very nearly works. The only problem is that 11, 12 and 13 show as 11st, 12nd and 13rd. I have tried to put extra CASE statements in to allow for this and also tried to tweak it using an IF/Else statement (basically saying that if $pos between 11 and 13 then use $position = $pos . 'th' otherwise use the switch) but there is a problem somewhere with the syntax as it just kept returning an error pointing to the first line of the if statement.

 

In my head the IF/ELSE route be the best way to sort it but my head is not very clued up in these things. I have found some code to do this using functions but as I have not used them before, I am not sure where to put them in my code or how to get them to work!

 

Steve

[BlueSkyIS]

lots of examples via google. here's one.

 

http://www.phpro.org/examples/Ordinal-Suffix.html

[MargateSteve]

http://www.phpro.org/examples/Ordinal-Suffix.html

 

That is perfect so massive thanks!! I had found a few similar through searching but this was the first to show how to execute the function in the HTML.

 

As I think I have confused things by asking several questions in the same post, I will mark this solved now and ask the other questions separately.

 

Thanks again

Steve