Auto numbered league table

[Mundane]

Hi all, this is my first post on this site.  I've built a few amateur sites in the past but am keen to utilize php combined with mysql for my current project.  However, I have little knowledge of php and am just picking it up as I go (same way I've done for all my computer knowledge)

 

What I'm looking for may sound simple, but I really don't know where to start and I'm hoping there are some kind souls here that will lend a hand.

 

SQL Database

Name Score

John 42

Peter 46

Michael 56

Angela 46

(many rows like this)

 

I am looking to output on my page a table of the top ten scores like this.  However it's not quite that simple, should there be a tie on any score I want it to appear like so:

1. Michael 56

2. Angela 46

    Peter 46

4. John 42

(onwards to 10)

 

I want this to run from positions 1 thru 10, but should the 10th score in the database be equal to the 11th (12th, 13th etc) I would like them displayed too.  For example:

9. Rafael 31

10. Sandy 29

      Bernard 29

      Jackie 29

 

 

Is this possible or would I be better off just sorting the info in excel and hard coding the info everytime?

 

 

Hope somebody can help.

 

Thanks in advance.

[BlueSkyIS]

select name, score from the score table ordered by score descending. loop over the records, keeping track of the score. if the score changes, you have reached the next level

 

1. Michael 56

2. Angela 46

    Peter 46

4. John 42

[Mundane]

select name, score from the score table ordered by score descending. loop over the records, keeping track of the score. if the score changes, you have reached the next level

 

1. Michael 56

2. Angela 46

    Peter 46

4. John 42

 

Thanks for your speedy reply BlueSkyIS, what you said does sound like exactly what I'm after.  As I said I really am pretty new to using php and I'm not quite sure how to go about what you've said.  Can you point me to a tutorial/sample code (or if it's only a couple of lines could you type a sample code for me?).

 

Hopefully I'm not asking for too much, thanks I appreciate your time and help.

[simshaun]

This is untested, and there may be a more elegant way of doing it that doesn't involve selecting every score from the database (I just can't think of it at this moment.)

<?php
$result = mysql_query("
SELECT
	name
	,score
FROM
	scores
ORDER BY
	score DESC
") or trigger_error('Query failed in '. __FILE__ .' on line '. __LINE__ .'. '. mysql_error(), E_USER_ERROR);
if (mysql_num_rows($result)) {
$prevScore = NULL;
$numScores = mysql_num_rows($result);

// Loop through the SQL records.
for ($x = 0; $x < $numScores; $x++) {

	// Get the record in an array form ($row['name'], $row['score']).
	$row = mysql_fetch_assoc($result);

	// If we've reached 10 iterations, see if the next record has the same score.
	// If yes, we'll keep going. If not, we'll kill the loop so we don't have to go through
	// every single record returned by the SQL query.
	if ($x >= 9 && $x+1 < $numScores) {
		// Peek at the next record.
		$nextRow = mysql_fetch_assoc($result);
		if ($nextRow['score'] == $row['score']) {
			// Set the MySQL pointer back a step or the next iteration will skip the $nextRow we just looked at.
			mysql_data_seek($result, $x+1);
		}
		else {
			break;
		}
	}

	// Only display the number if this score is different than the previous score (or is the first record).
	if ($row['score'] != $prevScore) {
		echo ($x+1).'. ';
	}

	echo htmlentities($row['name'], ENT_COMPAT, 'UTF-8') .' '. htmlentities($row['score'], ENT_COMPAT, 'UTF-8') .'<br>';

	// Set the previous score = to this score so the next iteration can compare its score with this one's.
	$prevScore = $row['score'];
}
}

I've tried to comment it so that you understand what's going on.

[Mundane]

Wow, thank you so much simshaun, this code worked a dream, exactly what I was after.

 

I appreciate you giving up your time to put this together for me