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

8

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?

<?php

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> ";

$num=1;

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

$num++;
}

echo "</table>"; ?> </div>
    
asked by anonymous 30.04.2014 / 18:24

2 answers

10

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);
...

Explanation:

  • 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.

        
    30.04.2014 / 19:39
    5

    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

        
    30.04.2014 / 19:26