I have a table that has a status column, the status is a number between 1-8.
I can use the query
SELECT status, COUNT(id) AS total
FROM cart_orders
GROUP BY status;
To get a result that looks like:
status | total
1 | 10
2 | 15
3 | 8
4 | 51
5 | 65
...
But that means every time I need the data I have to do:
$status = array(1 => null, 2 => null, 3 => null, 4 => null,
5 => null, 6 => null, 7 => null, 8 => null);
foreach($rows as $row){
$status[$row['status']] = $row['total'];
}
Which, while not a lot of code I would like to avoid having to do that,
What I want is to modify the query so I end up with a single row that looks like:
1 | 2 | 3 | 4 | 5
10 | 15 | 8 | 51 | 65 ...
so I can just go e.g. $row['4']
The only what I can think of to do this would be to have each column as a subquery, which I would rather use the code than have 8 sub-queries. The other option is to join the table on itself 7 times, but that is assumedly also not ideal?
How else could I do this?