How to Fetch Associative Array Grouped by the Values of a Specified Column with Pdo

Is there a way to fetch associative array grouped by the values of a specified column with PDO?

It's quite old topic, but I found very easy solution:

->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE)

First col will be set as key, rest will be set as value.

No need to walk over the array or use array_map.

Group multiples rows of result set data by a column value

Let's pretty up your scripting with some best practices...

• only add columns to your SELECT clause when you have a use for them
• enjoy PDO's very handy fetching modes -- FETCH_GROUP is perfect for your case.
• always endeavor to minimize trips to the database
• always endeavor to minimize the number of loops that you use.

Recommended code (yes, it is just that simple):

$sql = "SELECT email, todoID, mitarbeiterFID
        FROM todo 
        JOIN mitarbeiter ON mitarbeiterID = mitarbeiterFID
        WHERE archiv = 0
          AND status != 3
          AND durchfuehrung < CURRENT_DATE";
foreach ($db->query($sql)->fetchAll(PDO::FETCH_GROUP) as $email => $rows) { 
    sendSummary($email, $rows, $company, $db);
}

For the record, I don't know where $company comes from.

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

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];
}

PHP - PDO fetch resultset with column as index and column as value

I don't think there's anything built-in that will do that. You can do it by using a normal fetch() loop:

$results = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $results[$row['date']] = $row['price'];
}

PDO set default fetch mode to FETCH_UNIQUE

$PDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_UNIQUE);

This does not work because, PDO::FETCH_UNIQUE is not a valid constant for PDO::ATTR_DEFAULT_FETCH_MODE. See here the valid fetch mode constants http://www.php.net/manual/en/pdostatement.fetch.php

It seems that there is no way to enable PDO:FETCH_UNIQUE using $PDO->setAttribute(). You should pass it explicitly in the call to fetchAll().

Using value of a column as index in results using PDO

Fetch as assoc

For the manual:
http://php.net/manual/en/pdostatement.fetchall.php

fetch_style

Controls the contents of the returned array as documented in PDOStatement::fetch(). Defaults to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH)

To return an array consisting of all values of a single column from the result set, specify PDO::FETCH_COLUMN. You can specify which column you want with the column-index parameter.

To fetch only the unique values of a single column from the result set, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_UNIQUE.

To return an associative array grouped by the values of a specified column, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_GROUP.

That last bit is key. It doesn't seem to be completely documented (that I could find), but instead of PDO::FETCH_COLUMN, you can combine PDO::FETCH_ASSOC with PDO::FETCH_GROUP to achieve the desired result:

$PDOstmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP)

So, given the above data:

$stmt = $PDO_obj->prepare('select * from brands');
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
d($result);

Results in:

array (6) [
    '1' => array (1) [
        array (2) [
            'name' => string (10) "Solidfloor"
            'url' => string (10) "solidfloor"
        ]
    ]
    '2' => array (1) [
        array (2) [
            'name' => string (9) "Quickstep"
            'url' => string (9) "quickstep"
        ]
    ]
    '4' => array (1) [
        array (2) [
            'name' => string (10) "Cleanfloor"
            'url' => string (10) "cleanfloor"
        ]
    ]
    '5' => array (1) [
        array (2) [
            'name' => string (12) "Blue Dolphin"
            'url' => string (12) "blue-dolphin"
        ]
    ]
    '6' => array (1) [
        array (2) [
            'name' => string (5) "Krono"
            'url' => string (5) "krono"
        ]
    ]
    '8' => array (1) [
        array (2) [
            'name' => string (7) "Meister"
            'url' => string (7) "meister"
        ]
    ]
]

( d() is just a handy debugging function from the kint library, like var_dump() or print_r() )

Note that the column used to index the array will always be the first column in the results, so you can modify your select statement to choose which column you want. And note also that the indexed column will be stripped out of each row's array; to get around that, you can add the column twice to your select statement (i.e., select id, brands.* from brands, etc.).

There are more parameters documented here: http://php.net/manual/en/pdostatement.fetch.php , like PDO::FETCH_UNIQUE to make sure that each index is used only once.

PDO get result index by column name

There's no way to do it with any PDO flags.

Here' my suggested converting script, it's very small:

$result = [];
foreach($array as $arr) foreach($arr as $k=>$v) $result[$k][] = $v;

print_r($result);

---

Related Topics