PHP MySQL - Getting the index of a specific row

Question

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!

Just so you know, you have a potential security hole right there. SQL injection can be nasty. You could brute force search through the rows, but I suspect somebody will know a cool SQL way of doing it. — thatidiotguy, Aug 29 '12 at 20:44
Yes.. insert obligatory "use mysqli or PDO" commentary here. Also insert obligatory "risk of SQL injection" comment here. — Mike Brant, Aug 29 '12 at 20:45
@MikeBrant: Don't forget the mandatory XKCD link: [http://xkcd.com/327/](http://xkcd.com/327/). — Joe Stefanelli, Aug 29 '12 at 20:50
Despite the fact that PDO has nothing to do with this, the fetchall function from PDO would actually, in a sense, index them for you. It's an array of arrays so $row[0] would be #1, and so forth and so on. — Will, Aug 29 '12 at 20:54
Do you want the rank of the highest score of a specific player, or the ranks of all the high scores of a specific player? — Arjan, Aug 29 '12 at 21:03

score 3 · Answer 1 · edited Apr 18 '17 at 01:22

3

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;

edited Apr 18 '17 at 01:22

vishva8kumara

353
1
3
15

answered Aug 29 '12 at 20:53

Peter

5,556
3
23
38

This was very useful to add a long awaited feature to a forum application. I wanted to find the page number on which a given comment is on. I nested this query inside another query to get the exact single number like this: SELECT `index` FROM (SELECT @rownum:=@rownum+1 `index`, c.* FROM content c, (SELECT @rownum:=0) r WHERE pid = 6 #[Post ID] ORDER BY id) AS tmp WHERE id = 32 #[Comment ID] – vishva8kumara Apr 25 '17 at 21:08

Sean · Answer 2 · 2012-08-29T22:08:28.040

1

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.

edited Aug 29 '12 at 22:08

answered Aug 29 '12 at 21:14

Sean

12,443
3
29
47

I tried that, but I got "Resource id #9"... What am I doing wrong? – user1634334 Aug 29 '12 at 21:30
Sorry my error. When doing a `mysql_query` in php it puts the result as a resource, so you have to use `mysql_fetch_assoc` to put the result in an array to use it. I have edited my answer. You can learn more about this at - [stackoverflow.com/questions/5523840/what-is-the-notice-resource-id9](http://stackoverflow.com/questions/5523840/what-is-the-notice-resource-id9) or [stackoverflow.com/questions/4831364/what-are-resources?rq=1](http://stackoverflow.com/questions/4831364/what-are-resources?rq=1) – Sean Aug 29 '12 at 22:12

score 0 · Answer 3 · answered Aug 29 '12 at 20:50

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.

PHP MySQL - Getting the index of a specific row

3 Answers3