Know the position in a listing

Question

Know the position in a listing

Navigation

#1 by (3 votes)

0

I'm using this query to know how many comments a user has made:

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

However, I would like to know what his position in the overall ranking was, I ran a test and was able 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 this position individually?

php pdo query

asked by anonymous 28.05.2017 / 21:46

1 answer

Fibonacci question in Java Android MVP Framework

score 3 · Accepted Answer

If you are using PostgreSQL, for example, you do not need this function on php and you can write a 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 the php code itself. With the difference of bringing all authors with the amounts of comments in a 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>