SQL merges results into an object in codeigniter

Question

ok, some background

• codeigniter only
• not a fan of sql and server-side scripts
• I know what connections
• I have a many-to-many database for the first time

because the resulting connections usually have the following example. but I wanted to parse this without creating code to ignore repetitions. This is an example of joining three tables. the question of duplicate values ​​increases when I join more tables:

table1.authorid table1.authorname table2.books table3.favorited 1 john john book 1 jean 1 john john book 1 joe 1 john john book 2 ken 1 john john book 2 mark 2 mark mark book 1 alice 2 mark mark book 1 ted 2 mark mark book 2 sarah 2 mark mark book 2 denise 

Is there a way in codeigniter (or plain PHP) that I can get this array form and turn it into something like json (and parse it like json)

 $result = [ { 'authorid':1, 'authorname':'john', 'books':['john book1','john book2'], 'favorited':['jean','joe','ken','mark'] }, { 'authorid':2, 'authorname':'mark', 'books':['mark book1','mark book2'], 'favorited':['alice','ted','sarah','denise'] } ] 

Update : this is not limited to this depth of objects / arrays (as in the example). it can go deeper (arrays in arrays, arrays in objects, objects in arrays, objects in objects)

Answer 1

 // first, we need the SQL results in the $result_array variable $sql = 'SELECT ...'; // your SQL command $result_array = $this->db->query($sql)->result_array(); // codeigniter code // here the real answer begins $result = array(); foreach ($result_array as $row) { if (!isset($result[$row['authorid']]) { $author = new StdClass(); $author->authorid = $row['authorid']; $author->authorname = $row['authorname']; $author->books = array($row['books']); $author->favorited = array($row['favorited']); $result[$row['authorid']] = $author; } else { if (!in_array($row['books'], $result[$row['authorid']]->books)) { $result[$row['authorid']]->books[] = $row['books']; } if (!in_array($row['favorited'], $result[$row['authorid']]->favorited)) { $result[$row['authorid']]->favorited[] = $row['favorited']; } } } $result = array_values($result); echo json_encode($result);