By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
463,190 Members | 653 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 463,190 IT Pros & Developers. It's quick & easy.

creating a league table from a database

P: 40
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
Jan 30 '08 #1
Share this Question
Share on Google+
2 Replies

harshmaul
Expert 100+
P: 490
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,

Expand|Select|Wrap|Line Numbers
  1. --tblTeams
  2. TeamID
  3. 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,

Expand|Select|Wrap|Line Numbers
  1. --tblPoints
  2. PointID
  3. TeamID
  4. 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....

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPoints.TeamID as TeamIDA, SUM(Points) FROM tblPoints
  2. inner join tblTeams on tblPoints.TeamID = tblTeams.TeamIS
  3. GROUP BY TeamIDA
This should sort you out
Jan 30 '08 #2

P: 40
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
Jan 30 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.