Nested JSON PHP Array

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!

Stop what you're doing, start over again. You're using database APIs that have been deprecated and unsupported for 5 years now. Alternatives have been available for a decade. http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php — miken32, Dec 09 '16 at 23:38
In addition, the code you show does not result in the output you claim you're getting. Your code generates an associative array which would end up as a JSON object, not a JSON array. — miken32, Dec 09 '16 at 23:48
Hi @tomish did you solved this problem? I got same with you with my DB, only one children record show — da_root, Dec 07 '17 at 06:09

score 0 · Answer 1 · answered Dec 09 '16 at 22:35

0

$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
  }
}
*/

answered Dec 09 '16 at 22:35

LF00

27,015
29
156
295

Hi Kris, thanks, I gave this a shot with some light editing: – tomish Dec 09 '16 at 23:10
but was still unable to get it to work, I end up with – tomish Dec 09 '16 at 23:10
[{"Abbey Road":[{"name":"Come Together","parent":"Beatles"},{"name":"Something","parent":"Beatles"},{"name":"Maxwell","parent":"Beatles"},{"name":"Oh! Darling","parent":"Beatles"}]},{"Accelerate":[{"name":"Living Well","parent":"REM"},{"name":"Man-Sized ","parent":"REM"},{"name":"Supernatural","parent":"REM"},{"name":"Hollow Man","parent":"REM"}]}] – tomish Dec 09 '16 at 23:11
is there any error, from the outpu row artist not work, var dump row – LF00 Dec 09 '16 at 23:31
array(2) { ["Beatles"]=> array(1) { ["Abbey Road"]=> array(4) { [0]=> array(2) { ["name"]=> string(13) "Come Together" ["parent"]=> string(7) "Beatles" } [1]=> array(2) { ["name"]=> string(9) "Something" ["parent"]=> string(7) "Beatles" } -- partial dump – tomish Dec 09 '16 at 23:33
just change familys index, I'm confuse here why familys first index is not album but parent? are you sure there is not an error in your code? – LF00 Dec 09 '16 at 23:45

miken32 · Answer 2 · 2017-09-13T19:42:10.817

You need to change your database code, I recommend using PDO.

With every loop of your code you were rewriting the array instead of adding to it with the $array[] construct. This should work, might require some tweaking.

<?php
$dsn    = "mysql:host=localhost;dbname=testdb;charset=utf8";
$db     = new PDO($dsn, $username, $password);
$sql    = "SELECT album_table.album, artist, year, track FROM album_table LEFT JOIN tracks_table USING (album)";
$result = $db->query($sql);

while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
    if (!isset($info[$row["album"]])) {
        $info[$row["album"]] = [
            "name"     => $row["album"],
            "parent"   => $row["artist"],
            "children" => [],
        ];
    }
    $info[$row["album"]]["children"][] = [
        "name"   => $row["track"],
        "parent" => $row["album"],
    ];
}

$data = array_values($info);
header("Content-Type: application/json");
echo json_encode($data);

Including a value from a parent node in the child seems redundant. BTW, what do you do when two artists put out an album with the same name?

Nested JSON PHP Array

2 Answers2