League Table

[redsent]

I am looking to create a league table from a mysql table.I have the data set out in a table called tabres as thus:hteam hscore ateam ascoreteam1 34 team2 28team3 12 team4 24team5 6 team6 18etcI need a league table to show teamname, played, won, lost drawn, points diff, total points and a special column called bonus points. The bonus point would be awarded only if the losing team is within 12 points of the winning team.3 points for a win, two for a draw and 1 for a bonus point,any help would be appreciated.Cheers.

[justsomeguy]

You'll either need a SQL stored procedure or some PHP code to get all of records in the table and loop through them to build the statistics, then insert all of the data into the new table.

[redsent]

I was thinking of using a view created from a sql query to create the table and then using php code bring the table into my web page, i've got this code so far:

SELECT tname, Sum(P),Sum(W),Sum(D),Sum(L),SUM(F),SUM(A),SUM(PD),SUM(Pts) FROM(SELECT hteam Team, 1 P,  IF (hscore > ascore,1,0) W,  IF (hscore = ascore,1,0) D,  IF (hscore < ascore,1,0) L,  hscore F,  ascore A,  hscore-ascore PD,  CASE WHEN hscore > ascore THEN 3 WHEN hscore = ascore THEN 1 WHEN hscore <= ascore-12 THEN 2 ELSE 0 END PTS  FROM tabres  UNION ALL  SELECT ateam,  1,  IF (hscore < ascore,1,0),  IF (hscore = ascore,1,0),  IF (hscore > ascore,1,0),  ascore,  hscore,  ascore-hscore PD,  CASE WHEN hscore < ascore THEN 3 WHEN hscore = ascore THEN 1 WHEN hscore-12 <= ascore THEN 2 ELSE 0 END  FROM tabres) as tot, teams WHERE tot.Team=teams.teamid GROUP BY Team ORDER BY SUM(Pts) DESC

but this needs two tables and i can't quite get it to work properly.Ideally it would com from the results table.