Jump to content

League Standings Table


lukep11a

Recommended Posts

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?

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.