mysql ranking for a single user

Question

Im using a suggestion from Daniel Vassallo here to rank entries in my mysql table.

The suggestion doesn't deal with ties and thats the way I want it as a newer entry does not get a higher rank than an older entry with the same score on my scoreboard that way and it works for my needs.

My problem is that I want to be able to use this type of ranking to get the ranking for a single user. So from the output of this query I would like to define a name so that the script returns the rank, name and score of only that user.

I have tried a lot of different methods and as some of them deal with ties the results for a single user end up different from what is displayed in the results of the code below.

Your help would be greatly appreciated....going grey over this!

this is my current code:

it currently outputs:

rank name score

1. me 1111
2. me 1111

3. you 1110

   <?php
   include("common.php");
       $link=dbConnect();
   
   $limit = safe($_POST['limit']);
   
   $query = "SELECT name, score, @curRank := @curRank + 1 AS rank
   FROM $dbName . `scores`, (
   SELECT @curRank := 0
   ) q
   
   ORDER BY score DESC LIMIT $limit";
   
   $result = mysql_query($query);    
   $my_err = mysql_error();
   
   if($result === false || $my_err != '')
   {
       echo "";
   }
   
   $num_results = mysql_num_rows($result);
   
   for($i = 0; $i < $num_results; $i++)
   {
        $row = mysql_fetch_array($result);
        echo $row[rank] . " ". $row['name'] . " - " . $row['score'] . "\n";
   }
   ?>
   

   UPDATE

To clarify on ties; the original script will always increment regardless of ties this is how I want it to be because I don't want it so ties are ranked the same (no joint places) and it just so happens the script will favour the first person to achieve the score so that a new player can't knock him/her off the top spot with the same score, they have to beat it.

I know this is deprecated as I have seen in allot of similar posts but I'm just trying to get the skeleton built before I add the meat to the bones.

As kindly suggested by Spencer7593 I have tried the following code without much luck so far.

<?php
include("common.php");
$link=dbConnect();

$limit = safe($_POST['limit']);


$query = "SELECT name, score, @curRank := @curRank + 1 AS rank
FROM $dbName . `scores`, (
SELECT @curRank := 0
) q
ORDER BY score DESC LIMIT $limit";

$result = mysql_query($query);    
$my_err = mysql_error();

if($result === false || $my_err != '')
 {
     echo "";
 }

$num_results = mysql_num_rows($result);

while ($row = $result->fetch_assoc()) {

 if ( $row['rank'] == 'you' ) 
 {
    // output this row because it's for the specified user
    echo $row['name'];
 } 

 else 

 {
    continue;
 }
 }
 ?>  

Answer 1

To get rankings for a single user extracted from the query results, you could run through the results in PHP, just like you are doing, but "skip" the output of rows that aren't for the specified user.

There's no need for a for ($i=0;i< loop. Use a "while fetch" loop. (I'm loathe to give you any example code using the deprecated mysql interface; new development should use either mysqli or PDO.)

  while ($row = $result->fetch_assoc()) {
     if ( $row['name'] == 'you' ) {
        // output this row because it's for the specified user
        echo $row['rank'];
     } else {
        // skip this row
     }
  }

---

You make some noise about handling "ties", but what's not clear what you actually want as output. If you want rows that have the same value for "score" have the same value for rank, just handle that in your query. If the score on the current row matches the score from the previous row, don't increment the rank. e.g.

  SELECT @curRank := IF(s.score=@prev,@curRank,@curRank + 1) AS rank
       , s.name
       , @prev := s.score AS score
    FROM $dbName . `scores` s
   CROSS
    JOIN (SELECT @curRank := 0, @prev := NULL) q
   ORDER BY s.score DESC
   LIMIT $limit

Including potentially unsafe values into the SQL text leads to SQL Injection vulnerabilities; we're going to assume that you've guaranteed the values of $dbName and $limit are safe.

---

If you want the query to filter out rows for a particular name, then wrap that query in parens and reference it as an inline view, e.g.

SELECT v.rank
     , v.name
     , v.score
  FROM ( SELECT @curRank := IF(s.score=@prev,@curRank,@curRank + 1) AS rank
              , s.name
              , @prev := s.score AS score
           FROM $dbName . `scores` s
          CROSS
           JOIN (SELECT @curRank := 0, @prev := NULL) q
          ORDER BY s.score DESC
          LIMIT $limit
       ) v
 WHERE v.name = 'you'
 ORDER BY v.rank ASC