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

php arrays pdo fetch

30,478

Solution 1

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.

Solution 2

The accepted answer is essentially a cargo cult code, that does its job only by accident, but makes no sense by itself.

PDO::FETCH_GROUP and PDO::FETCH_UNIQUE are mutual exclusive fetch modes, that cannot be used together. Only one of them would work. When you combine them, the latter takes over and \PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE is actually just PDO::FETCH_UNIQUE.

Beside that, the question is ambiguous by itself, the OP wants his array to be indexed by the unique field, whereas he called it grouping which raised a controversy in the answers as well.

So to make it straight:

to index an array with unique values (when you want the resulting array to be indexed by the employee's name, given they are unique), the fetch mode must be PDO::FETCH_UNIQUE:
```
$pdo->query('SELECT name, e.* FROM employee e')->fetchAll(PDO::FETCH_UNIQUE);
```
to group the results (when you want to group employees by department, for example), the fetch mode must be PDO::FETCH_GROUP:
```
$pdo->query('SELECT dept_id, e.* FROM employee e')->fetchAll(PDO::FETCH_GROUP);
```

in both cases the field to be used as the first level array index, must be listed first in the SELECT field list.

A note on the PDO::FETCH_ASSOC. Given that fetch mode for the preferred result format could be set once for all in the constructor, it makes no sense to list it explicitly as well.

Solution 3

to reduce a unnecessary nesting array level:

$res = $pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
$res = array_map('reset', $res);

Solution 4

Key assoc array

PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC

Solution 5

This answer is out of date, please see this other answer instead.

It looks like there's no way to do this as part of fetchAll.

Your best bet is going to be creating a class that extends PDO, adding a utility method to it.

public function queryKeyedAssoc($query, $params, $key) {
    $sth = $this->prepare($query);
    $sth->execute($params);
    $res = array();
    while($row = $sth->fetch(PDO::FETCH_ASSOC))
        $res[ $row[$key] ] = $row;
    return $res;
}

View more solutions

30,478

Author by

HongKilDong

Updated on April 24, 2020

Comments

HongKilDong about 4 years

For example, let's use some simple data set

+---------+------+------+------------+
| name    | age  | sex  | position   |
+---------+------+------+------------+
| Antony  |   34 | M    | programmer |
| Sally   |   30 | F    | manager    |
| Matthew |   28 | M    | designer   |
+---------+------+------+------------+

What we are trying to get is array organized this way

Array
(
  [Antony] => Array
    (
      [age] => 34
      [sex] => M
      [position] => programmer
    )

  [Sally] => Array
    (
      [age] => 30
      [sex] => F
      [position] => manager
    )

  [Matthew] => Array
    (
      [age] => 28
      [sex] => M
      [position] => designer
    )
)

As a rough approximation we can use

$pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

But as result we have unnecessary nesting level

Array
(
    [Antony] => Array
        (
            [0] => Array
                (
                    [age] => 34
                    [sex] => M
                    [position] => programmer
                )

        )

    [Sally] => Array
        (
            [0] => Array
                (
                    [age] => 30
                    [sex] => F
                    [position] => manager
                )

        )

    [Matthew] => Array
        (
            [0] => Array
                (
                    [age] => 28
                    [sex] => M
                    [position] => designer
                )

        )

)

I tried to get rid of this unnecessary nesting level by using callback function

$stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC|PDO::FETCH_FUNC, 'current');

But for some reasons It passes not

Array
  (
   [0] => Array
    (
        [age] => 34
        [sex] => M
        [position] => programmer
    )
  )

but just a bunch of scalars 34, 'M', 'programmer' to callback function :(

You can see it using such function as callback

function what_do_you_pass_me() {

  $numargs = func_num_args();
  $arg_list = func_get_args();
  for ($i = 0; $i < $numargs; $i++) {
    echo "Argument $i is: " . $arg_list[$i] . "\n";
  };
  echo "\n\n";
};

So is there a way to get desired resultset using PDO::FETCH_* modes without using array_map('current', $result) after fetching results ?

Charles almost 11 years

It's not safe to call fetchAll from the result of query like that, as query can return false. This will result in a fatal error as PHP tries to call a method on a non-object.
Gideon about 10 years

What are the slashes ahead of the PDO:: here for?
Jeremy Zerr about 10 years

@Gideon Short answer is to avoid any Namespace conflicts. The \ in front of a constant like that will use the global namespace. In this case where we're not sure where the code will end up, it's the safe thing to write with a \ in front of the constants in case the code is put into a class within a Namespace other than the global one. Common practice on Stack Overflow and other places is to leave the \ out as it depends on the end implementation.
Stephen about 10 years

this should be the correct answer - the argument to fetchAll can also be OR'd with PDO::FETCH_OBJ to get an array of objects
oddtwelve about 10 years

what if I want to group by the second column?
imclickingmaniac about 10 years

If I understand correct just change the fetch order of selected elements (SELECT a,b,c to SELECT c,a,b).
ldg about 10 years

can also be OR'd to use FETCH_ASSOC if you want associative array instead of objects. Either way, seems like a nice solution.
Gannet almost 9 years

What does including FETCH_GROUP achieve? Surely using just FETCH_UNIQUE alone will achieve the desired result - this is the whole point of FETCH_UNIQUE.
Елин Й. over 7 years

What if I want to select all fields using SELECT * FROM and want to group by a specific field which is not the first column. How can I achieve this?
jave.web almost 7 years

Explanation: FETCH_ASSOC gets fields as column_name => value, GROUP takes 1st column and makes it the main key for the result array, however it will still be an array of single-item-arrays (assuming you have only 1 item per main key in the result set) - UNIQUE removes the unwanted level, basically saying "Hey dude, there is only 1 item per main key - so tidy up! :)".
Brad Kent over 6 years

@Gannet old comment, but... FETCH_GROUP is what's indexing the returned results by the name column... that's not the function of FETCH_UNIQUE at all
Gannet over 6 years

@BradKent I suggest you test this out. var_dump((PDO::FETCH_GROUP|PDO::FETCH_UNIQUE) === PDO::FETCH_UNIQUE); // true
Your Common Sense over 6 years

It would work but it contradicts with the wording in the OP as it does not group the results. Besides, PDO::FETCH_ASSOC is superfluous.
Your Common Sense over 6 years

@BradKent really? Surely you have a proof for such a statement?
kojow7 over 6 years

@YourCommonSense I believe that you are incorrect on both counts. 1) PDO::FETCH_UNIQUE does group the results. 2) PDO::FETCH_ASSOC has the benefit of eliminating the numeric indexes so you are only left with the associative ones. Based on the OP this is the exact output that they were looking for.
mpen over 6 years

@YourCommonSense Yep. You're right. I couldn't find the right combination of PDO fetch flags that would do this when I first wrote this.
Your Common Sense over 6 years

I believe you don't quite understand what does "group" mean.
Your Common Sense about 4 years

Your example is a bit confusing, as it shows only one group and looks like the result of just PDO::FETCH_COLUMN alone. I would advise to add another group just to show how it works
Beau about 4 years

I appreciate your feedback, @YourCommonSense. I've updated the answer with more illustrative code examples.