How to build a multidimensional json array with PHP while statement?

Question

As you can see in the code below, there are two appointments on the 2020-01-13 but they get generated as a separate array. How can I put them both on the same array since they are on the same day? I'm fetching all the information from a database using mysqli but for the sake of this code I'm only printing the necessary. It works but I need to know how to gather the appointments that have the same date in the same array.

php

//declare variable
$result = array();

//iterate
while($schedule = $result_data->fetch_assoc()){

//start time conversion into american time
// 24-hour time to 12-hour time 
$start_time  =date("g:i a", strtotime($schedule['start_time']));

//end time conversion into american time
$end_time  =date("g:i a", strtotime($schedule['end_time']));

$my_schedule_start .= $start_time;
$my_schedule_end .= $end_time;

$schedule_date= $schedule['cleaning_date'];


$result[] = array(
"$schedule_date" => [
    "number"=>'1',
    "badgeClass"=>'',
    "url"=>'url',

    "dayEvents"=>[
    "title"=>$schedule['first_name'].' '.$schedule['last_name'],
    "status"=>$schedule['cleaning_status'],
    "time"=>$start_time .' - '.$end_time
    ]
]
);  

}

//send response back to jquery
header('Content-Type: application/json');
echo json_encode($result);

this php prints this

[{"2020-01-15":{
    "number":"1",
    "badgeClass":"",
    "url":"url",
    "dayEvents":{
        "title":"Jen Doe",
        "status":"Booked",
        "time":"11:00 am - 12:00 pm"}
        }
        },
        {"2020-01-13":{
            "number":"1",
            "badgeClass":"",
            "url":"url",
            "dayEvents":{
                "title":"John Doe",
                "status":"Booked",
                "time":"2:00 pm - 5:00 pm"}
                }
                },
                {"2020-01-13":{
                    "number":"1",
                    "badgeClass":"",
                    "url":"url",
                    "dayEvents":{
                        "title":"Alfred Doe",
                        "status":"Booked",
                        "time":"11:00 am - 12:00 pm"}
                        }
                        }
                     ]

but my desired outcome is this

[{
        "2020-01-15": {
            "number": "1",
            "badgeClass": "",
            "url": "url",
            "dayEvents": {
                "title": "Jen Doe",
                "status": "Booked",
                "time": "11:00 am - 12:00 pm"
            }
        }
    },
    {
        "2020-01-13": {
            "number": "2",
            "badgeClass": "",
            "url": "url",
            "dayEvents": [{
                    "title": "John Doe",
                    "status": "Booked",
                    "time": "2:00 pm - 5:00 pm"
                },
                {
                    "title": "Alfred Doe",
                    "status": "Booked",
                    "time": "11:00 am - 12:00 pm"
                }
            ]
        }
    }
]

Not very clear. Can you share the initial array and expected array? — nice_dev, Jan 17 '20 at 09:27
`$result[] = $row; print_r($result);` see what you get and then see this : https://stackoverflow.com/questions/24535547/using-php-multidimensional-arrays-to-convert-mysql-to-json — , Jan 17 '20 at 09:35
Right now, you are just adding new items all the time. You will need to _check_ if an item for the given date exists already - and if so, modify it accordingly. This will be easiest, if you use the date as key on the top level of $result - then you can simply use isset to check, if an item for that date already exists. If you _really_ need one object wrapped into an array as result - then wrap the whole thing into an additional array level afterwards. (`$result = [$result]` at the very end.) — 04FS, Jan 17 '20 at 09:37
@chiliNUT: json_encode(). The tag available is jsonencoder for some reason. I don't have enough points to create mi own tags so. — Grogu, Jan 17 '20 at 18:29
@Dilek : I already know what I'm getting. I will update the question then. — Grogu, Jan 17 '20 at 18:31
@vivek_23: I'm sorry. I have updated the question. Is it clearer now? — Grogu, Jan 17 '20 at 18:39

score 1 · Accepted Answer · answered Jan 17 '20 at 09:41

