Sort sports standings array

beeker · July 10, 2012

I have a function that displays a sports league standings (have multiple divisions to show) that I need to sort by points. I call other functions to get wins and losses then do the math to get points.

Here is the main function followed by the other functions I call:

<?php
function show_standings($sDiv)
{
echo "<p align='center'><b>".$sDiv. "</b></p>";
echo "<table border='1' cellpadding='5' align='center'>";
echo "<tr>";
echo "<td align='left'><b>Team</b></td>";
echo "<td align='center'>GP</td>";
echo "<td align='center'>W</td>";
echo "<td align='center'>L</td>";
echo "<td align='center'>T</td>";
echo "<td align='center'>Pts</td>";
echo "<td align='center'>Win%</td>";
echo "<td align='center'>Last 10</td>";
echo "<td align='center'>GF</td>";
echo "<td align='center'>GA</td>";
echo "<td align='center'>PIM</td>";
echo "</tr>";
$getStanding = get_teaminfo($sDiv);
for ($i=0; $i<count($getStanding); $i++)
{
  echo "<tr>";
  echo "<td>".$getStanding[$i]['teamname']."</td>";
  echo "<td>".getTeamStatsGames($getStanding[$i]['team_id'])."</td>"; //GAmes playes
  echo "<td>".getTeamStatsWins($getStanding[$i]['team_id'])."</td>"; //Wins
  echo "<td>".getTeamStatsLosses($getStanding[$i]['team_id'])."</td>"; //Loss
  echo "<td>".getTeamStatsTies($getStanding[$i]['team_id'])."</td>"; //Ties
  echo "<td>".(getTeamStatsWins($getStanding[$i]['team_id'])*2 + getTeamStatsTies($getStanding[$i]['team_id']))."</td>"; //Points
  echo "<td>".trim(number_format(((getTeamStatsWins($getStanding[$i]['team_id']) + getTeamStatsTies($getStanding[$i]['team_id'])/2) / getTeamStatsGames($getStanding[$i]['team_id'])), 3),'0')."</td>"; //Win %
  echo "<td>".getTeamWinsLast10($getStanding[$i]['team_id'])."-".getTeamLossLast10($getStanding[$i]['team_id'])."-".getTeamTieLast10($getStanding[$i]['team_id'])."</td>"; //last 10
  echo "<td>".getTeamStatsGoalsFor($getStanding[$i]['team_id'])."</td>"; //Goals for
  echo "<td>".getTeamStatsGoalsAgin($getStanding[$i]['team_id'])."</td>"; //Goals Against
  echo "<td>".getTeamStatsPIM($getStanding[$i]['team_id'])."</td>"; //PIMS
  echo "</tr>";
  }
  echo "</table>";
  echo "<br><br>";
  }

function get_teaminfo($div)
{
$sql = "select teams.teamname, teams.team_id
from teams, division
where division.div_id = teams.divis_id 
and division.divisname='".$div."' 
and teams.season_id = ".CONST_SEASONID;
$results = mysql_query($sql) or die(mysql_error());
$teaminfo = array();
while ($row = mysql_fetch_array($results))
{
$teaminfo[] = $row;
}
return $teaminfo;
}

function getTeamStatsWins($sTeamID)
{
$sql = "SELECT count(*) from games, teams
WHERE ((games.ateam_id = teams.team_id and games.whowon = 'A') or
(games.hteam_id = teams.team_id and games.whowon = 'H'))
AND teams.team_id = ".$sTeamID;
$results = mysql_query($sql) or die(mysql_error());
$GW = mysql_fetch_row($results);
return($GW[0]);
}

function getTeamStatsLosses($sTeamID)
{
$sql = "SELECT count(*) from games, teams
WHERE ((games.ateam_id = teams.team_id and games.whowon = 'H') or
(games.hteam_id = teams.team_id and games.whowon = 'A'))
AND teams.team_id = ".$sTeamID;
$results = mysql_query($sql) or die(mysql_error());
$GL = mysql_fetch_row($results);
return($GL[0]);
}

