Converting a database result of 3 columns to associative array

I have a MySQL query that returns the following result:

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

I need to loop through the result and create a new associative array where the key is the ApptTime and the value is the combination of ApptType and Count like so:

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

I’ve tried looping through the mysqli_fetch_assoc result in a variety of ways and creating a new array within the loop but I’m fundamentally not understanding how to go about it so it’s not working out. Any help is greatly appreciated.

Answer

You can achieve this by using two loops. One to prepare the values for the format you want, and one to actually put the values in that format.

First, prepare the data

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

First loop return

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
        )

)

And this is where I would personally stop, and format the values when actually needed to be displayed. However, the following loop can be used to modify the $new_array to the format you wanted.

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

Second loop return

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