mysql best way to count orders ordered by status

For my panel I update my “status-buttons” with count-numbers.

F.e. New 2, backorder 5, cancelled 3, shipped 201

In the back, I now count them like this:

while($status = mysqli_fetch_array($query)){
   $countSQL = $mysqli->query("SELECT id FROM orders WHERE status=".$status['id']." AND user=".$user);
   $countStatus = mysqli_num_rows($countSQL);
   // etc...
}

I know… “Use prepared statements”… I will, in the updated version of my script 🙂

But is there an easier way to get all the orders from that specific user and then just receive the amount per “status”?

I already tested the below, but in the average, the difference is 0.000024 vs 0.000034. So it is faster, but I think (and hope) there is a better way?

SELECT 
        SUM( CASE WHEN status = '1' THEN 1 ELSE 0 END ) AS one, 
        SUM( CASE WHEN status = '2' THEN 1 ELSE 0 END ) AS two, 
        SUM( CASE WHEN status = '3' THEN 1 ELSE 0 END ) AS three, 
        SUM( CASE WHEN status = '4' THEN 1 ELSE 0 END ) AS four, 
        SUM( CASE WHEN status = '5' THEN 1 ELSE 0 END ) AS five, 
        SUM( CASE WHEN status = '6' THEN 1 ELSE 0 END ) AS six, 
        SUM( CASE WHEN status = '7' THEN 1 ELSE 0 END ) AS seven, 
        SUM( CASE WHEN status = '8' THEN 1 ELSE 0 END ) AS eight, 
        SUM( CASE WHEN status = '9' THEN 1 ELSE 0 END ) AS nine, 
        SUM( CASE WHEN status = '10' THEN 1 ELSE 0 END ) AS ten, 
        SUM( CASE WHEN status = '11' THEN 1 ELSE 0 END ) AS eleven 
FROM orders 
WHERE user=$user

Also, the upper way is not that usefull when there would be new statusses available (12,13,…).

Answer

You can use group by when status value is dynamic

SELECT id, status, count(id) as total FROM orders WHERE user='userid' GROUP by status