Top users, and displaying the current position in the profile, how to implement it correctly in MYSQL + PHP?

Question

PHP

I welcome everyone! I am a beginner and I need to make a Place in the ranking, I did sorting by points. That is, if the user has the most points, he is displayed above all, but I still need to add a type of place. It is also necessary that I display them in the user profile

<table style="margin-left: auto;margin-right: auto; margin-top: 20px;">
           <tr><th style="color: black; text-align: center;">Игрок</th><th style="color: black; text-align: center;">Команда</th><th style="color: black;">ROT <i style="cursor: pointer;" title="ROT- Raiting One Tour" class="fas fa-question-circle"></i></th></tr>
           <?php 
db();

$rait = mysqli_query($db, "SELECT `login`, `rating`, `team` FROM `users` ORDER BY  `rating` DESC LIMIT  100");

if(mysqli_num_rows($rait)) {

while ($row = mysqli_fetch_assoc($rait))
 echo '<tr><td>'.$row['login'].'</td><td>'.$row['team'].'</td><td>'.$row['raiting'].'</td></tr>';
}
            ?>
           
       </table>

Thanks in advance!

Answer 1

Pure SQL solution - enumerate returned rows via cross join

SELECT 
(@row_number:[email protected]_number + 1) AS pos, 
`login`, `rating`, `team`
FROM `users`, (SELECT @row_number:=0) AS t 
ORDER BY  `rating` DESC 
LIMIT  100

Or display keep a counter when outputting to PHP

<table style="margin-left: auto;margin-right: auto; margin-top: 20px;">
           <tr><th style="color: black; text-align: center;">Игрок</th><th style="color: black; text-align: center;">Команда</th><th style="color: black;">ROT <i style="cursor: pointer;" title="ROT- Raiting One Tour" class="fas fa-question-circle"></i></th></tr>
           <?php 
db();

$rait = mysqli_query($db, "SELECT `login`, `rating`, `team` FROM `users` ORDER BY  `rating` DESC LIMIT  100");

if(mysqli_num_rows($rait)) {

$position = 0;
while ($row = mysqli_fetch_assoc($rait))
 echo '<tr><td>'.(++$position).'. '.$row['login'].'</td><td>'.$row['team'].'</td><td>'.$row['raiting'].'</td></tr>';
}
            ?>
           
       </table>

Ranking position for a profile = count how many records with a rating that is higher than the player's current rating

SELECT (COUNT(*)+1) as rating_pos FROM users WHERE `rating` > 'рейтинг_текущего_профиля'

Answer 2

place_in_rating | player_name | glasses

SELECT место_в_рейтинге, имя_игрока, очки
FROM название_таблицы
ORDER BY место_в_рейтинге DESC
-- вывод рейтинга

SELECT место_в_рейтинге
FROM название_таблицы
WHERE имя_игрока = 'имя_игрока'
-- вывод места в рейтинге