Getting a users rank from a mysql table with PDO

[mediabob]

Hi, I am trying to get the rank of a user in a database, based on his score, so when the user logs in he can see his rank. I am using this to build a list of all users ordered by their score for a different section.

$stmt = $dbh->query("SELECT * FROM $table ORDER BY score DESC"); 
 
    $rank = 0;
        while($uid = $stmt->fetchObject()) {
          $rank++;
          echo $uid->uname ."is ranked". $rank;
        };

How can I get ONLY a users rank in the db based on his ID number, if I have a table like, .

 

| user | ID | score |

Jim  | 2568 | 80

Pete | 5693 | 115

Sam | 3258 | 569

George | 7412 | 30

 

the ID numbers are a unique key

 

If Jim logs in today he will see:

 

Your Current Rank is 3

 

I am using PHP with PDO on a MySQL table. Thanks!

[requinix]

Get the user's current score and count how many people have a score above that.

[fastsol]

$id = (int)$_GET['id']; // This is jsut an example using a GET var from the url.


$query = $dbh->query("SELECT `user` FROM `$table` WHERE `ID` = $id");


$result = $query->fetch(PDO::FETCH_ASSOC);

[mediabob]

Get the user's current score and count how many people have a score above that.

 

So using your idea, I have done this, and it works exactly for what I want, is this the best recommended way?

 

 

$stmt = $dbh->query("SELECT COUNT(uid) AS `rank` FROM $table WHERE score > ( SELECT score from $table WHERE uid = $uid )");
$result = $stmt->fetchColumn();
 
echo $result+1;
 

[requinix]

Pretty much, yeah. There are finer details to consider though, like how you treat ties...

[mediabob]

That's a good point I didn't consider, thanks for getting me going in the right direction! 

[Barand]

And don't forget to add 1 to the count to get the rank (if count is 0, rank is 1)

[jazzman1]

Or....try to use ALL SQL operator instead to add +1 and everytime to assign new $uid.

 

No....it's the same, b/s we have single output.

SELECT COUNT(*) AS `rank` FROM $table WHERE $table.score >= ALL ( SELECT $table.score FROM $table WHERE $table.id=$userID)

[Barand]

 

Fred    99
Joe     90
Mary    80
Jane    80
Paul    80
Peter   80
Jazzman 80

Jazzman,

if you use >= then your rank is now 7 instead of 3=

[jazzman1]

Fred    99
Joe     90
Mary    80
Jane    80
Paul    80
Peter   80
Jazzman 80
Barand  70

Jazzman,

if you use >= then your rank is now 7 instead of 3=

 

 

Yes, master, got it....but what about the user with lowest rank named "Barand"

 

If I run next query, "Barand" is getting rang 8 instead 4.

SELECT COUNT(*)+1 as 'rank'  FROM mydb.users WHERE score > (
SELECT score FROM mydb.users WHERE users.UserID=

[Barand]

My rank there is 8, there are 7 with a higher score. You only have 2 with a higher score