?>

On the main page I display the info this way:

<?php
show_standings("Red Division");
show_standings("White Division");
show_standings("Blue Division");

?>

Is there a way to sort this table on the points?

edit - pleeeease use code tags.

xyph · July 10, 2012

Please use the

tags

beeker · July 10, 2012

My apologies.

<?php
function show_standings($sDiv)
{
echo "<p align='center'><b>".$sDiv. "</b></p>";
echo "<table border='1' cellpadding='5' align='center'>";
echo "<tr>";
echo "<td align='left'><b>Team</b></td>";
echo "<td align='center'>GP</td>";
echo "<td align='center'>W</td>";
echo "<td align='center'>L</td>";
echo "<td align='center'>T</td>";
echo "<td align='center'>Pts</td>";
echo "<td align='center'>Win%</td>";
echo "<td align='center'>Last 10</td>";
echo "<td align='center'>GF</td>";
echo "<td align='center'>GA</td>";
echo "<td align='center'>PIM</td>";
echo "</tr>";
$getStanding = get_teaminfo($sDiv);
for ($i=0; $i<count($getStanding); $i++)
{
  echo "<tr>";
  echo "<td>".$getStanding[$i]['teamname']."</td>";
  echo "<td>".getTeamStatsGames($getStanding[$i]['team_id'])."</td>"; //GAmes playes
  echo "<td>".getTeamStatsWins($getStanding[$i]['team_id'])."</td>"; //Wins
  echo "<td>".getTeamStatsLosses($getStanding[$i]['team_id'])."</td>"; //Loss
  echo "<td>".getTeamStatsTies($getStanding[$i]['team_id'])."</td>"; //Ties
  echo "<td>".(getTeamStatsWins($getStanding[$i]['team_id'])*2 + getTeamStatsTies($getStanding[$i]['team_id']))."</td>"; //Points
  echo "<td>".trim(number_format(((getTeamStatsWins($getStanding[$i]['team_id']) + getTeamStatsTies($getStanding[$i]['team_id'])/2) / getTeamStatsGames($getStanding[$i]['team_id'])), 3),'0')."</td>"; //Win %
  echo "<td>".getTeamWinsLast10($getStanding[$i]['team_id'])."-".getTeamLossLast10($getStanding[$i]['team_id'])."-".getTeamTieLast10($getStanding[$i]['team_id'])."</td>"; //last 10
  echo "<td>".getTeamStatsGoalsFor($getStanding[$i]['team_id'])."</td>"; //Goals for
  echo "<td>".getTeamStatsGoalsAgin($getStanding[$i]['team_id'])."</td>"; //Goals Against
  echo "<td>".getTeamStatsPIM($getStanding[$i]['team_id'])."</td>"; //PIMS
  echo "</tr>";
  }
  echo "</table>";
  echo "<br><br>";
  }

function get_teaminfo($div)
{
$sql = "select teams.teamname, teams.team_id
from teams, division
where division.div_id = teams.divis_id
and division.divisname='".$div."'
and teams.season_id = ".CONST_SEASONID;
$results = mysql_query($sql) or die(mysql_error());
$teaminfo = array();
while ($row = mysql_fetch_array($results))
{
$teaminfo[] = $row;
}
return $teaminfo;
}

function getTeamStatsWins($sTeamID)
{
$sql = "SELECT count(*) from games, teams
WHERE ((games.ateam_id = teams.team_id and games.whowon = 'A') or
(games.hteam_id = teams.team_id and games.whowon = 'H'))
AND teams.team_id = ".$sTeamID;
$results = mysql_query($sql) or die(mysql_error());
$GW = mysql_fetch_row($results);
return($GW[0]);
}

function getTeamStatsLosses($sTeamID)
{
$sql = "SELECT count(*) from games, teams
WHERE ((games.ateam_id = teams.team_id and games.whowon = 'H') or
(games.hteam_id = teams.team_id and games.whowon = 'A'))
AND teams.team_id = ".$sTeamID;
$results = mysql_query($sql) or die(mysql_error());
$GL = mysql_fetch_row($results);
return($GL[0]);
}

