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

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
                )

        )

    [Matt开发者_StackOverflowhew] => 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 ?

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.

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. If you combine them, the latter takes over and \PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE is actually just PDO::FETCH_UNIQUE.

Besides, the question is ambiguous by itself: the OP wants the resulting array to be indexed by the unique field, whereas they called it grouping, which raised a controversy in the answers as well.

So to make it straight:

• to index the resulting array with unique values (when you want it 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 preferred fetch mode is best to be set once for all in the PDO constructor, most of time it can be omitted here.

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);

Key assoc array

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

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

It doesn't look like anyone has mentioned this variation, so, for the benefit of future Googlers:

Combine the \PDO::FETCH_GROUP and \PDO::FETCH_COLUMN flags. This vastly simplified my SQL statement and returned the exact result set I wanted. It's fast, too.

$this->database->query('SELECT t.fk, t.id FROM my_table t ORDER BY t.fk ASC, t.id ASC')
  ->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_COLUMN);

Where t.fk has a one-to-many relationship with t.id.

I didn't have to concern myself with a GROUP BY statement or MySQL's finicky handling of grouping on multiple fields. Best of all, I received results in the form of:

[
  foreign_key_1 => [
    0 => 11111,
    1 => 22222,
    2 => 33333,
  ],
  foreign_key_2 => [
    0 => 44444,
    1 => 55555,
    2 => 66666,
  ],
  foreign_key_3 => [
    0 => 77777,
    1 => 88888,
    2 => 99999,
  ],
];

Rather than:

[      
  foreign_key_1 => [
    0 => [
      id => 11111,
    ],
    1 => [
      id => 22222,
    ],
    2 => [
      id => 33333,
    ],
  ],
  foreign_key_2 => [
    0 => [
      id => 44444,
    ],
    1 => [
      id => 55555,
    ],
    2 => [
      id => 66666,
    ],
  ],
  foreign_key_3 => [
    0 => [
      id => 77777,
    ],
    1 => [
      id => 88888,
    ],
    2 => [
      id => 99999,
    ],
  ],

];

Hope it helps someone out there!

For reference: https://phpdelusions.net/pdo/fetch_modes

We can make Charles' solution a little nicer by extending the statement class instead:

class MyPdo extends PDO {
    function __construct($host, $database_name, $username, $password, $options=array()) {
        $options = self::merge(array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_STATEMENT_CLASS => array('PdoPlusStatement', array()),
            PDO::ATTR_EMULATE_PREPARES => true,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        ), $options);
        $dsn = "mysql:host=$host;dbname=$database_name;charset=utf8";
        parent::__construct($dsn, $username, $password, $options);
    }
}

class PdoPlusStatement extends PDOStatement {
    protected function __construct() {}

    /**
     * @param array|mixed $input_parameters An array of values with as many elements as there are bound parameters in the SQL statement being executed, or one or more non-array arguments to be matched with sequential parameter markers.
     * @throws PDOException
     * @return PdoPlusStatement
     */
    public function execute($input_parameters=null) {
        $args = func_get_args();
        $argc = func_num_args();
        if($argc===0) {
            parent::execute();
        } else {
            if($argc===1 && is_array($args[0])) {
                $args = $args[0];
            }
            parent::execute($args);
        }
        return $this;
    }

    /**
     * Returns an array containing all of the remaining rows in the result set
     * @return array An associative array using the first column as the key, and the remainder as associative values
     */
    public function fetchKeyAssoc() {
        return array_map('reset', $this->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC));
    }
}

Usage:

$users = $pcs->query("SELECT name, user_id, discipline_id FROM wx_user")->fetchKeyAssoc();

Not sure why no one has posted the following solution, but it works perfectly for me:

PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC

So, changing your statement to:

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

should be exactly what you want.