creating a league table from a database

[tommurray]

I’m looking for a little help and guidance on creating a league table from entries in a database, because of other requirements it is not possible to update the entry for the person in the database. So there is going to be multiple rows for the same person I would then like to add up the points for that person. Around ten people will be apart of the league and the total number of rows will be no more than one hundred so I don’t think there will be to much overhead on the database plus the site is never going to be main stream just the 10 members will be visiting.

I was thinking about doing a select statement and then doing a while loop going through all the records, then a for statement for each row that equals the name adding the points together.

Is it a case of writing out the results such as:

[PHP]$name1 = Tom
$name2 = Ste [/PHP]

Then doing something like:

[PHP]$i = 1
foreach ($row[‘name’] = $name{$i}; $i++)
{
‘add up points here
}[/PHP]

Thank you for your help

Tom

[harshmaul]

Hi,
Firstyl you will need to consider a different table structure. Are you a football (soccer) person? Thats the example i'm going to try and use to help me explain.

You have ONE table (tblTeams), and that table contains a list of all of the teams that are going to play with a structure like this,

--tblTeams
TeamID
TeamName

everytime a team wins they get three points so you will need to count this up, so you have another table (tblPoints) with a structure like this,

--tblPoints
PointID
TeamID
Points

So now each time a team wins, insert a new record into tblPoints with the TeamID of the team and the number of points the team was awarded.

Now when you want to display the league table you would run a query somthing like this....

SELECT tblPoints.TeamID as TeamIDA, SUM(Points) FROM tblPoints
inner join tblTeams on tblPoints.TeamID = tblTeams.TeamIS
GROUP BY TeamIDA

This should sort you out

[tommurray]

Thank you for your help, I was hoping to see a method that would be able use the data that is currently stored in the table. Reducing the need to add further SQL scripts to the original part of my code, so I think the easiest approach would be to create another table and update the points.

But thank you for the SQL script it will come in handy and I was not aware that approach.

Thank you

Tom