Nest JSON from MySQL using PHP

I have a MySQL query which returns the following table:

   event_id   name      inv_id    state
       1   At John's   1          0
       1   At John's   2          2
       1   At John's   3          2
       4   Meeting     4          1

and I have to return, via PHP, a formatted JSON which looks like the following:

     [  
       {  
          "id":"1",
          "nombre":"At John's",
          "invitations":[
           { "inv_id":"1", "state":"0" }
           { "inv_id":"2", "state":"2" }
           { "inv_id":"3", "state":"2" }
          ]
       },
       {  
          "id":"4",
          "nombre":"Meeting",
          "invitations":[
           { "inv_id":"4", "state":"1" }
          ]
       }
    ]

Basically, I need to nest the invitations inside each event.

Answer

Try this.

$rows = [
    ['event_id' => 1, 'name' => 'At John's', 'inv_id' => 1, 'state' => 0],
    ['event_id' => 1, 'name' => 'At John's', 'inv_id' => 2, 'state' => 2],
    ['event_id' => 1, 'name' => 'At John's', 'inv_id' => 3, 'state' => 2],
    ['event_id' => 4, 'name' => 'Meeting', 'inv_id' => 4, 'state' => 1]
];

$result = array_reduce($rows, function($result, $row) {
    if (!array_key_exists($row['event_id'], $result)) {
        $result[$row['event_id']] = [
            'id' => $row['event_id'],
            'nombre' => $row['name'],
            'invitations' => []
        ];
    }

    $result[$row['event_id']]['invitations'][] = [
        'inv_id' => $row['inv_id'],
        'state' => $row['state']
    ];

    return $result;
}, []);

echo json_encode(array_values($result));