I'm creating a fixtures/league table system for a football team site. I want it to be as dynamic as possible so the plan is to have a list of fixtures which are set at the start of the season. As the games are played the results are updated in the fixtures table which looks like this:
At the moment this information only used to create a fixture/result list but I would also like to use the information stored here to calculate the league standings when called upon, any ideas? (I also have season, league, team tables)
At the moment i have a back-up plan where when the fixtures are updated the function also updates an actual league table stored on the database. I'm sure this will work fine but it seems stupid to me to store preety much the same information twice when it's possible to calculate it from one table. What do you think? I can't quite get my head around how to do it, as it's not so much of a problem getting the data from the database using 'SELECT WHERE & AS etc' to make the calculations in PHP, but then you've got to sort it into a table and you won't be able to use 'ORDER BY'
Ok, do you have any code with which you are starting from? Are you looking to create a table that can be sortable by clicking on the column headers? Are you looking to output statistics from your database? What does your database enviroment look like currently? Is it all in one table, multiple tables, etc...
I haven't started coding yet, just thinking around the idea. Just to clarify what i want to do I have a table with the league fixtures in, with a typical entry having a team_id for each team in the fixture, and the goals they scored. From this you can work out whether it was a home win, draw or away win. You then need to use this information to work out the league table which will be sorted by points (3 for a win, 1 for a draw and 0 for a lose) and the Goal Difference ( scored goals - goals conceded). To work this all out surely you'd have to start with one team, process all of it's matchs and find out it's league stats, then do the same for the next and so on. Once you've done this for all teams you would then have to determine the order and create a table to that effect. This would have to be done in PHP but i'm not quite sure how, possibly count the teams in the league, create varables to hold the information (team number ones would look something like this: Pld1, W1, D1, L1, GF1, GA1, GD1, Pts1 - then process each fixture relevent to the team adding to the relevent variables. Team 2 would have Pld2, W2, D2, L2, GF2, GA2, GD2, Pts2.....and so on) then once completed for all teams determine the order in which to display them in a table...
At the moment I'm leaning towards my other idea of having a league_stats table which would hold the calculatons and is updated everytime results are entered, this way a SQL SELECT could sort and retreive the information. The only problem with this method is that you only have an up-to-date version of the league table and cannot look into the pass (i.e. specify league table up until a certain date). Do you see what i mean??
I'm just thinking out the idea at the moment before diving straight in and any thoughts would, as always, be great.
You could keep your stats table as a history if you wanted. Just add a field that holds the date. And, then when you do you calculations for league_stats only recall the most recent historical item. This way you do not lose any historical information if you wanted it.
Had a request to post the answer here, so here it is. This is a stripped down version of my solution, and uses the theory of 1 league point for each point scored in a match. e.g. if the result is 4-3, the home team gets 4 points onto their "For" total.