CODEWITHSUNDEEP
phpmysqlindexingrow
user1634334
user1634334

Reputation: 31

PHP MySQL - Getting the index of a specific row

I am using a MySQL database to record player highscores on a game I am creating. The MySQL database is being accessed through a PHP file "highscores.php".

The highscores are all recorded inside the database, but I want the actual rank numbers to be implemented by the PHP file. I want to be able to query for the ranking of a specific player, instead of only querying for his/her highscore.

I am using this to get the highscore of my player:

$username = $_GET["username"];

$checkID = mysql_query("SELECT * from Highscores WHERE Username =
'$username' ORDER BY Score DESC");

$row = mysql_fetch_array($checkID);

echo $row["Score"];

Now to get the ranking of my player amongst all the other players, all I need to do is find the index of the row in question... But how do I do that?

Thanks in advance!

Upvotes: 3

Views: 5352

Answers (3)

Peter
Peter

Reputation: 5884

You could do it with SQL directly like this:

SELECT @rownum:=@rownum+1 ‘rank’, h.*
FROM Highscores h, (SELECT @rownum:=0) r
ORDER BY score DESC;

Upvotes: 3

Sean
Sean

Reputation: 12433

You could do - 

// gets all the records with higher scores than current user
$ranking = mysql_query("SELECT count(*) AS ranking FROM Highscores WHERE Score > (SELECT Score FROM Highscores WHERE Username = '$username' ORDER BY Score DESC LIMIT 1)");
// creates an array from the mysql query above
$ranking_array = mysql_fetch_assoc($ranking); 
// gets the number of higher scores from the array
$ranked_above = $ranking_array['ranking']; 
// takes the total number ranked above and adds a 1 to get their rank
$current_rank = $ranked_above++;

Please be aware of the comments above about SQL Injection, and no longer using mysql_ functions.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71414

Based on your DB design, I don't know that you have much option other than to select the entire score table from the DB sorted by score, loop through it assigning score index value to each item, but only pulling out those particular to your user for display.

It sounds like you might want to consider running some process to regularly rank the scores in the database, or maybe create a stored procedure to select all the socres, give them rank values, and the select the user-related ones from them.

Upvotes: 0

Related Questions