PHP PDO Fetch as multidimensional array with first column as key, and second & third as key-value pair

user_13719067 0
Viewed 781

I have an MySQL database table that is created like this

CREATE TABLE table1 (
    column1 varchar(50),
    column2 varchar(50),
    column3 int(3)
);

INSERT INTO table1
VALUES ("column1 value1", "column2 value1", 120),
("column1 value1", "column2 value1", 240),
("column1 value2", "column2 value1", 240),
("column1 value2", "column2 value2", 10);

On this table. I execute this SQL query:

SELECT column1, column2, SUM(column3) FROM table1 GROUP BY column1, column2

Is it possible to fetch the result into a multidimensional associative array with column1 as the "level 1" key, and column2 & column3 as a key-value pair?

Desired result:

Array
(
    [column1 value] => Array
        (
            [column2 value] => column3 value
        )
)

I tried using PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC as the fetch style argument, but that resulted in:

Array
(
    [column1 value] => Array
        (
            [column2 key] => column2 value
            [column3 key] => column3 value
        )
)

This is how I am executing my query and fetching the result:

$stmt = $pdo->prepare("SELECT column1, column2, SUM(column3) FROM table GROUP BY column1, column2");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

Is there a fetch style combination that will get me the desired result, or will I have to do this programmatically?

multidimensional-array pdo mysql arrays php
3 Answers

Basically you're stuck doing it programmatically, but you could do it with a function in the fetchAll call; using FETCH_GROUP to do the grouping by the first column and using the function to return the appropriate array from the second and third columns.

$result = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_FUNC,
                          function ($col2, $col3) { 
                              return array($col2 => $col3); 
                          });

Note this returns an array with an extra level:

Array
(
    [column1 value1] => Array
        (
            [0] => Array
                (
                    [column2 value1] => 360
                )
        )
    [column1 value2] => Array
        (
            [0] => Array
                (
                    [column2 value1] => 240
                )
            [1] => Array
                (
                    [column2 value2] => 10
                )
        )
)

You can remove the extra level through a combination of array_map and array_reduce:

$result = array_map(function ($arr) {
    return array_reduce($arr, function ($c, $a) {
        $c[key($a)] = reset($a);
        return $c;
    }, []);
}, $result);

Output:

Array
(
    [column1 value1] => Array
        (
            [column2 value1] => 360
        )
    [column1 value2] => Array
        (
            [column2 value1] => 240
            [column2 value2] => 10
        )
)

However it is probably simpler just to loop through the array:

$result = $stmt->fetchAll(PDO::FETCH_NUM);
$out = array();
foreach ($result as $array) {
    $out[$array[0]][$array[1]] = $array[2];
}
user_94737640

Apparently, the hardest part in PDO fetch modes is to tell the grouping from indexing. Every question related to these modes for some reason have them confused.

From the nature of your query I would say you need the grouping. But in your hairy fetch mode combination you are using FETCH_UNIQUE, which results in the indexing, not grouping.

It's a good thing you provided some MCVE - only few succeeded with it under the PDO tag, but it's a very bad thing that you didn't provide the real desired result, but only a sketch. So I can only guess it must be something like this

{
    "column1 value1": {
        "column2 value1": "360"
    },
    "column1 value2": {
        "column2 value1": "240",
        "column2 value2": "10"
    }
}

but your current approach is giving you this array one row short.

I don't know any fetch modes combination that can give you the desired result but the old good manual fetching always can

$sql = "SELECT column1, column2, SUM(column3) sum FROM table1 GROUP BY column1, column2";
$stmt = $pdo->query($sql);
$data = [];
foreach($stmt as $row) {
    $data[$row['column1']][$row['column2']] = $row['sum'];
}

In the end, it turned out that your MCVE is not that good. And it will be a lesson for you, to always create a solid, working and non-contradictory example with the certain desired result that can be compared to the actual outcome.

user_2855870

Sadly PDO::FETCH_GROUP will only group results by the first fetched column so you won't be able to group by 2 columns.

But you can translate your result to the result you want using array_map, array_combine and array_column

$stmt = $pdo->prepare("SELECT column1, column2, SUM(column3) as column3 FROM table1 GROUP BY column1, column2");
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

$result = array_map(static function ($res) {
    return array_combine(
        array_column($res, 'column2'),
        array_column($res, 'column3')
    );
}, $result);

var_dump($result);
user_72838480
Related