March 1st, 2006, 04:51 PM
Football League Table
I am currently running a website for my local football team and I have to update the tables by hand at the moment which is abit like banging your head against a brick wall.
I have someknowledge in ASP but most of all PHP and I was wondering if it would be possible so that when I enetered the scores into the database the league table would automatically update with the information from the scores?
If anyone could support me with this as it is a must for the new website I would be very greatful, infact over the moon, because it seems quite a challenge to me.
My thanks in advance,
March 1st, 2006, 05:34 PM
Of course it's possible... There are actually a few questions about this already on devshed (I remember reading a few in the mysql section, with regards to table schemas and what not, matches played, etc). If you have mysql, that's one place to start with regards to a search for more information.
I'd post up a suggested table schema (that is if you're using mysql/mssql/postgres/etc) and a few tips for starters if I knew a bit more as to what you have access to... And if you specified what "football" you're referring to.
I'd have a rough idea of where to start if you're referring to "soccer" football, but you don't specify either your location or what type of football you're referring to (I wouldn't know where to start with Australian Rules or American football, since I don't have the first clue how those league tables work).
March 2nd, 2006, 02:59 AM
Firstly thanks for your reply.
I have access to mysql and I am referring to English (soccer) football. There are 22 teams in the league. I am looking to construct something like this:
Each team in the fixtures page is assigned an ID so you can click on a team, say Manchester United vs Arsenal, you could click on Arsenal and it would filter all the matches for Arsenal, etc. The table however is the most important part, when scores are added the table needs to update due to the changes made to the scores.
I have spent a couple of hours with pen and paper trying to firgure it out but it is quite challenging.
March 2nd, 2006, 05:34 AM
not sure im following you correctly but...
If each team has an indivdual ID then in your scores table you also need to have two extra fields such as 'team1id' and 'team2id' that would contain the Id's of the teams that played.
if you want to auto update the table then upon entering the score you would need to get the ID of team 1 (and read their current points) and the ID of team 2 (and read current points) then update the current points depending upon who won (which youd calculate by comparing the two entereed scores). If team 1 == win then add 3 (3 points?) to current score
March 2nd, 2006, 08:30 AM
I'll have this moved over to mysql forum. If you do a search on soccer or football (I can't remember which) it has been discussed before.
basically you only want a table holding scores and not tabulate the points in the table, you do those calculations on the fly and present those to the user.
Actually I dug out a thread here , take a look at how the table is laid out and then look at post #6 by yours truly to see how you can calculate the points on the fly. Make sure you read through the whole thread as there are some other calculations that are done as well.
Of course if you want a goals table for your footballers that would be something separate to keep track of individual goals etc. but it can be done.
Last edited by Guelphdad; March 2nd, 2006 at 08:37 AM.
March 2nd, 2006, 11:16 AM
I am a little confused to what you are saying I mean like this:-
Say I have 5 teams in my league:
4 Manchester United
Each team would be assigned an ID, like above.
When entering fixtures there would be a drop down box to select the home team, and another to select away team and then the boxes for the home score and away score are both left blank untill the game is played.
When the game has been played the score would be entered, lets say, 1-2.
Now the page which displays the fixtures should look like this:
HomeTeam vs AwayTeam 1-2
The reason I have assigned an ID to each team is because then you can hyperlink each team name in the fixtures page so that when the link is clicked all the games played by that team are displayed (filtered to be shown).
With the score entered into the database the table can now use it to position the teams on the league table.
My question isn't really "I need help with a certain aspect of it" but more I need help and tips to build a system which works like this.
March 2nd, 2006, 06:00 PM
I was thinking and wouldn't it have to be broken into 3 tables?
FixturesTable - FixtureID, League, Day, Month, Year, HomeTeam, HomeScore, AwayTeam, AwayScore, Details
TeamsTable - TeamID, TeamName, League
LeaguesTable - LeagueID, LeagueName
Forgive me if Im wrong but wouldnt this be the basic structure of it? And then each table draws information from the other one? So for instance when you add a fixture it draws all the team names from the TeamsTable and inserts them into a drop down box so you can select who will play who? And so on with the league?
I am having quite a difficult time getting my head around this so help would be appreciated.
March 2nd, 2006, 06:22 PM
Yes.. I think that is a good structure. I didn't look at the link Guelph posted, but, yes, that structure looks fine to me. Day/Month/Year should be all one field, but that's my only criticism.
This seems very sound.. what is it you're stuck on ?
March 2nd, 2006, 06:57 PM
use a field of datetime type for your date. much easier to sort and you can use DATE_FORMAT to show your dates any number of ways.
March 3rd, 2006, 02:27 AM
I am stuck on actually getting it all together. I can use them table structures but how can I make the league table from this?
I can make the pages which add and update the "FixturesTable" but am unsure of how to make the table display:
Pld Won Lost Drew etc...
And how would I get it so when you are adding a fixture it draws teams from the "TeamsTable" when you are working with the "FixturesTable" ?
My main problem is working with multiple databases in one page.
March 3rd, 2006, 02:57 AM
you will only need one database - but you will need several tables. you can then use joins to link your tables together.
ive recently built a football league site and i had difficulty with writing a script to calcualte league standings - you may find this thread useful.
March 3rd, 2006, 07:28 AM
Well that's why I bothered to add the link above in my first post, it talks about doing exactly that from the stats you have outlined for your main table. take the time to read through the entire thread.
Originally Posted by d4v1s
March 3rd, 2006, 09:13 AM
I have a lovely Excel spreadsheet from the last world cup which I've updated for this coming one. It has all the teams & groups and later rounds. You just enter the scores and it automatically calculates group positions and copies winning team names on into the next round.
It isn't done with VB but all with formulae. If this is usefull to you I could email it. It may give you some ideas for calculating goal difference and placings etc.
March 3rd, 2006, 06:04 PM
Grr, Im gettin really frustrated about this. Physically it looks easy, but logically I just cant think it out.
March 3rd, 2006, 06:12 PM
Well.. if you can't ask specific questions, we can't help you. If you're stuck on the querying part, head to the mysql forums. If you'rs stuck on the logic part or the php coding part, ask something specific instead of telling us you're frustrated. This task isn't all that difficult (unless I'm missing something) and I really don't know where you're getting tripped up because you already have a good database structure. Links have been given that sound like they may help you. Tell us where / how you're getting frustruated.
I didn't realize this had been moved. So.. change my post's text accordingly
Comments on this post
Last edited by angrypanda; March 3rd, 2006 at 06:18 PM.