问题描述
我对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:
因此我们知道至少该常数仅对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();`的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!