使用php从mysql获取属于用户的所有(一对多）记录作为数组 - Get all(one to many) records belongs to a user as an array from mysql using php

Get all(one to many) records belongs to a user as an array from mysql using php

我有一个下表

user    category
1          157
1          158
2          158
2          159
3          157

使用PHP所需的输出是

[
    1 => [157,158],
    2 => [158,159],
    3 => [157]
]

一种解决方案可以是从CCD_ 1&然后像这个一样在上面运行foreach

foreach ($result as $row) {
    $finalResult[$row['user']][] = $row['category'];
}

但是，还有其他最佳的方法吗？

为此使用GROUP_CONCAT()函数。

以下是参考资料：

GROUP_CONCAT()

所以你的查询应该是这样的：

SELECT user, GROUP_CONCAT(category SEPARATOR ',') AS categories FROM your_table GROUP BY user;

输出：

+------+------------+
| user | categories |
---------------------
|   1  |  157,158   |
---------------------
|   2  |  158,159   |
---------------------
|   3  |    157     |
+-------------------+

编辑：

// suppose $conn is your connection handler
$finalResult= array();
$query = "SELECT user, GROUP_CONCAT(category SEPARATOR ',') AS categories FROM your_table GROUP BY user";
if ($result = $conn->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $finalResult[$row['user']] = explode(",", $row['categories']);
    }
}