Merge MY SQL query result with multiple rows for same item id into single row for json response

I am new to PHP and WordPress. I am trying to build a custom API on WordPress. I have a My Sql query that uses INNER JOIN to join 2 tables and returns multiple rows for same item Id. I am then trying to convert the result set into a JSON response.

The problem is i am getting a new JSON object for each item Id even if the Ids are same.

Please see the My SQL query below:

SELECT id, title, answer from wp_tb1 wp1 INNER JOIN wp_tb2 wp2 ON wp1.belongs_id = wp2.id

Php code:

$data=array();
$count=0;
foreach($list as $l){
            $data[$count]=array(
            "id" =>$l->id,
            "title"=>$l->title,
            "answer"=> array($l->title),
            );
        ++$count;
    }

JSON result looks like:

"[{"id":"1","title":"Title 1","answer":"True"},{"id":"1","title":"Title 1","answer":"False"}]"

As you can see, the id value is repeating and so is Title. I want the response to be something like

"[{"id":"1","title":"Title 1","answer":{"True","False"}}]"

Any help with the query or at the Php code level will be useful.

Answer

Change your loop to this:

$data = [];
foreach ($list as $l) {
    $data[$l->id]['id'] = $l->id;
    $data[$l->id]['title'] = $l->title;
    $data[$l->id]['answer'][] = $l->answer;
}
$data = array_values($data);

This will group the nested array by id and title (I’m assuming that title is the same for the same id).

$data would now contain

[{"id":"1","title":"Title 1","answer":["True","False"]}]

See demo