query Pdo error (Sort by Count)

Asked

Viewed 58 times

1

I am trying to make a ranking based on the amount of coins each. In descending order. I used this query in Pdo:

$ranking = $pdo->query("SELECT * FROM usuarios WHERE banido='false' GROUP BY usuario ORDER BY count(moedas) DESC LIMIT 3");

Complete code:

<?php
                                $ranking = $pdo->query("SELECT * FROM usuarios WHERE banido='false' GROUP BY usuario ORDER BY count(moedas) DESC LIMIT 3");
                                $i = 1;
                                while($ver = $ranking->fetch(PDO::FETCH_ASSOC)){
                                    if($i == 1){
                                        $class = 'gold';
                                    }
                                    else if($i == 2){
                                        $class = 'silver';
                                    }
                                    else if($i == 3){
                                        $class = 'bronze';
                                    }
                            ?>
                            <div class="registry big <?php echo $class; ?>">
                                <div class="rBox"><i class="fa fa-user-circle" aria-hidden="true"></i>&nbsp;<a href="#"><?php echo $ver['usuario']; ?></a></div>
                                <div class="base">
                                    <div class="avatar" style="background-image: url('https://www.habbo.com.br/habbo-imaging/avatarimage?&user=<?php echo $ver['usuario']; ?>&action=std&direction=3&head_direction=3&img_format=png&gesture=std&headonly=0&size=b')"></div>
                                </div>
                                <div class="comments"><i class="fa fa-tint" aria-hidden="true"></i><br><?php echo $ver['moedas']; ?></div>
                            </div>
                            <?php $i++; } ?>

However, I’m not finding the error. The first place is correct, but in the second place appears a random user with 0 of coins, and the 3rd with their due coins, but was to be in 2nd place.

sql pdo query
  • 2

    Would not be ORDER BY moedas DESC simply?

  • What is your SGDB ?

  • 2

    Explain the table structure better, and why you needed to group the results.

  • Puts, you were right @bfavaretto. Great vacilo dei, confused me. ORDER BY coins DESC Resolved.

1 answer

0


Try adding an alias 

SELECT *, COUNT(moedas) AS money
FROM usuarios
WHERE banido='false' GROUP BY usuario
ORDER BY money DESC LIMIT 3;

Browser other questions tagged sql pdo query

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