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

Question

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 ?

Answer 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.

Answer 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.