How to calculate games behind in PHP and MySQL

First published on July 11, 2007

I’m working on a custom implementation of my sports league standings script for a baseball league (this could also come in handy for a basketball league) and one of the standings columns is games behind (GB). I’d found a great tutorial for this, but couldn’t find it the second time around! So I guess I’ll post my code instead of a link!

Let’s assume your database table looks like this:

teamid	teamname	teamwins	teamlosses
1	Stallions	42	43
2	The Fridays	43	44
3	Frames	53	34
4	Pandas	38	50
5	Alligators	52	33
6	Lightning	34	53

Games behind deals with win-loss differentials. The top team has the biggest difference between wins and losses:

// open a table and typically create other columns (the other columns have been omitted for this example)
  print '<table border=1 cellpadding=5><tr><th>Team</th><th>W</th><th>L</th>';
  print '<th>GB</th></tr>' . "\n";

// put your database connection stuff here

// run the query to select the top team and use something like games played (ideally following by other stats) to sort out a tie at the top
$query = "SELECT teamid, teamname, teamwins, teamlosses, (teamwins +  teamlosses) AS gamesplayed, (teamwins - teamlosses) AS gbvalue FROM sportsdb_teams ORDER BY gbvalue DESC, gamesplayed DESC LIMIT 1";
$result = mysql_query($query);

// grab the results of the query

  while ($topteam = mysql_fetch_array($result, MYSQL_ASSOC)) {

$gbvalue = $topteam['gbvalue'];
$teamid = $topteam['teamid'];

// Print the team stats and use "---" to indicate that it is the top team
print "<tr><td>{$topteam['teamname']}</td>";
print "<td>{$topteam['teamwins']}</td><td>{$topteam['teamlosses']}</td>";
print "<td>---</td></tr>\n";
}

———————————-

The order of the other teams is determined by the leader’s win-loss differential minus each team’s win-loss differential, divided by two ((($topteam['gbvalue']) – (teamwins – teamlosses)) / 2):

// Select the other teams
$query="SELECT teamid, teamname, teamwins, teamlosses, (teamwins  + teamlosses) AS gamesplayed, (($gbvalue - (teamwins - teamlosses)) / 2) AS gamesbehind FROM sportsdb_teams WHERE teamid != $teamid ORDER BY gamesbehind, gamesplayed";

$result=mysql_query($query);

// loop to print all the teams
while ($otherteams = mysql_fetch_array($result, MYSQL_ASSOC)) {

// Show if a team is tied for the lead
$gamesbehind = number_format($otherteams['gamesbehind'],1);
if ($gamesbehind == "0.0") $gamesbehind = "---";

// print the results
print "<tr><td>{$otherteams['teamname']}</td>";
print "<td>{$otherteams['teamwins']}</td><td>{$otherteams['teamlosses']}</td>";
print "<td>$gamesbehind</td></tr>\n";
}

// close the table
print "</table>\n";

———————————-

And here’s the result:

Team	W	L	GB
Frames	53	34	—
Alligators	52	33	—
The Fridays	43	44	10.0
Stallions	42	43	10.0
Pandas	38	50	15.5
Lightning	34	53	19.0

This entry is filed under Web development tutorials and written by Peter Keung. You can follow any responses to this entry through the RSS 2.0 feed.

9 Responses to “How to calculate games behind in PHP and MySQL”

March 11th, 2008 at 3:50 pm
Ed H says:

I am trying to figure out the Games Behind calculation. I see that you have built it in PHPâ€¦ Can I get just the calculation needed to figure this out so I can post it properly on my little leagues web site. Please drop me an email at [email protected] if you can help me out.

thanks,
Ed
March 11th, 2008 at 3:57 pm
Peter says:

1) The top team has the biggest difference between wins and losses; we'll call this top_team_gb

2) To figure out the GB for each subsequent team:

(((top_team_gb – (current_team_wins – current_team_losses)) / 2)
April 24th, 2008 at 6:11 am
Tyler says:

Hey, i like this simple league system. Although its not right for me i have changed some stuff for it to work better. But my question is im trying to get the points behind instead of GB. I’ve tried several methods but failed all.
April 24th, 2008 at 10:01 pm
Peter says:

Hi, points behind would be quite similar to GB. Assuming you don’t already have a column for points, the first query would be something like:

SELECT teamid, teamname, teamwins, teamlosses, (teamwins + teamlosses) AS gamesplayed, (teamwins * 2) AS points FROM sportsdb_teams ORDER BY points DESC, gamesplayed ASC LIMIT 1

Then the second query would be similar, but you would subtract the point results from the leader.

SELECT teamid, teamname, teamwins, teamlosses, (teamwins + teamlosses) AS gamesplayed, ($leader_points - (teamwins *2)) AS points_behind FROM sportsdb_teams WHERE teamid != $teamid ORDER BY points_behind, gamesplayed DESC
August 17th, 2008 at 1:41 pm
KJH278 says:

1. How do you calculate game behind when ties are involved

2. Is there a web site that will make question # 1 easier

I need this info for fantasy baseball
August 17th, 2008 at 11:25 pm
Peter says:

Hi KJH278, I don’t know of an official calculation for games behind when ties are involved as there are several ways to consider them (ignore ties, consider them half-wins, or many other ways). You’ll have to tweak the script yourself to correspond to whatever formula is relevant for your league.
June 6th, 2009 at 4:52 am
Howitzer says:

I just modified my GB calculation to account for ties as 1/2 games.

=((1stW-W-(T/2))+(L+(T/2)-1stL))/2

1st place Wins minus Wins minus half the Ties
plus
Losses plus half the Ties minus 1st place Losses

divided by 2
June 14th, 2009 at 8:39 pm
sjd says:

Hey, Howitzer –

Can you repost the formula but differentiate between the wins/losses & ties for each team? That is Wa,La,Ta & Wb,Lb & Tb.

I’ve tried to work the formula below (assume Team "a" is the leader):

2Wa-(La+g)+Ta = 2(Wb+g)-Lb+Tb (now, rearrange)
2Wa-2Wb+Lb-La+Ta-Tb = 2g+g
2(Wa-Wb)+(Lb-La)+(Ta-Tb) = 3g
g = 1/3[2(Wa-Wb)+(Lb-La)+(Ta-Tb)]

This gives games behind in increments of 1/3. I can’t find a prob with the math, but maybe somebody else can. I’ve also read an alternate solution to give a team 1/2 a win & 1/2 a loss for a tie game, and then the "classic" formula should hold.

Any thoughts?

Thanks,

sjd7aa
at gmail d0t com
July 19th, 2010 at 1:48 pm
Rob says:

Since the "Games Back" formula already divides by two (i.e., divides in half), there’s no need to count a tie as a half-win and half-loss. Instead, take the tie differential between the two teams and add to the existing "Games Back" equation. So: ((leader’s wins – leader’s losses) – (trailing team’s wins – trailing team’s losses) – (leader’s ties – trailing team’s ties))/2

Example: Leader team A is 4-0, 2nd place team B is 4-0-1.

(leader’s wins – leader’s losses) = 4-0 = 4
(trailing team’s wins – trailing team’s losses) = 4-0 = 4
(leader’s ties – trailing team’s ties) = 0-1 = -1
4-4-(-1)= 1
1/2 = 0.5
So 2nd place team B is half-game behind leader team A.