php - 将 3 列的数据库结果转换为关联数组

Question

我有一个返回以下结果的 MySQL 查询:

+----------+----------+-------+
| ApptTime | ApptType | Count |
+----------+----------+-------+
| 16:00    | pickup   | 1     |
+----------+----------+-------+
| 16:30    | dropoff  | 1     |
+----------+----------+-------+
| 16:30    | pickup   | 6     |
+----------+----------+-------+
| 16:45    | pickup   | 2     |
+----------+----------+-------+
| 16:45    | dropoff  | 1     |
+----------+----------+-------+

我需要遍历结果并创建一个新的关联数组，其中键是 ApptTime值是 ApptType的组合和 Count像这样:

"16:00" => "1 pickup"
"16:30" => "1 dropoff, 6 pickup" //the order in which appointments are listed is irrelevant. 
"16:45" => "1 dropoff, 2 pickup"

我试过循环遍历 mysqli_fetch_assoc以多种方式产生并在循环中创建一个新数组，但我从根本上不明白如何去做，所以它不起作用。
任何帮助是极大的赞赏。

Answer 1

您可以通过使用两个循环来实现这一点。一种是为您想要的格式准备值，另一种是将值实际放入该格式中。
一、准备资料

$array = array(
    array('ApptTime' => "16:00", 'ApptType' => "pickup", 'Count' => 1),
    array('ApptTime' => "16:30", 'ApptType' => "dropoff", 'Count' => 1),
    array('ApptTime' => "16:30", 'ApptType' => "pickup", 'Count' => 6),
    array('ApptTime' => "16:45", 'ApptType' => "pickup", 'Count' => 2),
    array('ApptTime' => "16:45", 'ApptType' => "dropoff", 'Count' => 1)
);

$new_array = [];
foreach($array as $arr) {

    $time = $arr['ApptTime'];
    $type = $arr['ApptType'];
    $count = $arr['Count'];

    //check if `$time` exists as key of `$new_array`, if not, create it with an empty array value
    if(!array_key_exists($time, $new_array)) {
        $new_array[$time] = [];
    }

    //add string e.g "1 pickup" as array value to array `$new_array` with key `$time`
    $new_array[$time][] = "{$count} {$type}";
}

第一次循环返回

Array
(
    [16:00] => Array
        (
            [0] => 1 pickup
        )

    [16:30] => Array
        (
            [0] => 1 dropoff
            [1] => 6 pickup
        )

    [16:45] => Array
        (
            [0] => 2 pickup
            [1] => 1 dropoff
        )

)

这就是我个人会停下来的地方，并在实际需要显示时格式化这些值。但是，可以使用以下循环来修改 $new_array到你想要的格式。

foreach($new_array as &$n_arr) {
    $n_arr = implode(', ', $n_arr);
}

第二次循环返回

Array
(
    [16:00] => 1 pickup
    [16:30] => 1 dropoff, 6 pickup
    [16:45] => 2 pickup, 1 dropoff
)