League Standings Table

[lukep11a]

I am trying to create a league standings table and can't seem to find anything to help. I can do it all as seperate queries but it is getting very messy and wondered if theres is a way to select it all in one query. I have a teams table, a fixtures table and a results table.

 

Teams table: team_id, team_name

Fixtures table: fixture_id, home_team_id, away_team_id, date

Results table: fixture_id, home_goals, away_goals

 

Basically I want to be able to create a standings table ranking the teams in order of points, I want to present this table on the fly and not put it into the database like the example below:

 

Pos Team      Pld  W  D  L  F  A    GD  Pts

1  Man Utd    5    2  3  0  8  5    3    9

2  Arsenal    6    2  2  2  11 10  1    8

3  Chelsea    6    2  2  2  8  8    0    8

4  Man City  5    0  3  2  8  12  -4  3

 

W=won, D=Draw, L=Loss, F=Goals scored For, A=Goals scored against, GD=Goals difference Pts=Points (3 points for a win, 1 point for a draw)

 

I think the most efficient way to do this would be to assign wins, draws and losses, sum the goals scored and goals scored against. I can do them all individually but how would I put it into one?

 

[xyph]

Is there any reason to not have home_goals and away_goals in the fixtures table?

[lukep11a]

The only reason i have done that is because I want to display upcoming fixtures for teams and then show them as results once they are complete

[xyph]

Wouldn't filtering by date/time be sufficient for that?

[lukep11a]

I originally thought about just having a fixtures table, I came up with two issues though:

 

1) If I didn't manage to update the table with the score as soon as the game had finished the date field would show that the game had been played and there would be no data to display

 

2) How would I update the fixtures data set with the scores quickly, as there could be around 50 results to enter at a time i wouldnt want to be entering them manually. I currently copy and paste them and then use excel formulas to strip them down so I can quickly upload import them into mySQL.

 

I am open to suggestions though if you think there is a better way to do it

[xyph]

Have an extra column, boolean, named game_over. If that column is 0, no score has been entered so it shouldn't be displayed, but at the same time, it won't be displayed in 'upcoming games' queries due to the date being passed.

 

You could dump the results into a temporary table, and then perform an update. Here's how I'd do it with a sample table

 

mysql> show columns from temp_table;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| value | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> show columns from perm_table;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| value | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> select * from temp_table;
+----+-------+
| id | value |
+----+-------+
|  1 | foo   |
|  2 | bar   |
|  3 | cas   |
|  4 | bah   |
+----+-------+
4 rows in set (0.00 sec)

mysql> select * from perm_table;
+----+-------+
| id | value |
+----+-------+
|  1 |       |
|  2 |       |
|  3 |       |
|  4 |       |
+----+-------+
4 rows in set (0.00 sec)

mysql> UPDATE perm_table p
    ->   INNER JOIN temp_table t ON p.id = t.id
    -> SET
    ->   p.value = t.value;
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from perm_table;
+----+-------+
| id | value |
+----+-------+
|  1 | foo   |
|  2 | bar   |
|  3 | cas   |
|  4 | bah   |
+----+-------+
4 rows in set (0.00 sec)

[Barand]

May be of interest:

 

http://forums.phpfreaks.com/index.php?topic=362277

 

there is a single query solution in that thread

[MargateSteve]

The way I do it is if home_goals = null, it is still classed as a fixture. However, I was recently told on phpfreaks that I should have the goals in a separate table.

 

Steve

[xyph]

Well, normalizing your data is generally good, but it's possible to 'over-normalize.' When you over-normalize, you're cause MySQL to do more work than it has to without adding any benefit.

 

If you were tracking goals by player_id as well as game_id, it would be worth it to normalize it to another table, but for now, your results table will only ever have one row for any given fixture_id. In this case, you're better off storing the data side-by-side. You don't gain any benefit moving it to it's own table, besides making the individual tables smaller (this only become an advantage in the millions of row tables). Even then, you're talking a couple bytes per row, which really isn't a big deal.

 

