Jump to content

League Table


redsent
 Share

Recommended Posts

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.

Edited by redsent
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...