?>

<?php

show_standings("Red Division");
show_standings("White Division");
show_standings("Blue Division");
?>

edit - pleeeease use code tags with the correct brackets

Barand · July 10, 2012

You appear to looping through the array and calling about 15 sql queries for each team !!!

That is not exactly an efficient solution. Post your table structures - there has to be something better.

beeker · July 10, 2012

Barand,

Here is the structure of the main tables that the data is based off of:

TABLE `division` (
  `div_id` int(10) unsigned NOT NULL,
  `divisname` tinytext COLLATE latin1_general_ci NOT NULL,
  `shrtname` tinytext COLLATE latin1_general_ci NOT NULL,
  UNIQUE KEY `div_id` (`div_id`)
)

TABLE `games` (
  `game_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `season_id` smallint(5) unsigned NOT NULL,
  `gamedate` date NOT NULL,
  `gametime` time NOT NULL,
  `hteam_id` smallint(5) unsigned DEFAULT NULL,
  `ateam_id` smallint(5) unsigned DEFAULT NULL,
  `gamenum` smallint(5) unsigned DEFAULT NULL,
  `complete` tinyint(3) unsigned NOT NULL,
  `forfeited` tinyint(3) unsigned NOT NULL,
  `homescore` tinyint(3) unsigned DEFAULT NULL,
  `awayscore` tinyint(3) unsigned DEFAULT NULL,
  `whowon` varchar(1) COLLATE latin1_general_ci DEFAULT NULL,
  `perplayed` tinyint(3) unsigned DEFAULT NULL,
  `wentot` tinyint(3) unsigned NOT NULL,
  `wenttoso` tinyint(4) NOT NULL,
  `location` varchar(12) COLLATE latin1_general_ci DEFAULT NULL,
  `ref1` tinyint(3) unsigned DEFAULT '0',
  `ref2` tinyint(3) unsigned DEFAULT '0',
  `scrkpr` tinyint(3) unsigned DEFAULT '0',
  `gamenotes` text COLLATE latin1_general_ci,
  PRIMARY KEY (`game_id`)
) 

TABLE `teams` (
  `team_id` smallint(5) unsigned NOT NULL,
  `divis_id` int(11) NOT NULL,
  `teamcode` varchar(5) COLLATE latin1_general_ci DEFAULT NULL,
  `teamname` varchar(35) COLLATE latin1_general_ci DEFAULT NULL,
  `season_id` smallint(5) unsigned DEFAULT NULL,
  `url` varchar(150) COLLATE latin1_general_ci DEFAULT NULL,
  `rosterapp` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`team_id`)
) 
TABLE `players` (
  `player_id` mediumint( unsigned NOT NULL AUTO_INCREMENT,
  `jerseynum` smallint(5) unsigned DEFAULT NULL,
  `suspended` tinyint(3) unsigned NOT NULL,
  `stksuspended` tinyint(3) unsigned NOT NULL,
  `position` varchar(2) COLLATE latin1_general_ci DEFAULT NULL,
  `lastname` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `firstname` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `probation` tinyint(3) unsigned NOT NULL,
  `team_id` smallint(5) unsigned DEFAULT NULL,
  `season_id` smallint(5) unsigned DEFAULT NULL,
  `active` tinyint(3) unsigned DEFAULT NULL,
  `expelled` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`player_id`)
) 

TABLE `goals` (
  `GOAL_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `GAME_ID` int(10) unsigned NOT NULL,
  `TEAM_ID` smallint(5) unsigned NOT NULL,
  `PLAYER_ID` int(10) unsigned NOT NULL,
  `ASSIST1_ID` int(10) unsigned DEFAULT NULL,
  `PERIOD` tinyint(3) unsigned DEFAULT NULL,
  `POWERPLAY` tinyint(3) unsigned NOT NULL,
  `SHORTHAND` tinyint(3) unsigned NOT NULL,
  `EMPTYNET` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`GOAL_ID`)
)