And while Barand's SQL query in that thread he posted is totally kick-ass, it might be more efficient to perform some of those calculations in PHP... It's worth benchmarking at least, if efficiency is a concern. If the results are close, his solution is far and beyond the 'better' one, based on the fact that you're using SQL to handle all data manipulation, and PHP to simply format the way the data is presented.

[lukep11a]

Thank you all for your help. Xyph, so are you saying it would be better in my case to do something in one single query? I am always looking for better/more efficient ways of doing things as I am still learning. I have used Barand's suggestion from another thread that seems to do the trick. I still have one problem though, do you know how I can get each team from the team table to show up even when they haven't played any games yet? I thought using left joins instead of inner joins would do it to but it doesn't seem to be working.

 

Here is the code that I have amended slightly to work for my tables:

 

SELECT x.team_name 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.team_name
    , 1 as played
    , IF(ht_goals > at_goals, 1,0) as win
    , IF(ht_goals < at_goals, 1,0) as lose
    , IF(ht_goals = at_goals, 1,0) as tie
    , CASE WHEN ht_goals > at_goals THEN 3 WHEN ht_goals < at_goals THEN 0 ELSE 1 END as pts
    , ht_goals as goalsfor
    , at_goals as goalsagainst
    FROM results g INNER JOIN fixtures ON g.fixture_id = fixtures.fixture_id INNER JOIN teams t ON fixtures.ht_id = t.team_id

    UNION ALL

    SELECT t.team_name
    , 1 as played
    , IF(ht_goals < at_goals, 1,0) as win
    , IF(ht_goals > at_goals, 1,0) as lose
    , IF(ht_goals = at_goals, 1,0) as tie
    , CASE WHEN ht_goals < at_goals THEN 3 WHEN ht_goals > at_goals THEN 0 ELSE 1 END as pts
    , at_goals as goalsfor
    , ht_goals as goalsagainst
    FROM results g INNER JOIN fixtures ON g.fixture_id = fixtures.fixture_id INNER JOIN teams t ON fixtures.at_id = t.team_id

) as x
GROUP BY Team
ORDER BY Pts DESC

[Barand]

this should pull in teams with no results

 

SELECT t.team_name 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
teams t
LEFT JOIN
(
    SELECT fixtures.ht_id as team_id
    , 1 as played
    , IF(ht_goals > at_goals, 1,0) as win
    , IF(ht_goals < at_goals, 1,0) as lose
    , IF(ht_goals = at_goals, 1,0) as tie
    , CASE WHEN ht_goals > at_goals THEN 3 WHEN ht_goals < at_goals THEN 0 ELSE 1 END as pts
    , ht_goals as goalsfor
    , at_goals as goalsagainst
    FROM results g INNER JOIN fixtures ON g.fixture_id = fixtures.fixture_id 

    UNION ALL

    SELECT fixtures.at_id as team_id
    , 1 as played
    , IF(ht_goals < at_goals, 1,0) as win
    , IF(ht_goals > at_goals, 1,0) as lose
    , IF(ht_goals = at_goals, 1,0) as tie
    , CASE WHEN ht_goals < at_goals THEN 3 WHEN ht_goals > at_goals THEN 0 ELSE 1 END as pts
    , at_goals as goalsfor
    , ht_goals as goalsagainst
    FROM results g INNER JOIN fixtures ON g.fixture_id = fixtures.fixture_id 

) as x ON t.team_id = x.team_id
GROUP BY Team
ORDER BY Pts DESC

[lukep11a]

Thank you Barand that's great, you are an absolute star. Do you know how I can get the standings table to only show results where fixtures.comp_id = 1, it's because all my teams are in one table and I want to split them up by division, so I just want to sum up the totals for fixtures that were in a certain competition if that makes sense?? Really sorry about this, I should of thought about it before in my original question.

[Barand]

add the "WHERE fixtures.comp_id = 1" to each of the two selects in the UNION (the home results and away results queries)

[lukep11a]

Nice one, thank you so much for all your help

[Barand]

You might want to add the "goal diff" calc into each of those two selects. SUM() it in the main query (at the top) and then you can order the result by points and goal diff.

[lukep11a]

Thanks Barand, done that now and it all works great, cheers for you help