0

I have a query which generates a soccer standings table and am trying to create a different version which will only include the 6 most recent games for each team.

An example of the output of my current query can be seen HERE. The full query is below and I have also attached a dump of the relevant tables in a text file.

I have asked the question many times on other forums and have googled 'Show Last N records per group' for months, including another 2 hour spell just now but everything I have tried just causes empty results.

To be honest, all of the suggestions I have looked at are for just one table so I think that with 3 tables being used in my query, the whole thing becomes more complex. It is, however, possible as most UK soccer sites have this (known as a 'Current Form' Table) and an example can be found at http://www.andysstats.co.uk/form.asp?division=P&type=LT&season=200910. Out of interest, the right-most column on that table shows the actual outcome of that team's last 6 games (W for won, L for Lost etc) and that is something else I would be looking at doing eventually.

Any suggestions or pointers would be gratefully received as always.
Thanks in advance
Steve

THE MAIN QUERY

$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 
");
Attachments
-- 
-- Table structure for table `all_games`
-- 

CREATE TABLE `all_games` (
  `all_games_id` int(11) NOT NULL auto_increment,
  `date` date default NULL,
  `time` time default NULL,
  `comp` int(11) NOT NULL default '1',
  `round` tinyint(4) default NULL,
  `replay` char(1) collate latin1_general_ci default NULL,
  `home_team` int(11) NOT NULL default '1',
  `away_team` int(11) NOT NULL default '1',
  `home_goals` int(11) default NULL,
  `away_goals` int(11) default NULL,
  `attendance` int(11) default NULL,
  `report_url` varchar(255) collate latin1_general_ci default NULL,
  `photo_url` varchar(255) collate latin1_general_ci default NULL,
  `live` varchar(1) collate latin1_general_ci default NULL,
  `extratime` varchar(1) collate latin1_general_ci default NULL,
  `notes` text collate latin1_general_ci,
  `home_ht` tinyint(4) default NULL,
  `away_ht` tinyint(4) default NULL,
  `home_pen` tinyint(4) default NULL,
  `away_pen` tinyint(4) default NULL,
  PRIMARY KEY  (`all_games_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2003 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2003 ;

-- 
-- Dumping data for table `all_games`
-- 

INSERT INTO `all_games` VALUES (1, '2009-08-15', '15:00:00', 1, NULL, '0', 19, 42, 4, 0, 508, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250446274&&archive=1281131284&start_from=&ucat=10&', '', 'N', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (2, '2009-08-18', '19:45:00', 1, NULL, '0', 42, 29, 0, 4, 653, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250637449&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/6.%20mfc%20v%20Dartford%20-%2018.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (3, '2009-08-22', '15:00:00', 1, NULL, '0', 42, 30, 2, 1, 345, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1250965567&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/7.%20mfc%20v%20Boreham%20Wood%20-%2022.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (4, '2009-08-24', '19:45:00', 1, NULL, '0', 1, 42, 0, 3, 243, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251151671&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (5, '2009-08-29', '15:00:00', 1, NULL, '0', 11, 42, 3, 0, 156, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251573000&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (6, '2009-08-31', '15:00:00', 1, NULL, '0', 42, 7, 0, 1, 423, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1251746220&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/8.%20mfc%20v%20Cray%20Wanderers%20-%2031.08.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (7, '2009-09-05', '15:00:00', 1, NULL, '0', 31, 42, 2, 2, 120, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252191674&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (8, '2009-09-12', '15:00:00', 2, 2, '0', 42, 19, 2, 2, 402, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1252781008&&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (9, '2009-09-15', '19:45:00', 2, 2, 'r', 19, 42, 3, 2, 301, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253050913&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (10, '2009-09-19', '15:00:00', 1, NULL, '0', 42, 15, 1, 3, 356, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253386555&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/9.%20mfc%20v%20Horsham%20-%2019.09.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (11, '2009-09-22', '19:45:00', 1, NULL, '0', 13, 42, 1, 2, 159, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253659389&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (12, '2009-09-26', NULL, 1, NULL, '0', 42, 14, 0, 0, 355, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1253990544&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/10.%20mfc%20v%20AFC%20Hornchurch%20-%2026.09.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (13, '2009-10-03', NULL, 1, NULL, '0', 42, 16, 2, 1, 434, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1254597010&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/11.%20mfc%20v%20Kingstonian%20-%2003.10.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (14, '2009-10-10', NULL, 1, NULL, '0', 4, 42, 2, 0, 359, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255198975&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/12.%20Canvey%20Island%20v%20mfc%20-%2010.10.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (15, '2009-10-17', NULL, 3, 2, '0', 22, 42, 3, 1, 265, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1255797599&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (16, '2009-11-07', NULL, 1, NULL, '0', 12, 42, 2, 1, 517, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257626642&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (17, '2009-11-10', NULL, 5, 7, '0', 32, 42, 0, 1, 91, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1257895019&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (18, '2009-11-17', '19:45:00', 1, NULL, '0', 33, 42, 3, 4, 129, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1258495597&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (19, '2009-11-29', NULL, 1, NULL, '0', 7, 42, 3, 2, 179, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1259518899&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (20, '2009-12-05', NULL, 1, NULL, '0', 42, 11, 2, 2, 363, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260033638&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/14.%20mfc%20v%20Harrow%20Borough%20-%2005.12.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (21, '2009-12-12', NULL, 1, NULL, NULL, 35, 42, 3, 3, 316, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260614761&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (22, '2009-12-16', NULL, 5, 8, '0', 34, 42, 3, 2, 130, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1260999710&archive=1281131284&start_from=&ucat=10&', '', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (23, '2009-12-26', NULL, 1, NULL, NULL, 18, 42, 0, 1, 225, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1261853400&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/15%20Maidstone%20United%20v%20mfc%20-%2026.12.2009&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (24, '2010-01-02', '15:00:00', 1, NULL, '0', 42, 18, 0, 1, 568, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1262461878&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/16%2C%20mfc%20v%20Maidstone%20United%20-%2002.01.2010&Qiv=thumbs&Qis=M', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (25, '2010-01-16', NULL, 1, NULL, NULL, 42, 31, 1, 1, 280, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1263668584&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/17.%20mfc%20v%20Ashford%20Town%20%28Mx%29%20-%2016.01.2010&Qiv=thumbs&Qis=M', NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (26, '2010-01-23', NULL, 1, NULL, NULL, 15, 42, 3, 3, 298, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264267560&archive=1281131284&start_from=&ucat=10&', NULL, NULL, NULL, NULL, 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (27, '2010-01-26', NULL, 4, 7, '0', 42, 36, 1, 0, 79, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264545282&archive=1281131284&start_from=&ucat=10&', 'http://www.margate-fc.com/match_gallery/?Qwd=./Season%202009-10/18.%20mfc%20v%20Chatham%20Town%20%28KSC%29%20-%2026.01.2010&Qiv=thumbs&Qis=M', '', '', '', 0, 0, 0, 0);
INSERT INTO `all_games` VALUES (28, '2010-01-31', NULL, 1, NULL, NULL, 16, 42, 2, 0, 347, 'http://www.margate-fc.com/content/1st_team/match_report_archives.php?subaction=showfull&id=1264954840&archive=12
4
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by urtrivedi
0

Which parameter value have you set for season_id ? How and where do you set an initial value for rank (@rownum)?

This query code works for me (i.e. it delivers results which seem to be structurally o.k.):

set @rownum = 0;
SELECT *,
t.team_name as Tm
, @rownum := @rownum+1 AS rank
, ((sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals)OR(g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 3 ELSE 0 END)
+ sum(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) AND g.home_goals = g.away_goals THEN 1 ELSE 0 END)))  AS P
, count(CASE WHEN (g.home_team = t.team_id OR g.away_team = t.team_id) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals > g.away_goals) OR (g.away_team = t.team_id AND g.home_goals < g.away_goals) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals = g.away_goals) OR (g.away_team = t.team_id AND g.home_goals = g.away_goals) THEN 1 ELSE 0 END) AS D
, sum(CASE WHEN (g.home_team = t.team_id AND g.home_goals < g.away_goals) OR (g.away_team = t.team_id AND g.home_goals > g.away_goals) THEN 1 ELSE 0 END) AS L
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END) as GF
, SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN g.home_goals END) as GA
, (SUM(CASE WHEN (g.home_team = t.team_id) THEN g.home_goals WHEN (g.away_team = t.team_id) THEN g.away_goals END)
- SUM(CASE WHEN (g.home_team = t.team_id) THEN g.away_goals WHEN (g.away_team = t.team_id) THEN g.home_goals 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 =  103
GROUP BY t.team_id
ORDER BY P desc, GD desc, GF desc

Edited by smantscheff: n/a

0

Sorry for not getting back sooner but I have had a busy weekend!

Which parameter value have you set for season_id ?

In the actual working file the season_id is posted but for this example I have I am just using a static one which relates to the 2010 season.

How and where do you set an initial value for rank (@rownum)?

Ignore that as I have mistakenly left it in from an initial attempt to get a ranking. The rank is actually done by the 'ordinalSuffix' function and

while ($row_table = mysql_fetch_assoc($table)){
$rankings_arr[$i] = $row_table;
echo ordinalSuffix($i);

This query code works for me (i.e. it delivers results which seem to be structurally o.k.):

The query does actually work showing all the games for every team in the relevent season but I just want to show each teams last 6 games.

I was pointed to this tutorial http://explainextended.com/2009/03/05/row-sampling/ but as that is just relating to one table, I am completely confused how to get it to work with three joined tables.

In essence, what I need to do is take each team separately, get only the last 6 games for each team, and then recreate the table from those results. It seems as simple as there should be a subquery of SELECT each teams games ORDER BY date desc LIMIT 6 but all I ever can get back is the last 6 games in total, not the last 6 for each team.

Any suggestions would be gratefully received!
Thanks for taking the time to look at this.
Steve

0

may take a two part query
not formalised as code, just a thought exercise

firstarray=select from team teamid
secondarray= for each in firstarray select from games where teamid=firstarray[team] order by date desc limit 6

properly constructed would create a 2d array where rows are by teamid, and columns are team id and last 6 games

0

may take a two part query
not formalised as code, just a thought exercise

firstarray=select from team teamid
secondarray= for each in firstarray select from games where teamid=firstarray[team] order by date desc limit 6

properly constructed would create a 2d array where rows are by teamid, and columns are team id and last 6 games

That looks like a good potential solution but would not know where to start!
When you say 'for each' I presume you don't mean a php loop statement? Would a two part query take the form of a sub query of the secondarray being the sub of the firstarray.

Excuse my naivety with this but I am starting to get way out of my league with this, but am hoping to understand!

Thanks
Steve

0

I have tried doing in one query, but Not able to do it in your case, I was able to succced in other cases long back ago. So as almostbob suggested I am posting part query code which is mysql php combination (uncompiled). You may try this

<?PHP
$query="select * from team order by team_id";

$web=mysql_query($query);
echo "\n<table>";

   echo "\n<tr><td><b>team name</b></td>";

   echo "\n";
   echo "<td>col1</td>";
   echo "<td>col2</td>";
   echo "<td>col3</td>";
   echo "</tr>";

while($rowweb=mysql_fetch_assoc($web))
{

   $query="select ....... where .... and team_id='{$rowweb[team_id]}' order by date desc limit 6"; //...means your original query that you have posted with additional teamid filter and limit 6 phrase

   $rec=mysql_query($query);


   while($rowrec=mysql_fetch_assoc($rec))
   {
      echo "\n<tr>";
      echo "<td>{$rowrec['col1']}</td>";
      echo "<td>{$rowrec['col2']}</td>";
      echo "<td>{$rowrec['col3']}</td>";
      echo "</tr>";

   }

   echo "\n<tr><td><b>&nbsp</td></tr>";
}


?>

This may not give you result in order of points.
for that:
1) You may have to store all result in array,
2) perform array sort and
3) display array

Edited by urtrivedi: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.