PDOStatement:在 `fetchAll($mode);` 和 `setFetchMode($mode); 之间得到不同的结果；获取所有()；`

2022-01-20 00:00:00 arrays fetch php pdo

我有一个关于 PDO 的问题.

I have a question regarding PDO.

有没有区别

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

和

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

?

我为每个人得到一个不同的 $result.

I get a different $result for each of them.

PHP 关于这些获取模式的文档对我来说不是很清楚.

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

我有一个包含不同页面的表，我想获取所有按其 ID 索引的页面.

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

第一个方法返回这个:

[
   [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 ],
   ...
]

我想要这样:

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

推荐答案

解释好像是 fetchAll() 和 setFetchMode() 支持不同的集合 PDO::FETCH__ 常量.

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

我认为 setFetchMode() 会忽略 PDO::FETCH_GROUP，但不会忽略 PDO::FETCH_ASSOC.

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

我从 PHP 源代码中确认了这一点，请参阅 setFetchMode() 实现.setFetchMode 支持的常量列表不包括 PDO::FETCH_GROUP.

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.

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

测试数据

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

结果 0

这是默认设置，结果不分组，并且有基于名称和基于索引的字段:

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

结果 1

这是 $q->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC) 的结果.我们有 FETCH_GROUP 和 FETCH_ASSOC 的效果:

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

结果 2

这是$q->setFetchMode(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);的结果，我们只有FETCH_ASSOC的效果:

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

这样，FETCH_GROUP 适用于 fetchAll()，但不适用于 setFetchMode().FETCH_ASSOC 在这两种情况下都有效.

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

现在，来自文档的间接确认:

Now, an indirect confirmation from docs:

这里是所有PDO 常量的列表.PDO::FETCH_FUNC 的描述说:

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

允许完全自定义动态处理数据的方式(仅在 PDOStatement::fetchAll() 内部有效).

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

所以我们知道至少这个常量只适用于fetchAll()，并且可以假设其他常量可能并不适用于任何地方.

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

另外，如果我们查看 fetch()，我们在那里看到有限的常量列表.

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

例如 PDO::FETCH_GROUP 和 PDO::FETCH_UNIQUE 存在于 fetchAll() 描述，但不存在于 fetch() 描述中.

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

所以我认为与多行操作相关的常量，例如 PDO::FETCH_GROUP，仅用于 fetchAll() 并被 fetch() 和 setFetchMode().

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

我尝试了几个组合，看起来 FETCH_GROUP + FETCH_UNIQUE 可以.

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

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

结果

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

另一种选择是使用带有自定义函数的 FETCH_FUNC 来格式化数据，请参阅 fetchAll() 文档.

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

相关文章