calculate league table

[eluser]mirag3[/eluser]
Hi, i need to generate a league table for 1 season with these columns: position, points, name, wins, wins_ot, loses, loses_ot, goals scored, goals conceded. Rows are sorted by: 1. points 2. goals scored . In this league cannot be draw, overtime is needed. My tables looks like this:

#Matches
- match_id
- hometeam_id
- awayteam_id
- location
- season_id

#Seasons
- season_id
- name
- key

#Results
- result_id
- match_id
- hometeam_id
- awayteam_id
- hometeam_goals
- awayteam_goals
- overtime


I dont know how will look select and view. Can anybody help me please? Thanks much. I can pay some small amount on paypal/skrill after good advices/help.

[eluser]InsiteFX[/eluser]
See the codeigniter users guide database Active Record

[eluser]Flemming[/eluser]
First of all, I think you have too many tables. What is the benefit of storing match data and result data in separate tables? Every match will have a result, so you could store hometeam_goals, awayteam_goals and overtime in the matches table.

This will make for simpler queries to create your league table.

I may be wrong though, I haven't spent long thinking about this! :-)

[eluser]mirag3[/eluser]
You are right! Now i have only 2 tables (matches and seasons) but "the query" is too hard for me, icant get it work

[eluser]Flemming[/eluser]
OK, just take your time and build it up slowly. Are you able to use phpMyAdmin?

I can't write the query for you (haven't got time) but just think about how to achieve each part of the result one step at a time.

Example - how do you calculate the points? Is there a formula for this?

[eluser]Tim Brownlaw[/eluser]
@mirag3 - are you still stuck on this...