Basically the overall game info goes into the game table, each goal is entered into the goal table and a player requires a unique player_id for each team they play for each year they play (it is a rec league so people on multiple teams is quite common). Of note might be the knowledge that I am currently converting an ASP/M$ site to php/mysql (I am a noob) and this is the current method we have in asp/M$. I have decided to convert and am somewhat basing this off what we currently do.

If more info is required let me know.

edit - I beg of you.... pleeeease use code tags

xyph · July 10, 2012

Again, please use the code tags.

This time, please actually follow my example rather than guessing your own.

...your codes goes here...

beeker · July 10, 2012

Barand,

Here is the structure of the main tables that the data is based off of:

TABLE `division` (
  `div_id` int(10) unsigned NOT NULL,
  `divisname` tinytext COLLATE latin1_general_ci NOT NULL,
  `shrtname` tinytext COLLATE latin1_general_ci NOT NULL,
  UNIQUE KEY `div_id` (`div_id`)
)

TABLE `games` (
  `game_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `season_id` smallint(5) unsigned NOT NULL,
  `gamedate` date NOT NULL,
  `gametime` time NOT NULL,
  `hteam_id` smallint(5) unsigned DEFAULT NULL,
  `ateam_id` smallint(5) unsigned DEFAULT NULL,
  `gamenum` smallint(5) unsigned DEFAULT NULL,
  `complete` tinyint(3) unsigned NOT NULL,
  `forfeited` tinyint(3) unsigned NOT NULL,
  `homescore` tinyint(3) unsigned DEFAULT NULL,
  `awayscore` tinyint(3) unsigned DEFAULT NULL,
  `whowon` varchar(1) COLLATE latin1_general_ci DEFAULT NULL,
  `perplayed` tinyint(3) unsigned DEFAULT NULL,
  `wentot` tinyint(3) unsigned NOT NULL,
  `wenttoso` tinyint(4) NOT NULL,
  `location` varchar(12) COLLATE latin1_general_ci DEFAULT NULL,
  `ref1` tinyint(3) unsigned DEFAULT '0',
  `ref2` tinyint(3) unsigned DEFAULT '0',
  `scrkpr` tinyint(3) unsigned DEFAULT '0',
  `gamenotes` text COLLATE latin1_general_ci,
  PRIMARY KEY (`game_id`)
)

TABLE `teams` (
  `team_id` smallint(5) unsigned NOT NULL,
  `divis_id` int(11) NOT NULL,
  `teamcode` varchar(5) COLLATE latin1_general_ci DEFAULT NULL,
  `teamname` varchar(35) COLLATE latin1_general_ci DEFAULT NULL,
  `season_id` smallint(5) unsigned DEFAULT NULL,
  `url` varchar(150) COLLATE latin1_general_ci DEFAULT NULL,
  `rosterapp` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`team_id`)
)
TABLE `players` (
  `player_id` mediumint( unsigned NOT NULL AUTO_INCREMENT,
  `jerseynum` smallint(5) unsigned DEFAULT NULL,
  `suspended` tinyint(3) unsigned NOT NULL,
  `stksuspended` tinyint(3) unsigned NOT NULL,
  `position` varchar(2) COLLATE latin1_general_ci DEFAULT NULL,
  `lastname` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `firstname` varchar(25) COLLATE latin1_general_ci DEFAULT NULL,
  `probation` tinyint(3) unsigned NOT NULL,
  `team_id` smallint(5) unsigned DEFAULT NULL,
  `season_id` smallint(5) unsigned DEFAULT NULL,
  `active` tinyint(3) unsigned DEFAULT NULL,
  `expelled` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`player_id`)
)

TABLE `goals` (
  `GOAL_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `GAME_ID` int(10) unsigned NOT NULL,
  `TEAM_ID` smallint(5) unsigned NOT NULL,
  `PLAYER_ID` int(10) unsigned NOT NULL,
  `ASSIST1_ID` int(10) unsigned DEFAULT NULL,
  `PERIOD` tinyint(3) unsigned DEFAULT NULL,
  `POWERPLAY` tinyint(3) unsigned NOT NULL,
  `SHORTHAND` tinyint(3) unsigned NOT NULL,
  `EMPTYNET` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`GOAL_ID`)
)

