How do I get the position of a user individually in a rank, if in the database I only have your score?

Posted on

Question :

With the code below I can display all users, because I put DESC and variable $num and it increments the placement with $num++; .

But what if I want to get the / rank from the guy who is logged in ($_SESSION) and not everyone’s?


include "../banco_de_dados/banco_cadastro.php"; 

$sql= mysql_query("SELECT * FROM rank ORDER BY pontuacao DESC");

echo "<table>";
echo"<th > Colocação  </th> <th> Nome </th>  <th > Pontuação </th> ";


while ($row = mysql_fetch_array($sql)) 
echo "<tr>";
echo"<td >" . $num . "º</td>"; 
echo "<td  > " .$row['nome'] . "</td>"; 
echo "<td > " .$row['pontuacao']. "</td>";


echo "</table>"; ?> </div>


Answer :

A query:

SELECT COUNT(*)+1 FROM rank WHERE pontuacao>(SELECT pontuacao FROM rank WHERE email = '$email' )

Using PHP :

$email = (EMAIL DO USUARIO LOGADO); // Sanitize para evitar SQL Injection.
$query = "SELECT COUNT(*) + 1 AS colocacao FROM rank
          WHERE pontuacao > ( SELECT pontuacao FROM rank WHERE email = '$email' ) ";
sql = mysql_query($query);


  • The subquery (SELECT pontuacao FROM rank WHERE email='$email') returns the points of the logged-in user. Tailor the comparison to your specific case.

  • Query SELECT COUNT(*) FROM rank WHERE pontuacao > ( ... ) counts how many people have a score equal to or greater than the logged-in user. If you prefer, you can change > by >= , and COUNT(*)+1 by COUNT(*) . It will depend on your tiebreaker criteria.

  • If you want ties to be of the same rank, one way is to change COUNT(*) + 1 by COUNT(DISTINCT Pontuacao) + 1 in this same query, keeping the rest equal.
    (This item 3 was inspired by @ramaral’s idea)


    Tip: Change mysql to mysqli_ to binding . Click here to understand better.


    A solution doing 2 query’s and a subtraction.

    First calculate the number of different scores:

    SELECT COUNT(DISTINCT Pontuacao) FROM rank;  

    Then see how many have less score than the user:

    SELECT Count(DISTINCT Pontuacao) FROM rank
    WHERE Pontuacao < (SELECT Pontuacao FROM rank WHERE email = EmailDoUtilizador);  

    Subtract the second from the first to get the ranking .

    Note that using DISTINCT will ensure that users with the same score have the same ranking


    Leave a Reply

    Your email address will not be published. Required fields are marked *