Creating Rankings

[visionaire]

Hello all,

 

I am working on a webpage for a series of pool tournaments, and want to be able to show the ranking of players for every separate tournament, ie a list of 1st, 2nd, 3rd place and so on. The problem is the tour has been going for 20 years with 8 tournaments a year, so there have been an enormous amount of players and tournaments over the years. I will need a separate table with the players names, details etc, I could maybe use this in the rankings as well. A few things to keep note of:

 

- Not every player has played in every tournament

- Every tournament has a number of ranking points and an amount of prize money linked to the place in which a player finished (2000 euro, 500 points for number 1, 1000, 300 points for number 2 etc.) These have changed over the years, so need to be specified for each tournament.

 

I am breaking my head over this, and don't even know where to start! Any help would be greatly appreciated, I'm hoping this would be fun for someone to figure out with me.

 

Many thanks in advance,

Ron

[requinix]

Table of player information: id, name, age, and whatever

Table of tournaments: id, date, sponsor, and whatever

Table of tournament placements: id (optional), player id, tournament id, ranking, points awarded, money awarded, and whatever

 

And a table of tournament participants if you want it. I'd just as soon keep it separate from the placements table as I would relate the two - your choice.

[visionaire]

Would this not create a very slow system if each table has literally 3000-4000 lines in it?

[Sock Puppet]

Databases are made to deal with huge amounts of data. If you create indexes on the columns you'll be using as selection or join criteria, it should be fine for you.

[visionaire]

Thank you both for the replies!

 

@Sock Puppet:

 

Thanks for your help, what exactly do you mean by creating indexes on the columns? This is new to me

[visionaire]

I Have managed the above, many thanks!

 

I am now running into another problem though. I have the following tables:

 

1. et_rank

    PlayerID - TournamentID - Ranking

 

2. et_tournaments

    Name - Location - Date

 

3. et_matches

    Player1ID - Player1Score - Player2ID - Player2Score - Round

 

The way I want this to display data is as follows:

 

Each player has a stats page, on these stats pages should be an overview of each tournament played, and all his matches in this tournament, layed out as follows:

 

<Tournament 1 Name>

<Match 1 Result>

<Match 2 Result>

 

<Tournament 2 Name>

<Match 3 Result>

<Match 4 Result>

<Match 5 Result>

 

The problem Im running into is I can't get it to only display the tournament name once, then show all matches with that tournament ID and player ID, then show the next tournament name once followed by all its matches. I keep getting outputs like:

 

<Tournament 1 Name>

<Tournament 2 Name>

<Match 1 Result>

<Match 2 Result>

<Match 3 Result>

<Match 4 Result>

<Match 5 Result>

 

I have been staring at this for over 3 hours now, and can't figure it out! Any help would be greatly appreciated!

[lastkarrde]

We can't help if you don't post any code.

[visionaire]

Of course, my apologies My latest attempt is below:

 

$query5=mysql_query("SELECT DISTINCT TournamentID FROM et_matches WHERE Player1='$ID' OR Player2='$ID'");

				for(mysql_fetch_array($query5); !mysql_fetch_array($query5);  ){

				$query6=mysql_query("SELECT * FROM et_tourn WHERE ID='$TournamentID'");
				$row6=mysql_fetch_array($query6);
					echo $row6['Name']."<br />";

					echo "<table width='500' cellpadding=0 cellspacing='2' align='center' border=1>";

					$query7=mysql_query("SELECT * FROM et_matches WHERE TournamentID='$TournamentID' AND (Player1='$ID' OR Player2='$ID')");
					while($row7=mysql_fetch_array($query7)){
					echo "<tr><td>".
					$row7['Player1Score']." ".$row7['Player2Score']
					."</td></tr>";
					}
				}

 

I have a feeling that using 'for' is the wrong approach though.