Converting a database result of 3 columns to associative array

user_2912859 0
Viewed 184

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.

arrays php
2 Answers

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

Use this logic :

I have taken sample array assuming you are getting same in MySql resultset.

<?php

$resultset = array(
     array("16:00","pickup", 1),
     array("16:30","Drop of ", 1),
     array("16:30","pickup", 6),
     array("16:45","pickup", 2),
     array("16:45","Drop of ", 1)

    );
    $sample_array=array();
    foreach ( $resultset as $value )
   {
       //var_dump($value);
       if(!array_key_exists($value[0],$sample_array)){

           $sample_array[$value[0]] = array();
       }
       array_push($sample_array[$value[0]],$value[2]." ".$value[1]);
       //var_dump($sample_array);

   } 
   var_dump($sample_array);
?>

Output :

array(3) {
  ["16:00"]=>
  array(1) {
    [0]=>
    string(8) "1 pickup"
  }
  ["16:30"]=>
  array(2) {
    [0]=>
    string(10) "1 Drop of "
    [1]=>
    string(8) "6 pickup"
  }
  ["16:45"]=>
  array(2) {
    [0]=>
    string(8) "2 pickup"
    [1]=>
    string(10) "1 Drop of "
  }
}
user_62850540
Related