当前位置： 文江博客话题详情

有没有办法使用 PDO 获取按指定列的值分组的关联数组？

发布于 2024-10-24 06:59:27 字数 2332 浏览 6 评论 0原文

例如，让我们使用一些简单的数据集

+---------+------+------+------------+
| name    | age  | sex  | position   |
+---------+------+------+------------+
| Antony  |   34 | M    | programmer |
| Sally   |   30 | F    | manager    |
| Matthew |   28 | M    | designer   |
+---------+------+------+------------+

我们想要得到的是这样组织的数组

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

作为我们可以使用的粗略近似

$pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);

但结果我们有不必要的嵌套级别

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
                )

        )

)

我试图通过使用回调函数来摆脱这个不必要的嵌套级别

$stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC|PDO::FETCH_FUNC, 'current');

但是由于某些原因，它

Array
  (
   [0] => Array
    (
        [age] => 34
        [sex] => M
        [position] => programmer
    )
  )

不仅传递了一堆标量 34, 'M', 'programmer' 到回调函数:(

你可以使用回调这样的函数来看到它

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

所以有没有办法得到想要的在获取结果后使用 PDO::FETCH_* 模式而不使用 array_map('current', $result) 的结果集？

原文

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 :(

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 ?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（8）

油焖大侠 2024-10-31 06:59:27

这是一个相当老的话题，但我发现了非常简单的解决方案：

->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE)

第一个列将被设置为键，其余将被设置为值。

无需遍历数组或使用 array_map。

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.

No need to walk over the array or use array_map.

回复 原文
扬花落满肩 2024-10-31 06:59:27

公认的答案本质上是一个货物崇拜代码，它只是偶然完成其工作，但其本身没有任何意义。

PDO::FETCH_GROUP 和 PDO::FETCH_UNIQUE 是互斥获取模式，不能一起使用。只有其中之一可以工作。如果将它们组合起来，后者将接管并且 \PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE 实际上只是 PDO::FETCH_UNIQUE。

此外，问题本身是含糊不清的：OP希望结果​​数组由唯一字段索引，而他们称之为分组，这在答案中引起了争议以及。

因此，为了简单起见：

  • 用唯一值索引结果数组（当您希望它由员工姓名索引时，假设它们是唯一的），获取模式必须是
    PDO::FETCH_UNIQUE：

     $pdo->query('选择姓名，e.* FROM 员工 e')->fetchAll(PDO::FETCH_UNIQUE);

  • 对结果进行分组（例如，当您想按部门对员工进行分组时），获取模式必须是
    PDO::FETCH_GROUP：

     $pdo->query('SELECT dept_id, e.* FROM 员工 e')->fetchAll(PDO::FETCH_GROUP);

在这两种情况下，该字段都用作第一级数组索引，必须在 SELECT 字段列表中首先列出。

关于 PDO::FETCH_ASSOC 的注释。鉴于首选获取模式最好在 PDO 构造函数中一次性设置，因此大多数情况下可以在此处省略。

The accepted answer is essentially a cargo cult code, that does its job only by accident, but makes no sense by itself.

PDO::FETCH_GROUP and PDO::FETCH_UNIQUE are mutual exclusive fetch modes, that cannot be used together. Only one of them would work. If you combine them, the latter takes over and \PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE is actually just PDO::FETCH_UNIQUE.

Besides, the question is ambiguous by itself: the OP wants the resulting array to be indexed by the unique field, whereas they called it grouping, which raised a controversy in the answers as well.

So to make it straight:

  • to index the resulting array with unique values (when you want it 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 group the 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);

in both cases the field to be used as the first level array index, must be listed first in the SELECT field list.

A note on the PDO::FETCH_ASSOC. Given that preferred fetch mode is best to be set once for all in the PDO constructor, most of time it can be omitted here.

回复 原文
尘曦 2024-10-31 06:59:27

减少不必要的嵌套数组级别：

$res = $pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
$res = array_map('reset', $res);

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);
回复 原文
海夕 2024-10-31 06:59:27

键关联数组

PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC

Key assoc array

PDO::FETCH_GROUP|PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC
回复 原文
懒猫 2024-10-31 06:59:27

似乎没有人提到过这种变体，因此，为了未来的 Google 员工的利益：

结合 \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);

其中 t.fk 与 t.id 具有一对多关系。

我不必担心 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,
  ],
];

而不是：

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

];

希望它可以帮助那里的人！

供参考：https://phpdelusions.net/pdo/fetch_modes

It doesn't look like anyone has mentioned this variation, so, for the benefit of future Googlers:

Combine the \PDO::FETCH_GROUP and \PDO::FETCH_COLUMN flags. This vastly simplified my SQL statement and returned the exact result set I wanted. It's fast, too.

$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.fk has a one-to-many relationship with t.id.

I didn't have to concern myself with a GROUP BY statement or MySQL's finicky handling of grouping on multiple fields. Best of all, I received results in the form of:

[
  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

回复 原文
陌生 2024-10-31 06:59:27

此答案已过时，请参阅此其他答案。

看起来没有办法将其作为 fetchAll 的一部分来执行。

最好的选择是创建一个扩展 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;
}

This answer is out of date, please see this other answer instead.

It looks like there's no way to do this as part of fetchAll.

Your best bet is going to be creating a class that extends PDO, adding a utility method to it.

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;
}
回复 原文
掐死时间 2024-10-31 06:59:27

我们可以通过扩展语句类来使 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));
    }
}

用法：

$users = $pcs->query("SELECT name, user_id, discipline_id FROM wx_user")->fetchKeyAssoc();

We can make Charles' solution a little nicer by extending the statement class instead:

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();
回复 原文
失而复得 2024-10-31 06:59:27

不知道为什么没有人发布以下解决方案，但它对我来说非常有效：

PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC

因此，将您的声明更改为：

$pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_UNIQUE|PDO::FETCH_ASSOC);

应该正是您想要的。

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.

回复 原文
~没有更多了~

关于作者

花想c

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

已经忘了多久

文章 0 评论 0

15867725375

文章 0 评论 0

LonelySnow

文章 0 评论 0

走过海棠暮

文章 0 评论 0

轻许诺言

文章 0 评论 0

信馬由缰

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文