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
– thatidiotguy, Commented Aug 29, 2012 at 20:44
Yes.. insert obligatory "use mysqli or PDO" commentary here. Also insert obligatory "risk of SQL injection" comment here. — Mike Brant
– Mike Brant, Commented Aug 29, 2012 at 20:45
@MikeBrant: Don't forget the mandatory XKCD link: http://xkcd.com/327/. — Joe Stefanelli
– Joe Stefanelli, Commented Aug 29, 2012 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
– Will, Commented Aug 29, 2012 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
– Arjan, Commented Aug 29, 2012 at 21:03

vishva8kumara · Accepted Answer · 2017-04-18 01:22:59Z

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, 2017 at 1:22

vishva8kumara

3551 gold badge3 silver badges16 bronze badges

answered Aug 29, 2012 at 20:53

Peter

5,9243 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vishva8kumara Over a year ago

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]

Sean · Accepted Answer · 2012-08-29 22:08:28Z

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, 2012 at 22:08

answered Aug 29, 2012 at 21:14

Sean

12.4k3 gold badges31 silver badges49 bronze badges

2 Comments

user1634334 Over a year ago

I tried that, but I got "Resource id #9"... What am I doing wrong?

Sean Over a year ago

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 or stackoverflow.com/questions/4831364/what-are-resources?rq=1

Mike Brant · Accepted Answer · 2012-08-29 20:50:22Z

0

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.

answered Aug 29, 2012 at 20:50

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

Collectives™ on Stack Overflow

PHP MySQL - Getting the index of a specific row

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related