Database Football Result and Fixture tables

I am relatively new to using MySQL and PHP and have a question that I hope someone may be able to help me with. Unfortunately I feel the question is quite involved and may be difficult and so not sure if anybody can help. If you can it would be really appreciated. I am using Dreamweaver to create this.

The question comprises of four parts (all related) and so to make it easier to explain I will break it down to these. First I will give you an overview of what I want to achieve before detailing it in the four parts.

Overview:

I want to create a football fixtures calendar/list by date and a results table taken from data inserted by the administrator via two back stage forms. One form will be for inserting new teams at the start of the season and the contact details for that team, the other form is used to input the results of each match so that they automatically update the results table.

Part 1: The ‘NEW TEAM’ form

I know how to setup a database table and insert data to it from a form and so I think I can do what is needed for Part 1, but I wanted to include it because the other parts may have an impact on the required fields in the team table??

Part 2: The ‘Fixtures Table’

The fixtures table needs to achieve the following tasks:

It needs to automatically layout a fixtures list where every team inserted under the form in Part 1 plays each other twice, Home and Away. Every team needs to play weekly and so obviously half will have to play home (left of the fixtures table) while the other half play away (right side of the fixtures table) and then vice-versa for the rematches. Unfortunately there may be occasions where a match needs to be postponed because two teams may play in a cup match instead and so the table needs to be able allow manual administrator updates as well and the reschedule of the postponed game later on in the table. There are other fields that also need to be easily adjusted including the pitch, time, and date. Here is an example of what I am looking to achieve:

Week 20 28/04/08
PITCH 3 07:30 Stiff 7 v 2 Riffo Raffo
PITCH 3 08:00 Star Rangers 1 v 0 Truck FC
PITCH 3 08:30 FC 1 v 0 AFC
PITCH 3 09:00 Pizza 1 v 0 Uni
PITCH 3 09:30 MK977 0 v 4 WMC

The scores would all be 0 before the game is played (not as above). This then leads to the next bit, Part 3, the update page of the Fixtures table.

Part 3: ‘Updating Fixtures Table’

The idea here is to create an update page identical to the fixtures table in part 2 but allowing the administrator to enter the scores next to the team names (as on the table above). On update the Part 2 fixtures table would be updated to show the new scores for people viewing that page.

Part 4: The ‘results table’

The aim here is to supply the team’s positions within the league on a table. For each team the following fields would be needed and updated automatically based on the information in the fixtures table:

- Team Name
- No Games Played
- No of Wins
- No of Draws
- No of Losses
- A Sum of goals FOR a team (i.e. the amount of goals scored by the team)
- A Sum of goals AGAINST a team (i.e. the amount of goals conceded)
- Finally a sum of the team’s points calculated on 3pts for every win, 1pt for a draw, and then 0pts when the team loses.

As the scores adjust the teams need to change position within the table and so ordered by the final field ‘points’ but where teams are on equal points the ordering is then decided by goal difference, i.e. the one with the most goals FOR would be higher in the table and where goals FOR both teams are also equal you can put the team with least goals AGAINST them as the higher position in the table.

I think that covers everything needed and although I can see how some of this would work I could really do with some help in explaining the right way to do it.

I hope someone can help. Thanks.

Terry

Just start that's what I'd suggest. Build the tables and start building logic.