PDOStatement:在fetchAll($ mode);和setFetchMode($ mode)之间得到不同的结果. fetchAll();` [英] PDOStatement: Getting different results between `fetchAll($mode);` and `setFetchMode($mode); fetchAll();`

问题描述

我对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源代码中确认了这一点，请参见

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()内部有效).

因此我们知道至少该常数仅对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.

例如， fetchAll() 描述，但未出现在fetch()描述中.

因此，我认为与多行操作相关的常量(例如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.

这篇关于PDOStatement:在fetchAll($ mode);和setFetchMode($ mode)之间得到不同的结果. fetchAll();`的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！