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