嵌套的JSON PHP数组

Question

I am trying to generate a JSON array of objects in the form of name, parent, and children from the mySQL data shown below.

Ideally my output would look like:

[{"name":"Abbey Road","parent":"Beatles","children":[{"name":"Come Together","parent":"Abbey Road"}, {"name":"Something","parent":"Abbey Road"},{"name":"Maxwell","parent":"Abbey Road"}, {"name":"Oh! Darling","parent":"Abbey Road"}]}]

With the same structure for the REM album.

Right now, I am only able to get this:

[{"name":"Abbey Road","parent":"Beatles","children":[{"name":"Come Together","parent":"Abbey Road"}]},{"name":"Accelerate","parent":"REM","children":[{"name":"Living Well","parent":"Accelerate"}]}]

The structure works, but I am only returning one record from the database.

Here's my code in PHP that generates the single record return:

$results_select = mysql_query("SELECT album_table.album , album_table.artist, album_table.year,
tracks_table.track
FROM album_table
JOIN tracks_table ON tracks_table.album = album_table.album");


while ($row = mysql_fetch_array($results_select)) {

            if (!isset($info[$row['album']])) {
                $info[$row['album']] = array(
                   'name' => $row['album'],
                   'parent' => $row['artist'],
                   'children' => array(['name' => $row['track'],
                                      'parent' => $row['album'] ])
          );
       }

}
$data = json_encode(array_values($info));
echo $data;

I feel like I am very close to a solution, if I could just return more than one record in my php code.

Thanks very much for any guidance you can provide!

Answer 1

$familys = []
while ($row = mysql_fetch_array($results_select)) {

if (!isset($info[$row['album']]))
  $familys[ $row['album'] ][ $row['artist'] ][] = ['name' => $row['track'], 'parent' => $row['album'] ];   
}

//I think the familys is ok, if you want to make you info format: uncomment below code
/*
foreach($familys as $album => $family)
{
  foreach($family as $parent => $children)
  {
     $info[$album] = array(
                   'name' => $album,
                   'parent' => $parent,
                   'children' => $children
  }
}
*/