Basically the overall game info goes into the game table, each goal is entered into the goal table and a player requires a unique player_id for each team they play for each year they play (it is a rec league so people on multiple teams is quite common). Of note might be the knowledge that I am currently converting an ASP/M$ site to php/mysql (I am a noob) and this is the current method we have in asp/M$. I have decided to convert and am somewhat basing this off what we currently do.

If more info is required let me know.

edit - I'm sensing a pattern here..

KevinM1 · July 10, 2012

beeker, once again, use

tags. Note that

IS NOT THE SAME AS <code></code>. The square brackets are intentional.

Barand · July 10, 2012

You could use something like this which just uses a single query

SELECT x.divis_id as Division, x.teamname as Team, SUM(x.played) as Games, SUM(x.win) Won,
SUM(x.lose) as Lost, SUM(x.tie) as Tied, SUM(x.pts) as Pts, SUM(goalsfor) as GoalsFor, SUM(goalsagainst) AS GoalsAgainst
FROM
(
    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore > awayscore, 1,0) as win
    , IF(homescore < awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore>awayscore THEN 3 WHEN awayscore>homescore THEN 0 ELSE 1 END as pts
    , homescore as goalsfor
    , awayscore as goalsagainst
    FROM games g INNER JOIN teams t ON g.hteam_id = t.team_id

    UNION ALL

    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore < awayscore, 1,0) as win
    , IF(homescore > awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore<awayscore THEN 3 WHEN awayscore<homescore THEN 0 ELSE 1 END as pts
    , awayscore as goalsfor
    , homescore as goalsagainst
    FROM games g INNER JOIN teams t ON g.ateam_id = t.team_id

) as x
GROUP BY Team
ORDER BY Division, Pts DESC

As the CASE statement shows it uses 3 pts for a win, 1 for a tie and none for a lose. You should be able to extend it for other fields.

With my test data it gives

+----------+------------------+-------+------+------+------+------+----------+--------------+

+----------+------------------+-------+------+------+------+------+----------+--------------+

| 1 | Blackburn Rovers | 8 | 7 | 0 | 1 | 22 | 44 | 24 |

| 1 | Man Utd | 8 | 4 | 3 | 1 | 13 | 41 | 36 |

| 1 | Arsenal | 8 | 3 | 4 | 1 | 10 | 43 | 27 |

| 1 | Chelsea | 8 | 3 | 5 | 0 | 9 | 32 | 54 |

| 1 | Man City | 8 | 1 | 6 | 1 | 4 | 31 | 50 |

| 2 | Stockport | 8 | 4 | 1 | 3 | 15 | 39 | 25 |

| 2 | Blackpool | 8 | 4 | 3 | 1 | 13 | 41 | 36 |

| 2 | Wigan | 8 | 3 | 4 | 1 | 10 | 28 | 33 |

| 2 | Macclesfield | 8 | 3 | 4 | 1 | 10 | 31 | 38 |

| 2 | Preston | 8 | 3 | 5 | 0 | 9 | 30 | 37 |

+----------+------------------+-------+------+------+------+------+----------+--------------+

beeker · July 11, 2012

That is awesome but how would I put in a WHERE clause to show only a single division for a single season? We give each season a number (season_id field) and running this against my DB it shows everything from the beginning of the league.

Barand · July 11, 2012

In each part of the subquery

