TOP 5 PLAYERS

Question

I’m trying problems in my code, I wanted him to get the 5 best players who most purchased products in the month, the problem is that I wanted him to catch the guy’s user and show the amount he paid, if he had more purchases, he would just add up the amount.

I tried to :

    public function topShopping()
    {
        $stmt = $this->pdo->prepare("SELECT * FROM `website_transactions` ORDER BY `transaction_AMOUNT` DESC LIMIT 5");
        $stmt->execute();
        if($stmt->rowCount() == 0) { return "<div class=\"col-md-12\">
            <div class=\"compra\" onclick=\"\">
            <img class=\"imgboder\" src=\"https://minotar.net/avatar/steve/60\">
            <p><b>NENHUMA COMPRA REGISTRADA</b></p>
        </div>
            </div>"; }
        $fetch = $stmt->fetchAll(\PDO::FETCH_OBJ);
        $return = "";
        foreach ($fetch as $key => $rs)
        {

            $posicao = $key + 1;
            $name = $rs->transaction_USERNAME;
            $valor = $rs->transaction_AMOUNT;
            $date = \DateTime::createFromFormat('Y-m-d', $rs->transaction_DATE)->format('Y/m/d');
            $return .= "<b>
            <tr class=\"tr\" style=\"border-radius: 10px;white-space: nowrap;\">
                <td class=\"th\">$posicao</td>
                <td class=\"th\"><img style=\"vertical-align: sub;width:20px!important;height:20px!important;border-radius: 3px;\" src=\"https://minotar.net/avatar/$name/100\"> $name <i class=\"fas fa-crown\"></i></td>
                <td class=\"th\">R$ $valor</td>
            </tr></b>";

    }
        return "{$return}";
    }  

but the result is

wanted to know if I can put to add the value after several purchases with the same user

my mysql:

Answer 1

To do this you need to sum the transaction_amount of the period you want, group the results by user and sort by descending order of value. Here is an example of how to do this, the query sums the purchased value and also counts the total user transactions:

select transaction_USERNAME, sum(transaction_AMOUNT) as total_amount, count(transaction_CODE) as 
total_transactions from website_transactions
WHERE MONTH(transaction_DATE) = 8 AND YEAR(transaction_DATE) = 2019
GROUP BY transaction_USERNAME
ORDER BY sum(transaction_amount) DESC LIMIT 5