php 有没有办法使用 PDO 获取按指定列的值分组的关联数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5361716/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to fetch associative array grouped by the values of a specified column with PDO?
提问by HongKilDong
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 :(
但只是一堆标量34, 'M', 'programmer'回调函数:(
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 ?
那么有没有办法使用PDO::FETCH_*模式获得所需的结果集而无需array_map('current', $result)在获取结果后使用？
回答by imclickingmaniac
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.
第一个 col 将被设置为 key，rest 将被设置为 value。
No need to walk over the array or use array_map.
无需遍历数组或使用 array_map。
回答by Stefan
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);
回答by Xakki
Key assoc array
键关联数组
PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC
回答by Your Common Sense
The accepted answer is essentially a cargo cult code, that does its job only by accident, but makes no sense by itself.
接受的答案本质上是一个货物崇拜代码，它只是偶然地完成它的工作，但它本身没有任何意义。
PDO::FETCH_GROUPand PDO::FETCH_UNIQUEare mutual exclusive fetch modes, that cannot be used together. Only one of them would work. When you combine them, the latter takes over and \PDO::FETCH_GROUP|\PDO::FETCH_UNIQUEis actually just PDO::FETCH_UNIQUE.
PDO::FETCH_GROUP和PDO::FETCH_UNIQUE是互斥的获取模式，不能一起使用。只有其中一个会起作用。当您将它们组合起来时，后者会接管并且\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE实际上只是PDO::FETCH_UNIQUE.
Beside that, the question is ambiguous by itself, the OP wants his array to be indexedby the unique field, whereas he called it groupingwhich raised a controversy in the answers as well.
除此之外，这个问题本身就是模棱两可的，OP希望他的数组由唯一字段索引，而他称之为分组，这在答案中也引起了争议。
So to make it straight:
所以要直截了当：
to indexan array with unique values (when you want the resulting array 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 groupthe 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);
要索引具有唯一值的数组（当您希望结果数组按员工姓名索引时，因为它们是唯一的），获取模式必须为 PDO::FETCH_UNIQUE：
$pdo->query('SELECT name, e.* FROM employee e')->fetchAll(PDO::FETCH_UNIQUE);要对结果进行分组（例如，当您想按部门对员工进行分组时），获取模式必须为 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.
在这两种情况下，要用作第一级数组索引的字段必须首先列在 SELECT 字段列表中。
A note on the PDO::FETCH_ASSOC. Given that fetch mode for the preferred result format could be set once for all in the constructor, it makes no sense to list it explicitly as well.
关于PDO::FETCH_ASSOC. 鉴于可以在构造函数中一次性设置首选结果格式的获取模式，因此明确列出它也是没有意义的。
回答by Charles
This answer is out of date, please see this other answerinstead.
此答案已过时，请参阅此其他答案。
It looks like there's no way to do this as part of fetchAll.
作为fetchAll.
Your best bet is going to be creating a class that extends PDO, adding a utility method to it.
最好的办法是创建一个扩展 PDO 的类，向它添加一个实用程序方法。
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;
}
回答by mpen
We can make Charles' solution a little nicer by extending the statement class instead:
我们可以通过扩展语句类来使 Charles 的解决方案更好一点：
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();
回答by Beau
It doesn't look like anyone has mentioned this variation, so, for the benefit of future Googlers:
似乎没有人提到过这种变化，因此，为了未来的 Google 员工的利益：
Combine the \PDO::FETCH_GROUPand \PDO::FETCH_COLUMNflags. This vastly simplified my SQL statement and returned the exact result set I wanted. It's fast, too.
结合\PDO::FETCH_GROUP和\PDO::FETCH_COLUMN标志。这极大地简化了我的 SQL 语句并返回了我想要的确切结果集。它也很快。
$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.fkhas a one-to-many relationship with t.id.
wheret.fk与t.id.
I didn't have to concern myself with a GROUP BYstatement or MySQL's finicky handling of grouping on multiple fields. Best of all, I received results in the form of:
我不必担心GROUP BY语句或 MySQL 对多个字段分组的挑剔处理。最重要的是，我收到了以下形式的结果：
[
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
回答by kojow7
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.
应该正是你想要的。
