How can I retrieve the TOP 5 in my products based on sales?

Question

How can I retrieve the top 5 products in my table? Based on sales I'm using CodeIgniter I tried method like this one

$conn = new mysqli("localhost", "root","","bubblebee");
if ($conn->connect_errno) { 
    printf("Connect failed: %s\n", $conn->connect_error);
    exit();
} else {
    echo 'Connection success <br>' ;
}

$total = "SELECT product_id, SUM(amount) 
            FROM order_items 
            GROUP BY product_id 
            ORDER BY SUM(Amount) DESC 
            LIMIT 3";
$statement = $conn->query($total);
if (!$statement = $conn->query($total)) {
    echo 'Query failed: ' . $conn->error;
} else {
    foreach($statement as $row){
        echo $row['product_id'] . '<br>';
    }
}

And this one

$select = array('products.name as label', 'sum(order_items.amount) as amount');
$final = $this -> db -> select($select)
       -> from('products') 
       -> join('order_items', 'order_items.product_id = products.id', 'left') 
       -> group_by('products.id') 
       -> order_by('products.id', 'DESC') 
       -> limit(5) 
       -> get() -> result_array();

And I cannot still make it work but I tried my query in my localhost and it's working it's retriving the prod id and sum of the amount I'm using this query

SELECT product_id, SUM(amount) FROM order_items GROUP BY product_id ORDER BY SUM(Amount) DESC LIMIT 5

Answer 1

Try

$this->db->select('product_id, SUM(amount)');
$this->db->from('order_items');
$this->db->group_by('product_id');
$this->db->order_by('SUM(amount)', 'DESC');
$this->db->limit(5);
$query = $this->db->get();

source SELECT product_id, SUM(amount) FROM order_items GROUP BY product_id ORDER BY SUM(Amount) DESC LIMIT 5