Arrow

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

Arrow

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


  1. 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 webguy@viejolittleleague.org if you can help me out.

    thanks,
    Ed


  2. 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)


  3. 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.


  4. 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


  5. 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


  6. 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.


  7. 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


  8. 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


  9. 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.

Speak your mind

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word