PDOStatement: Getting different results between `fetchAll($mode);` and `setFetchMode($mode); fetchAll();`

Question

I have a question regarding PDO.

Is there a difference between

$sql = "SELECT * FROM pages";
$pdo = $this->db->query($sql);
$result = $pdo->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC); 

and

$sql = "SELECT * FROM pages";
$pdo = $this->db->query($sql);
$pdo->setFetchMode(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
$result = $pdo->fetchAll();

?

I get a different $result for each of them.

The documentation from PHP about these fetch modes is not very clear to me.

I have a table with different pages, and I want to fetch all the pages indexed by their ID.

The first method returns this:

[
   [id of page 1] => [
      [0] => [ page 1 ],
   ],
   [id of page 2] => [
      [0] => [ page 2 ],
   ],
   ...
]

When I do the second method, I only get:

[
   [0] => [ page 1 ],
   [1] => [ page 2 ],
   [3] => [ page 3 ],
   ...
]

I want it like this:

[
   [id of page 1] => [ page 1 ],
   [id of page 2] => [ page 2 ],
   [id of page 3] => [ page 3 ],
   ...
]

The first one is good enough for me because I can easily tweak it with the array map function:

array_map('reset', $result);

Answer 1

The explanation seems to be that fetchAll() and setFetchMode() support different sets of PDO::FETCH__ constants.

I think setFetchMode() ignores PDO::FETCH_GROUP, but not PDO::FETCH_ASSOC.

I confirmed this from the PHP source code, see the setFetchMode() implementation. The list of constants supported by setFetchMode does not include PDO::FETCH_GROUP.

---

Why the results of setFetchMode() and fetchAll() are different

 The code:

<?php

$db = new PDO('mysql:host=localhost;dbname=test', 'root');

// print the default output, no constants
$sql = "SELECT * FROM service_table";
$q = $db->query($sql);
$result = $q->fetchAll();
echo 'Result 0' . PHP_EOL;
var_dump($result);

// use fetchAll() with constants
$sql = "SELECT * FROM service_table";
$q = $db->query($sql);
$result = $q->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
echo 'Result 1' . PHP_EOL;
var_dump($result);

// use setFetchMode with constants
$sql = "SELECT * FROM service_table";
$q = $db->query($sql);
$q->setFetchMode(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
$result = $q->fetchAll();
echo 'Result 2' . PHP_EOL;
var_dump($result);

 Test data

mysql> select * from test.service_table;
+----+------+
| id | name |
+----+------+
|  1 | one  |
|  2 | two  |
+----+------+

Result 0

This is the default, results are not grouped and there are both name-based and index-based fields:

array(2) {
  [0] =>
  array(4) {
    'id' => string(1) "1"
    [0] => string(1) "1"
    'name' => string(3) "one"
    [1] => string(3) "one"
  }
  [1] =>
  array(4) {
    'id' => string(1) "2"
    [0] => string(1) "2"
    'name' => string(3) "two"
    [1] => string(3) "two"
  }
}

Result 1

This is the result of $q->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC). We have the effects of both FETCH_GROUP and FETCH_ASSOC:

array(2) {
  [1] => array(1) {
    [0] => array(1) {
      'name' => string(3) "one"
    }
  }
  [2] => array(1) {
    [0] => array(1) {
      'name' => string(3) "two"
    }
  }
}

Result 2

This is the result of $q->setFetchMode(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);, we have only the effect of FETCH_ASSOC:

array(2) {
  [0] => array(2) {
    'id' => string(1) "1"
    'name' => string(3) "one"
  }
  [1] => array(2) {
    'id' => string(1) "2"
    'name' => string(3) "two"
  }
}

This way, FETCH_GROUP works for fetchAll(), but doesn't work for setFetchMode(). FETCH_ASSOC works in both cases.

PHP Documentation

Now, an indirect confirmation from docs:

Here is the list of all PDO constants. The description for PDO::FETCH_FUNC says:

Allows completely customize the way data is treated on the fly (only valid inside PDOStatement::fetchAll()).

So we know that at least this constant only works for fetchAll(), and can assume that other constants may work not everywhere.

Also if we look at the docs for fetch(), we see a limited list of constants there.

For example PDO::FETCH_GROUP and PDO::FETCH_UNIQUE are present in the fetchAll() description, but are not present in the fetch() description.

So I think constants related to multi-row operations, such as PDO::FETCH_GROUP, are only used for fetchAll() and ignored by fetch() and setFetchMode().

---

How to fetch an array indexed by ID

I tried few combinations and it looks like FETCH_GROUP + FETCH_UNIQUE does it.

 Code

$sql = "SELECT * FROM service_table";
$q = $db->query($sql);
$result = $q->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);
var_dump($result);

Result

array(2) {
  [1] => array(1) {
    'name' => string(3) "one"
  }
  [2] => array(1) {
    'name' => string(3) "two"
  }
}

Another option can be to use FETCH_FUNC with custom function to format data, see the fetchAll() docs.