将mysql行转换为列

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?

What you are asking to do is called a "pivot query," which MySQL unfortunately does not support natively. What you must do is specify each row individually, as in:

SELECT
   SUM(IF (status = 1, total, 0)) AS '1'
   ...

..but this is very verbose -- especially if you have a lot of columns. I think your PHP is a little more verbose than it needs to be too in that you don't need to declare the array ahead of time.

It's not the query you want to change, but how you display the information you get from it. Try something like this

// insert code to whatever defines your $rows variable

$string1 = "<tr>";
$string2 = "<tr>";
foreach($rows as $row) {
  $string1 .= "<td>" . $row['status'] . "</td>";
  $string2 .= "<td>" . $row['total'] . "</td>";
}
$string1 .= "</tr>
";
$string2 .= "</tr>
";

echo "<table border=\"1\">
";
echo $string1;
echo $string2;
echo "</table>";