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

Answer 3

Key assoc array

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

Answer 4

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

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

Answer 6

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

Answer 7

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

Answer 8

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.