This code when creating the schedule, creates each days events indexed by the date itself, then when a new event is found it will check if this date is already set. If so, it will increment the count and add the new event. The only complication is that it needs to check if it only has 1 existing entry - this needs to be converted to an array first. To remove the index, the json_encode() uses array_values() first...

while($schedule = $result_data->fetch_assoc()){
    //start time conversion into american time
    // 24-hour time to 12-hour time
    $start_time  =date("g:i a", strtotime($schedule['start_time']));

    //end time conversion into american time
    $end_time  =date("g:i a", strtotime($schedule['end_time']));

    $my_schedule_start .= $start_time;
    $my_schedule_end .= $end_time;

    $schedule_date= $schedule['cleaning_date'];

    if ( isset($result[$schedule_date])){
        // Increment count
        $result[$schedule_date][$schedule_date]["number"]++;
        // Convert single item to array
        if ( !isset($result[$schedule_date][$schedule_date]["dayEvents"][0]))    {
            $result[$schedule_date][$schedule_date]["dayEvents"] = 
                [$result[$schedule_date][$schedule_date]["dayEvents"]];
        }
        // Add in new event to dayEvents
        $result[$schedule_date][$schedule_date]["dayEvents"][] = [
            "title"=>$schedule['first_name'].' '.$schedule['last_name'],
            "status"=>$schedule['cleaning_status'],
            "time"=>$start_time .' - '.$end_time
        ];

    }
    else    {
        // Create new days schedule
        $result[$schedule_date] = [
            "$schedule_date" => [
                "number"=>'1',
                "badgeClass"=>'',
                "url"=>'url',

                "dayEvents"=>[
                    "title"=>$schedule['first_name'].' '.$schedule['last_name'],
                    "status"=>$schedule['cleaning_status'],
                    "time"=>$start_time .' - '.$end_time
                ]
            ]
        ];
    }

}

header('Content-Type: application/json');
echo json_encode(array_values($result), JSON_PRETTY_PRINT);

score 0 · Answer 2 · answered Jan 17 '20 at 09:44

Based on my observation on the code provided above and the desired output, you can do something like this:

$scheduledDate = date('Y-m-d');
$my_schedule_start = "";
$my_schedule_end = "";
$finalResult = [];

while($schedule = $result_data->fetch_assoc()){
    $start_time  =date("g:i a", strtotime($schedule['start_time']));
    $end_time  =date("g:i a", strtotime($schedule['end_time']));

    $my_schedule_start .= $start_time;
    $my_schedule_end .= $end_time;

    $schedule_date= $schedule['cleaning_date'];

    $res = new stdClass;

    $res->number = 1;
    $res->badgeClass = "";
    $res->url = 'url';

    $dayEvents = new stdClass;
    $dayEvents->title = $schedule['first_name'].' '.$schedule['last_name'];
    $dayEvents->status = $schedule['cleaning_status'];
    $dayEvents->time = $start_time .' - '.$end_time;

    $res->dayEvents = $dayEvents;
    $finalResult[] = $res;
}

echo json_encode($finalResult);

Somethings to Note here:

1.) I have given the code base on the first entry in the output provided. if you want to go deeper then, instead of using stdClass on $dayEvents, you can directly declare $dayEvents as an array and use individual stdClass inside and push it to dayEvents like I have done for the $finalResult array.

2.) You may receive a notice for $my_schedule_start and $my_schedule_end not declared earlier so I took the liberty to declare them outside the While loop with empty values. But please feel free to change them according to your needs.

3.) If the $res = new stdClass; doesn't work then you can try $res = new \stdClass instead.

4.) If you find any syntax errors, please forgive me as I am also Human :)

Summary:

The stdClass is PHP's internal feature to declare objects in PHP. You can also use the short syntax like $dayEvents = {}; instead of $dayEvents = new stdClass;.

You can find more information on stdClass Here

Hope this helps.

How to build a multidimensional json array with PHP while statement?

2 Answers2