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

Question

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

Navigation

#1 by (10 votes)
#2 by (5 votes)

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 all?

<?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>

php mysql

asked by anonymous 30.04.2014 / 18:24

2 answers

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

How to add static methods in interface? How does GroupBy work in LINQ?

score 10 · Accepted 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 mysql to mysqli_ to binding . Click here to understand better.