I have a query which generates a soccer standings table and although it works fine there I am trying to implement a couple of variations on it but everything I have tried has failed. I have searched and asked many times and for both problems and it seems as though a subquery would be the solution, but every attempt I have made has not given the desired result.
An example of the output can be seen at http://www.margate-fc.com/content/test/standings.php?season_id=103 for the 2010 season and http://www.margate-fc.com/content/test/standings.php?season_id=104 for the 2011 season.
I have also attached a dump of the relevant tables in a text file.
The first problem I have is creating a sum from two different tables. In the following query, the 'Pts' column is generated from the following on lines 70 and 71
, ((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 PThe 'Adj' column is pulled from a separate table (line 80) joined by two columns (line 85)
, d.change as DIFF
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)Teams can have points deducted from them during a season and this needs to be reflected in the actual 'Pts' column so I need to somehow add 'P' to 'DIFF'.
As a test, I have deducted 50 points from Margate (team_id 42) for 2010 (season_id 103) and 100 points from Croydon Athletic (team_id 8) for 2011 (season_id 104) in the deductions table. These both show up correctly on the relevant seasons standings (with the links I gave back at the start) in the 'Adj' column, but everything I have tried to add 'P' to 'DIFF' has not worked.
Some of the time it works for the relevant team but the 'Pts' show up as blank for everyone else. Other times it just adds 'DIFF' to everyone. The other attempt that got me close was to show the relevant team and the right 'Pts' but no other teams showed up.
The full query is....
$i = 1;
/*ADD th, st, rd TO THE POSITION NUMBERS*/
/*
* @return number with ordinal suffix
* @param int $number
* @param int $ss Turn super script on/off
* @return string
*/
function ordinalSuffix($i, $ss=0)
{
/*** check for 11, 12, 13 ***/
if ($i % 100 > 10 && $i %100 < 14)
{
$os = 'th';
}
/*** check if number is zero ***/
elseif($i == 0)
{
$os = '';
}
else
{
/*** get the last digit ***/
$last = substr($i, -1, 1);
switch($last)
{
case "1":
$os = 'st';
break;
case "2":
$os = 'nd';
break;
case "3":
$os = 'rd';
break;
default:
$os = 'th';
}
}
/*** add super script ***/
$os = $ss==0 ? $os : '<sup>'.$os.'</sup>';
/*** return ***/
return $i.$os;
};
/*GET THE CORRECT SEASON*/
if (isset($_GET['season_id']) ? $_GET['season_id'] : 1);
$season_id = $_GET['season_id'];
/*COMMON STANDINGS QUERY STRINGS*/
$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";
/*THE STANDINGS QUERY*/
$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
, count(CASE WHEN (".$ht." OR ".$at.") THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (".$ht." AND ".$hw.") OR (".$at." AND ".$aw.") THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (".$ht." AND ".$d.") OR (".$at." AND ".$d.") THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (".$ht." AND ".$aw.") OR (".$at." AND ".$hw.") THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END) as GF
, SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END) as GA
, (SUM(CASE WHEN (".$ht.") THEN ".$hg." WHEN (".$at.") THEN ".$ag." END)
- SUM(CASE WHEN (".$ht.") THEN ".$ag." WHEN (".$at.") THEN ".$hg." END)) as GD
, d.change as DIFF
from teams t
left join all_games g
on t.team_id in (g.home_team,g.away_team)
LEFT JOIN seasons as S ON g.date BETWEEN S.season_start AND S.season_end
LEFT JOIN deductions d ON ( d.team = t.team_id) AND (d.season = S.season_id)
WHERE comp = '1' AND home_goals IS NOT NULL
AND S.season_id = $season_id
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc
");The second thing I am trying to do should be extremely simple but isn't!
With this one, I want to show the standings but only taking into account the 6 most recent games for each team. The output would be something like what can be seen at http://www.andysstats.co.uk/form.asp?division=P&type=LT&season=200910 with out the right hand column.
I have followed so many different bits of advice on this and have never even got close although, once again, almost all of the advice suggested a subquery would be the solution.
The only time that I manage to get a query that did not fail or return no records it just pulled the most recent 6 games from the database and not the most recent 6 for each team.
If you are still reading this, then thank you! It was hard to decide how to word it and I thought two different posts would be frowned upon as both problems are working with the same query. Hopefully I have provided all the information needed but if there is anything else you need to know, then please let me know.
Any suggestions or pointers would be gratefully received and I would be more than happy for someone to mention it if the current query design is flawed and advise on a better approach.
Thanks in advance
Steve