How to take the position of an individual user in a rank, if in the database I only have your score?

Asked

Viewed 2,031 times

8

With the code below I can display all users as I put DESC and the variable $num and increasing the placement with $num++;.

But what if I want to take the placement/rank of the guy who’s 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>
php mysql

  • The best way was to really have a spine with the ranking of each.

  • What are the columns of your rank table?

  • I know, however as users are using the application the rank is being changed, so I cannot determine a position for them.

  • The columns are: NAME to identify, EMAIL to know who is logged in and SCORE where the number can both increase and decrease. I also created the COLOCACAO field, but I don’t know how to put data in it according to the score.

  • 1

    ORDER BY pontuacao DESC? Ai just do a binary search on the ordered vector. The position in the vector will be the position in the ranking.

2 answers

10


The query:

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

Using in the 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:

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

  2. The query SELECT COUNT(*) FROM rank WHERE pontuacao > ( ... ) account how many people have a score equal to or higher than that of the logged in user. If you prefer, you can change the > for >=, and the COUNT(*)+1 for COUNT(*). That depends on your tie-breaking criteria.

  3. If you want draws to be of the same rank, an exit is to change the COUNT(*) + 1 for COUNT(DISTINCT Pontuacao) + 1 in this same query, keeping the rest equal.
    (This item 3 was inspired by the idea of @ramaral)

Tip: change the mysql for mysqli_ with Binding. Click here to better understand.

  • It worked perfectly. Thank you very much, not only did it solve my problem but it gave me a light on how to use COUNT. As for sql Injection, I’ll get the session email, so validation is something that’s already back there. And yes, I’ve read about mysqli and as soon as I’m structured things will replace the "mysql". Thanks again.

5

A solution making 2 query s and a subtraction.

First calculate the number of Separate 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 obtain the ranking.

Note that by using DISTINCT I guarantee that users with the same score have the same ranking

  • I understood the logic. But how do I subtract? I made the first query with $xery and the second query with $query, it looks like $colocacao= ($xery - $query); ?

  • 1

    Use Bacco’s answer, because it is the right one. My answer is an example of how, sometimes, we can complicate what is simple. As it is easy to understand, subtracting from the total amount of Scores the amount of Scores lower than that of the user, is the same as counting the total of Scores higher than that of the user, which is what Bacco is proposing.

  • I understood the logic of both and thank you, because you helped me understand COUNT with two different examples. I was able to show on the screen the result.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.