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,…).
Advertisement
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