Know the position on a listing

Question

I’m wearing this query to know a user’s feedback quantity:

$comentarios = $pdo->query("SELECT * FROM topicos_comentarios WHERE autor='".$ver['autor']."'")->rowCount();

However, I would like to know his position in the overall ranking, I did a test and managed to list all users and their positions with this code:

<?php
include 'assets/php/config.php';
$ranking = $pdo->query("SELECT * FROM topicos_comentarios GROUP BY autor ORDER BY count(autor) DESC");
$i = 1;
  while($ver = $ranking->fetch(PDO::FETCH_ASSOC)){
    $comentarios = $pdo->query("SELECT * FROM topicos_comentarios WHERE autor='".$ver['autor']."'")->rowCount();
?>
<?php echo $i; ?>°- <?php echo $ver['autor'];?> - <?php echo $comentarios; ?></br>
<?php ++$i; } ?>

But how could I get that position individually?

Answer 1

If you are using Postgresql, for example, this function on php is not necessary and you can write an SQL that returns only the author you want, with the ranking and number of comments. With this SQL:

SELECT ROW_NUMBER(), g.autor, g.n FROM (SELECT count(tc.autor) as n, tc.autor FROM topicos_comentarios tc GROUP BY autor ORDER BY n DESC) g WHERE autor='nome do autor'

As I do not know which backend you are using, I wrote an example considering only ANSI SQL. Where you will have to look for the author in php code. With the difference of bringing all authors with the amounts of comments in one query only, with fetchAll().

<?php
include 'assets/php/config.php';

function autorRank($autor, $allRes) {
    foreach ($allRes as $rank => $umRes)
        if ($umRes["autor"] == $autor) return array ($rank + 1, $umRes["n"]);
}



$ranking = $pdo->query("SELECT g.autor, g.n FROM (SELECT count(tc.autor) as n, tc.autor FROM topicos_comentarios tc GROUP BY autor ORDER BY n DESC) g");

$all = $ranking->fetchAll(PDO::FETCH_ASSOC);

$ret = autorRank("nome do autor", $all);
$i = $ret[0];
$comentarios = $ret[1];

?>
<?php echo $i; ?>°- <?php echo "nome do autor";?> - <?php echo $comentarios; ?></br>