SELECT x.divis_id as Division, x.teamname as Team, SUM(x.played) as Games, SUM(x.win) Won,
SUM(x.lose) as Lost, SUM(x.tie) as Tied, SUM(x.pts) as Pts, SUM(goalsfor) as GoalsFor, SUM(goalsagainst) AS GoalsAgainst
FROM
(
    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore > awayscore, 1,0) as win
    , IF(homescore < awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore>awayscore THEN 3 WHEN awayscore>homescore THEN 0 ELSE 1 END as pts
    , homescore as goalsfor
    , awayscore as goalsagainst
    FROM games g INNER JOIN teams t ON g.hteam_id = t.team_id
    WHERE t.divis_id = X and g.season_id = Y                                                        /* here */

    UNION ALL

    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore < awayscore, 1,0) as win
    , IF(homescore > awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore<awayscore THEN 3 WHEN awayscore<homescore THEN 0 ELSE 1 END as pts
    , awayscore as goalsfor
    , homescore as goalsagainst
    FROM games g INNER JOIN teams t ON g.ateam_id = t.team_id
    WHERE t.divis_id = X and g.season_id = Y                                                 /* and here */

) as x
GROUP BY Team
ORDER BY Division, Pts DESC

beeker · July 11, 2012

Barand,

Thank you so much for this!

beeker · July 11, 2012

I now just need to add the total penalty minutes to the SQL query!

Barand · July 11, 2012

Where are those stored?

beeker · July 11, 2012

penalties are stored in this table:

