php - 从数据库中对用户进行分组

Question

我有一张这样的 table

+--------+----------+--------------+------------------+
| id     | user_id  | preference_id|Sub_Preference_Id |
+--------+----------+--------------+------------------+
|  15145 | 55       |    2         |      7           |
|  15146 | 56       |    2         |      7           |
|  15148 | 58       |    3         |      10          |
|  15150 | 52       |    3         |      12          |
|  15314 | 59       |    1         |      1           |
|  15315 | 60       |    3         |      12          |
|  15316 | 57       |    3         |      12          |
+--------+----------+--------------+------------------+

我想首先根据相同的preference_id对成员进行分组，然后在该特定的相同preference_id中再次对相同的Sub_Preference_Id进行分组。

意思是，我想要一个这样的组:

+--------+----------+--------------+------------------+
| id     | user_id  | preference_id|Sub_Preference_Id |
+--------+----------+--------------+------------------+
|  15145 | 55       |    2         |      7           |
|  15146 | 56       |    2         |      7           |
+--------+----------+--------------+------------------+

+--------+----------+--------------+------------------+
| id     | user_id  | preference_id|Sub_Preference_Id |
+--------+----------+--------------+------------------+
|  15150 | 52       |    3         |      12          |
|  15315 | 60       |    3         |      12          |
|  15316 | 57       |    3         |      12          |
+--------+----------+--------------+------------------+

+--------+----------+--------------+------------------+
| id     | user_id  | preference_id|Sub_Preference_Id |
+--------+----------+--------------+------------------+
|  15314 | 59       |    1         |      1           |
+--------+----------+--------------+------------------+

+--------+----------+--------------+------------------+
| id     | user_id  | preference_id|Sub_Preference_Id |
+--------+----------+--------------+------------------+
|  15148 | 58       |    3         |      10          |
+--------+----------+--------------+------------------+

现在我已经完成了:

$preference_idss = array('1','2','3','4','5','6');

foreach ($preference_idss as $preference_ids) {

    $query ="SELECT * FROM gic_user_preference where preference_id='$preference_ids'";

    $result_preferencea = mysqli_query($createCon->connect(), $query);

    while ($result_preference2 = mysqli_fetch_assoc($result_preferencea)) {

        $groupedData[$result_preference2['sub_preference_id']][] = $result_preference2;
    }

}

但是我失败了？

知道如何使用选择查询而不是使用 php foreach、数组等来选择数据吗？

Answer 1

你什么都不需要。只需循环每条记录并根据首选项 ID 和 subPreferenceID 创建一个数组即可:

例如:

$query ="SELECT * FROM gic_user_preference order by preference_id, sub_preference_id";

$result_preferencea = mysqli_query($createCon->connect(), $query);
$groupedData = [];

while ($result_preference2 = mysqli_fetch_assoc($result_preferencea)) {
    $prefId = $result_preference2["preference_id"];
    $subPrefId = $result_preference2["sub_preference_id"];
    if(!isset($groupedData[$prefId])) $groupedData[$prefId] = [];
    $groupedData[$prefId][$subPrefId] = $result_preference2;
}