how to output a standings table on the fly from a mysql table of football [soccer] results?

First union the scores table together swapping the hometeam with the awayteam and swapping the goal counts. This gives you some source data that is easily aggregated and the query to generate the score card is something like this:

select     team,     count(*) played,     count(case when goalsfor > goalsagainst then 1 end) wins,     count(case when goalsagainst> goalsfor then 1 end) lost,     count(case when goalsfor = goalsagainst then 1 end) draws,     sum(goalsfor) goalsfor,     sum(goalsagainst) goalsagainst,     sum(goalsfor) - sum(goalsagainst) goal_diff,    sum(          case when goalsfor > goalsagainst then 3 else 0 end         + case when goalsfor = goalsagainst then 1 else 0 end    ) score from (    select hometeam team, goalsfor, goalsagainst from scores   union all    select awayteam, goalsagainst, goalsfor from scores) a group by teamorder by score desc, goal_diff desc;

// connection stuff$sql = 'select * from matchesTable';$result = mysql_query($sql)$standings = array ();$standingTemplate = array ('matches' => 0, 'wins' => 0, 'draws' => 0, 'losses' => 0, 'goalsfor' => 0, 'goalsagainst' => 0, 'goalsdiff' => 0, 'points' => 0);while ($row = mysql_fetch_assoc($result)) {    handleMatch($row['hometeam'], $row['goalsfor'], $row['goalsagainst']);    handleMatch($row['awayteam'], $row['goalsfor'], $row['goalsagainst']);    print_r( usort(standings, 'comparePoints') );  // up to you to format the output as you like}function handleMatch($team, $goalsfor, $goalsagainst){    global $standings, $standingTemplate;    if ($goalsfor > $goalsagainst)     {        $points = 3;        $win = 1;        $draw = 0;        $loss = 0;    }    elsif ($goalsfor == $goalsagainst)     {        $points = 1;        $win = 0;        $draw = 1;        $loss = 0;    }    else     {        $points = 0        $win = 0;        $draw = 0;        $loss = 1;    }    if ( empty($standings[$team]) )$standing = $standingTemplate;    else $standing = $standings[$team];    $standing['matches']++;    $standing['wins'] += $win;    $standing['draws'] += $draw;    $standing['losses'] += $loss;    $standing['goalsfor'] += $goalsfor;    $standing['goalsagainst'] += $goalsagainst;    $standing['goalsdiff'] += $goalsfor - $goalsagainst;    $standing['points'] += $points;    $standings[$team] = $standing;}function comparePoints($a, $b){    if ($a['points'] == $b['points'])     {        if ($a['goalsdiff'] == $b['goalsdiff']) return 0;        return ($a['goalsdiff'] < $b['goalsdiff']) ? 1 : -1 ;    }           return ($a['points'] < $b['points']) ? 1 : -1 ;}

NOTES: I didn't test it and all, might be little bug (some $ or ; missing).

Recently I had to make quite more extended version of datatable. Although it is based on my own schema, but it maybe useful for someone (it is based on previous a'r answer):

  SELECT    team_id                                             team_id,    t.name                                              team_name,    t.country                                           country,    count(*)                                            matches,    SUM(scored)                                         scored_total,    SUM(conceided)                                      conceided_total,    count(CASE WHEN scored > conceided      THEN 1 END)                                       wins,    count(CASE WHEN scored = conceided      THEN 1 END)                                       draws,    count(CASE WHEN scored < conceided      THEN 1 END)                                       lost,    sum(scored) - sum(conceided)                        balance,    sum(        CASE WHEN scored > conceided          THEN 3        ELSE 0 END        + CASE WHEN scored = conceided          THEN 1          ELSE 0 END)                                   points,    count(CASE WHEN place = 'home'      THEN 1 END)                                       home_matches,    count(CASE WHEN place = 'home' AND scored > conceided      THEN 1 END)                                       home_wins,    count(CASE WHEN place = 'home' AND scored = conceided      THEN 1 END)                                       home_draws,    count(CASE WHEN place = 'home' AND scored < conceided      THEN 1 END)                                       home_lost,    SUM(CASE WHEN place = 'home'      THEN scored        ELSE 0 END)                                     home_scored,    SUM(CASE WHEN place = 'home'      THEN conceided        ELSE 0 END)                                     home_conceided,    count(CASE WHEN place = 'away'      THEN 1 END)                                       away_matches,    count(CASE WHEN place = 'away' AND scored > conceided      THEN 1 END)                                       away_wins,    count(CASE WHEN place = 'away' AND scored = conceided      THEN 1 END)                                       away_draws,    count(CASE WHEN place = 'away' AND scored < conceided      THEN 1 END)                                       away_lost,    SUM(CASE WHEN place = 'away'      THEN scored        ELSE 0 END)                                     away_scored,    SUM(CASE WHEN place = 'away'      THEN conceided        ELSE 0 END)                                     away_conceided,    GROUP_CONCAT((CASE                  WHEN scored > conceided                    THEN 'W'                  WHEN scored = conceided                    THEN 'D'                  WHEN scored < conceided                    THEN 'L'                  END) ORDER BY date ASC separator '') streak  FROM    (      (SELECT         hm.date         date,         hm.home_team_id team_id,         hm.score_home   scored,         hm.score_away   conceided,         'home'          place       FROM matches hm       WHERE hm.season_id = :seasonId)      UNION ALL      (SELECT         am.date         date,         am.away_team_id team_id,         am.score_away   scored,         am.score_home   conceided,         'away'          place       FROM matches am       WHERE am.season_id = :seasonId)    ) m    JOIN teams t ON t.id = team_id  GROUP BY team_id  ORDER BY points DESC, balance DESC;