TABLE `penalty` (
  `penalty_id` int(10) unsigned NOT NULL,
  `game_id` int(10) unsigned NOT NULL,
  `team_id` smallint(5) unsigned NOT NULL,
  `player_id` mediumint( unsigned DEFAULT NULL,
  `penltycode` varchar(5) COLLATE latin1_general_ci DEFAULT NULL,
  `period` tinyint(3) unsigned DEFAULT NULL,
  `minutes` tinyint(3) unsigned DEFAULT NULL,
  `offtime` varchar(6) COLLATE latin1_general_ci DEFAULT NULL,
  `benchpenal` tinyint(3) unsigned NOT NULL,
  `PIMWeight` decimal(4,1) DEFAULT NULL,
  PRIMARY KEY (`penalty_id`)
)

Here is the non-efficient function I was using (players and teams table structure is earlier in thread for reference):

function getTeamStatsPIM($sTeamID)
{
$sql = "SELECT sum(minutes) from penalty, players, teams
WHERE penalty.player_id = players.player_id
and players.team_id = teams.team_id
AND teams.team_id = " .$sTeamID;
$results = mysql_query($sql) or die(mysql_error());
$TPIM = mysql_fetch_row($results);
return ($TPIM[0]);
}

redarrow · July 11, 2012

barand how you do it , are you a mysql designer, it like you no every think about mysql structure, your fast cool dude

You could use something like this which just uses a single query
SELECT x.divis_id as Division, x.teamname as Team, SUM(x.played) as Games, SUM(x.win) Won,
SUM(x.lose) as Lost, SUM(x.tie) as Tied, SUM(x.pts) as Pts, SUM(goalsfor) as GoalsFor, SUM(goalsagainst) AS GoalsAgainst
FROM
(
    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore > awayscore, 1,0) as win
    , IF(homescore < awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore>awayscore THEN 3 WHEN awayscore>homescore THEN 0 ELSE 1 END as pts
    , homescore as goalsfor
    , awayscore as goalsagainst
    FROM games g INNER JOIN teams t ON g.hteam_id = t.team_id

    UNION ALL

    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore < awayscore, 1,0) as win
    , IF(homescore > awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore<awayscore THEN 3 WHEN awayscore<homescore THEN 0 ELSE 1 END as pts
    , awayscore as goalsfor
    , homescore as goalsagainst
    FROM games g INNER JOIN teams t ON g.ateam_id = t.team_id

) as x
GROUP BY Team
ORDER BY Division, Pts DESC
As the CASE statement shows it uses 3 pts for a win, 1 for a tie and none for a lose. You should be able to extend it for other fields.

With my test data it gives

+----------+------------------+-------+------+------+------+------+----------+--------------+

| Division | Team | Games | Won | Lost | Tied | Pts | GoalsFor | GoalsAgainst |

+----------+------------------+-------+------+------+------+------+----------+--------------+

| 1 | Blackburn Rovers | 8 | 7 | 0 | 1 | 22 | 44 | 24 |

| 1 | Man Utd | 8 | 4 | 3 | 1 | 13 | 41 | 36 |

| 1 | Arsenal | 8 | 3 | 4 | 1 | 10 | 43 | 27 |

| 1 | Chelsea | 8 | 3 | 5 | 0 | 9 | 32 | 54 |

| 1 | Man City | 8 | 1 | 6 | 1 | 4 | 31 | 50 |

| 2 | Stockport | 8 | 4 | 1 | 3 | 15 | 39 | 25 |

| 2 | Blackpool | 8 | 4 | 3 | 1 | 13 | 41 | 36 |

| 2 | Wigan | 8 | 3 | 4 | 1 | 10 | 28 | 33 |

| 2 | Macclesfield | 8 | 3 | 4 | 1 | 10 | 31 | 38 |

| 2 | Preston | 8 | 3 | 5 | 0 | 9 | 30 | 37 |

+----------+------------------+-------+------+------+------+------+----------+--------------+

Barand · July 11, 2012

barand how you do it , are you a mysql designer...

Experience. I have been developing

- business systems and programs since 1968,

- SQL and database development since 1985,

- PHP since 2001

Barand · July 11, 2012

With penalty total minutes

SELECT x.divis_id as Division, x.teamname as Team, SUM(x.played) as Games, SUM(x.win) Won,
SUM(x.lose) as Lost, SUM(x.tie) as Tied, SUM(x.pts) as Pts, SUM(x.goalsfor) as GoalsFor, 
SUM(x.goalsagainst) AS GoalsAgainst, SUM(x.penalty) as Penalties
FROM
(
    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore > awayscore, 1,0) as win
    , IF(homescore < awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore>awayscore THEN 3 WHEN awayscore>homescore THEN 0 ELSE 1 END as pts
    , homescore as goalsfor
    , awayscore as goalsagainst
    , SUM(minutes) as penalty
    FROM games g 
        INNER JOIN teams t ON g.hteam_id = t.team_id
        LEFT JOIN penalty p ON g.game_id = p.game_id AND g.hteam_id = p.team_id
     t.divis_id = X AND g.season_id = Y
    GROUP BY g.game_id, g.hteam_id
    
    UNION ALL

    SELECT t.divis_id
    , t.teamname
    , 1 as played
    , IF(homescore < awayscore, 1,0) as win
    , IF(homescore > awayscore, 1,0) as lose
    , IF(homescore = awayscore, 1,0) as tie
    , CASE WHEN homescore<awayscore THEN 3 WHEN awayscore<homescore THEN 0 ELSE 1 END as pts
    , awayscore as goalsfor
    , homescore as goalsagainst
    , SUM(minutes) as penalty
    FROM games g 
        INNER JOIN teams t ON g.ateam_id = t.team_id
        LEFT JOIN penalty p ON g.game_id = p.game_id AND g.ateam_id = p.team_id
    WHERE t.divis_id = X AND g.season_id = Y
    GROUP BY g.game_id, g.ateam_id

) as x
GROUP BY Team
ORDER BY Division, Pts DESC

beeker · July 12, 2012

When I pass the values for X & Y in t.divis_id = X AND g.season_id = Y (x = 8 and y = 34), I receive this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't.divis_id = 8 AND g.season_id = 34 GROUP BY g.game_id, g.hteam_id ' at line 19.

The mysql version is Server version: 5.1.63-0ubuntu0.11.10.1

Barand · July 12, 2012

I forgot the "WHERE"

WHERE t.divis_id = X ...

beeker · July 13, 2012

Once again, thank you!!! Works like a charm, on to the next page. I am inspired to really learn mysql!

Sign In

Sort sports standings array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information