how to get the position of sorted rows using mysql and php

I have a table which stores high-scores, along with player ids. I want to be able to extract a record by a players id, and then get the rank, or position of their score in the table. Mea开发者_运维百科ns, Basically I want to be able to say "you are in Nth" position, purely based on the players score against all other scores. For Example: if i am at 46th position then to me the position message will be like you are at 46th position out of total scores. Can anyone show me small example?

There are two ways of doing it:

Method 1:

SET @i = 0;

SELECT * FROM 
scores s1 INNER JOIN (SELECT *, @i := @i + 1 AS rank FROM scores ORDER BY score DESC) AS s2 USING (id);

Method 2:

SELECT *, (SELECT COUNT(1) AS num FROM scores WHERE scores.score > s1.score) + 1 AS rank FROM scores AS s1
ORDER BY rank asc

This will provide duplicate rank values when there are duplicates:

SELECT t.playerid,
       t.highscore,
       (SELECT COUNT(*)
          FROM TABLE x 
         WHERE x.playerid = t.playerid
           AND x.highscore >= t.highscore) AS rank
  FROM TABLE t
 WHERE t.playerid = ?

IE: If three players have the same score for second place, they'll all have a rank value of two.

This will give a distinct value - three players tied for second place, only one will be ranked as 2nd:

SELECT x.playerid,
       x.highscore,
       x.rank
  FROM (SELECT t.playerid,
               t.highscore,
               @rownum := @rownum + 1 AS rank
          FROM TABLE t
          JOIN (SELECT @rownum := 0) r
      ORDER BY t.highscore DESC) x
 WHERE x.playerid = ?

Here is an example.

You want to store the user's ID when they log in, like so...

 $_SESSION['username'] = $usernamefromdb;
 $_SESSION['id'] = $userid;

And then you want to open a session on every page on yoru website that you will be pulling dynamic information depending on the $_SESSION['id']

 session_start();

Then find the row of data in the datebase according to the userID

 $userid = $_SESSION['id'];
 $rank_query = "SELECT * FROM table_name WHERE id='$userid'";
 $rank_result = mysqli_query($cxn, $rank_query) or die("Couldn't execute query.");
 $row = mysqli_fetch_assoc($rank_result)

Then using PHP, declare the nth postiion as a variable. And pull the total amount of rows from the DB

$rank = $row['rank'];
$all = $numrows = mysqli_num_rows($result);

echo out the players rank.

echo $rank . "out of" . $all;