How to generate a nested array structure from a resultset?

Question

I have this Json array events

[
    {
        "id": "4",
        "event_name": "Harliquins 7s",
        "event_description": "Ruggby game",
        "event_date": null,
        "event_venue": "UFA grounds",
        "event_company": "Harliquins",
        "event_image": "http://www.aal-europe.eu/wp-content/uploads/2013/12/events_medium.jpg",
        "event_ticket_no": "200",
        "paybill": "25666",
        "status": "0"
    },
    {
        "id": "5",
        "event_name": "christie &s",
        "event_description": "Ruggby",
        "event_date": "1-2-2917",
        "event_venue": "KISUMU ground",
        "event_company": "Kenya Games",
        "event_image": "N/A",
        "event_ticket_no": "400",
        "paybill": "79000",
        "status": "0"
    }
]

I also have this options

[
    {
        "id": "4",
        "event_id": "5",
        "options_id": "1",
        "seasonal": "1",
        "amount": "300"
    },
    {
        "id": "5",
        "event_id": "5",
        "options_id": "2",
        "seasonal": "1",
        "amount": "400"
    }
]

I want to get this as the results

[
    {
        "id": "4",
        "event_name": "Harliquins 7s",
        "event_description": "Ruggby game",
        "event_date": null,
        "event_venue": "UFA grounds",
        "event_company": "Harliquins",
        "event_image": "http://www.aal-europe.eu/wp-content/uploads/2013/12/events_medium.jpg",
        "event_ticket_no": "200",
        "paybill": "25666",
        "status": "0"
    },
    {
        "id": "5",
        "event_name": "christie &s",
        "event_description": "Ruggby",
        "event_date": "1-2-2917",
        "event_venue": "KISUMU ground",
        "event_company": "Kenya Games",
        "event_image": "N/A",
        "event_ticket_no": "400",
        "paybill": "79000",
        "status": "0",
        "Options:" [
            {
                 "id": "4",
                 "event_id": "5",
                 "options_id": "1",
                 "seasonal": "1",
                 "amount": "300"
             },
             {
                 "id": "5",
                 "event_id": "5",
                 "options_id": "2",
                 "seasonal": "1",
                 "amount": "400"
             }
         ]
    }
]

Here is my code:

while( $row = $result->fetch_assoc()){
    $new_array[] =$row;
    $new_array['options'] =getTicketOptions($row['id']);
}
echo json_encode($new_array);

Each event object has an option array.

Possible duplicate of [What is the best method to merge two PHP objects?](https://stackoverflow.com/questions/455700/what-is-the-best-method-to-merge-two-php-objects) — Mark Ryan Orosa, Nov 02 '17 at 11:47
I have concerns about the efficiency of your while loop. What is `getTicketOptions()` doing? is it calling database queries? or is it accessing a static array? or something else? If this is making iterated queries, perhaps a single JOIN query would be the better way. Please clarify this aspect of your code. — mickmackusa, Nov 02 '17 at 22:20

score 0 · Accepted Answer · answered Nov 02 '17 at 11:32

You are nearly there already. All you need to do is assigning $row and Options at once:

while( $row = $result->fetch_assoc()){
    $new_array[] = array($row, "Options" => getTicketOptions($row['id']));
}
echo json_encode($new_array);

The reason is that with $new_array[] you automatically set a new key without knowing its value. Therefore you can not just easily add something into this record later on. Doing both at once solves it for you.

score 0 · Answer 2 · answered Nov 02 '17 at 11:46

0

It seems to me you only need to merge the 2 objects:

$mergedObject = (object) array_merge((array) $events, (array) $options);

answered Nov 02 '17 at 11:46

Mark Ryan Orosa

847
1
6
21

3

You are not meant to copy the answer from the duplicate page that you found. This creates redundant information on SO. – mickmackusa Nov 02 '17 at 12:09
oh okay. then does that mean I shouldn't bother to answer? or should I just put it in as a comment maybe? – Mark Ryan Orosa Nov 02 '17 at 21:52
Flagging/Voting to close as duplicate is all that is required. This helps the OP find the correct solution. Reduces wasted volunteer time (reading a question and answering an already answered question). OP's are expected to search and research before asking -- marking as duplicate indicates that exhaustive research was not done (due to lack of knowledge or laziness). This subject is a point of contention between different users on SO; if you want to get a broader perspective on the matter I recommend that you search Meta. You will see more justifications and a few counter-arguments. – mickmackusa Nov 02 '17 at 22:05

score 0 · Answer 3 · answered Nov 02 '17 at 12:07

I think you want to add second array at last position?? If yes then follow answer.

First array

[{"id":"4","event_name":"Harliquins 7s"},{"id":"5","event_name":"Harliquins 7s"}]

Second Array

[{"id":"4","event_id":"5","options_id":"1"},{"id":"4","event_id":"5","options_id":"1"}]

Required array

[{"id":"4","event_name":"Harliquins 7s"},{"id":"5","event_name":"Harliquins 7s","options":[{"id":"4","event_id":"5","options_id":"1"},{"id":"4","event_id":"5","options_id":"1"}]}]

Php Code

$last_key = end(array_keys($data));
        foreach ($data as $key => $value) {
            if($key == $last_key){
                $data[$key]['options'] = $data2;
            }

        }

        echo json_encode($data);

score 0 · Answer 4 · answered Nov 02 '17 at 12:52

I get the same result by using array_map and array_filter functions, You can try my code here: php sandbox, it always create options property in events array:

$events = json_decode($events, true);
$options = json_decode($options, true);

$result = array_map( function ($item) use ($options) {

    $item['options'] = array_filter($options, function ($option) use ($item) {
       return $item['id'] == $option['event_id'];
    });

    return $item;

}, $events);

print_r($result);

How to generate a nested array structure from a resultset?

4 Answers4