Creating a multidimensional array from query with PDO

Question

I am wondering if this is possible.

I have a table with three columns (there's an id column too, but irrelevant here):

[year] [month] [price]

How do I generate an array that consolidates year and month?

Result would be like this:

[
2011 => [Jan => price, Feb => price, etc.]
2012 [Jan => price, Feb => price, etc.]
]

Any way to this directly with PDO, instead of looping and creating it with PHP?

I've tried the FETCH GROUP fetch mode, but haven't been successful.

Answer 1

You will have to loop over the structure, the only methods to retrieve data from the PDO results are the following

• PDOStatement::fetch
• PDOStatement::fetchAll
• PDOStatement::fetchColumn
• PDOStatement::fetchObject

None of these return in multi-dimensional arrays.

IMO here is the simplest way to acheive this:

$db = new PDO(...);

$query = $db->query("
    SELECT 
        year, month, SUM(price) AS total_price
    FROM 
        table
    GROUP BY 
        year, month
");

$query->setFetchMode(PDO::FETCH_ASSOC);

$results = array();
while ($row = $query->fetch()) {
    list($year, $month, $totalPrice) = $row;

    if (!isset($results[$year])) {
        $results[$year] = array();
    } //if

    $results[$year][$month] = $totalPrice;

} //while

unset($query);
unset($db);

Answer 2

The best I could come up with is

http://www.sqlfiddle.com/#!2/a87785/10/0

SELECT a.year, 
   Group_concat(a.arr) 
FROM   (SELECT year, 
           (SELECT Concat(sub.month, ' ', Group_concat(price)) 
            FROM   test sub 
            WHERE  sub.year = t.year 
                   AND sub.month = t.month 
            GROUP  BY month) AS arr 
    FROM   test t 
    GROUP  BY year, 
              month) a 
GROUP  BY a.year; 

but this implies to explode afterwards. I am pretty sure multidimensional arrays aren't supported by the current implementations of PDO