How to dynamically pivot MySQL results

Question

Not sure if the title is appropriate for the question, but I have the following problem.

The following code:

while($row = mysql_fetch_array($result2)) {
  print_r($row);
}

Produces the following result:

Array
(
    [y] => 2016
    [m] => 9
    [status] => 0
    [count] => 2
)
Array
(
    [y] => 2016
    [m] => 9
    [status] => 1
    [count] => 33
)
Array
(
    [y] => 2016
    [m] => 9
    [status] => 2
    [count] => 4
)
Array
(
    [y] => 2016
    [m] => 10
    [status] => 0
    [count] => 20
)
Array
(
    [y] => 2016
    [m] => 10
    [status] => 1
    [count] => 3
)
Array
(
    [y] => 2016
    [m] => 10
    [status] => 2
    [count] => 14
)

I would like some help with a function that would convert these results to the following format:

Array
(
    [y] => 2016
    [m] => 9
    [<name status 0>] => 2
    [<name status 1>] => 33
    [<name status 2>] => 4
)
Array
(
    [y] => 2016
    [m] => 10
    [<name status 0>] => 20
    [<name status 1>] => 3
    [<name status 2>] => 14
)

Edit:

The Mysql query:

 SELECT y, m, count(status) as count, status
 FROM (
  SELECT y, m
  FROM
     (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
     (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
       UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
       UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
   LEFT JOIN db_orders
   ON ym.y = YEAR(db_orders.delivery_date)
      AND ym.m = MONTH(db_orders.delivery_date)
 WHERE
   (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
   OR
   (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
 GROUP BY y, m, status;

Edit 2:

The solution:

editing the select for the mysql query solved my problem:

SELECT y, m, COUNT(IF(status='0',1, NULL)) '<name for status 0>', COUNT(IF(status='1',1, NULL)) '<name for status 1>', COUNT(IF(status='2',1, NULL)) '<name for status 2>'

This leads to the following query:

SELECT y, m, COUNT(IF(status='0',1, NULL)) '<name for status 0>', COUNT(IF(status='1',1, NULL)) '<name for status 1>', COUNT(IF(status='2',1, NULL)) '<name for status 2>'
FROM (
  SELECT y, m
  FROM
     (SELECT YEAR(CURDATE()) y UNION ALL SELECT YEAR(CURDATE())-1) years,
     (SELECT 1 m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
       UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
       UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) months) ym
   LEFT JOIN db_orders
   ON ym.y = YEAR(db_orders.delivery_date)
      AND ym.m = MONTH(db_orders.delivery_date)
 WHERE
   (y=YEAR(CURDATE()) AND m<=MONTH(CURDATE()))
   OR
   (y<YEAR(CURDATE()) AND m>MONTH(CURDATE()))
 GROUP BY y, m, status;

Sounds like you're going to have to write your own print method -- the print_r() isn't meant to be customizable like that. What have you tried so far? — Hayden Schiff, Nov 02 '16 at 18:05
The print_r() function is only used to show the structure of the array i have. I'm already using the results in other graphs i'm using to show the results. I would like to build a stacked graph (using google charts) and i need the results in that way to make that possible. I'm not that familliar with writing functions (other than using while and for loops) .. So i'm hoping for someone who can give me an example function to show what i can do.. Thank you for any help you can give me. — Jeff Van Herck, Nov 02 '16 at 18:13
Your problem is that your query is returning results in a way you dont want it. Make your query return the grouped data, and you'll get your array structured right. Post your sql query and the structure of your tables. — leoap, Nov 02 '16 at 18:21
You probably want to look at your query and `SUM()` and `GROUP BY` and alias the `SUM()`. — AbraCadaver, Nov 02 '16 at 18:26
Please, don't use mysql_* functions. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — Michas, Nov 02 '16 at 23:19

How to dynamically pivot MySQL results

0 Answers0