Creating hierarchical JSON from MySQL results and PHP for D3.js tree?

Question

I am trying to create the following JSON (much simplified...) from database results using PHP:

{
    "name": "Bob",
    "children": [{
            "name": "Ted",
            "children": [{
                "name": "Fred"
            }]
        },
        {
            "name": "Carol",
            "children": [{
                "name": "Harry"
            }]
        },
        {
            "name": "Alice",
            "children": [{
                "name": "Mary"
            }]
        }
    ]
}

The database tables:

Table 'level_1':

level_1_pk| level_1_name
-------------------------
 1 | Bob  


Table 'level_2':

level_2_pk| level_2_name | level_1_fk
-------------------------
 1 | Ted                 | 1
 2 | Carol               | 1
 3 | Alice               | 1


Table 'level_3':

level_3_pk| level_3_name | level_2_fk
-------------------------
 1 | Fred                | 1
 2 | Harry               | 2
 3 | Mary                | 3

The code:

$query = "SELECT * 
FROM level_1
LEFT JOIN level_2
ON level_1.level_1_pk = level_2.level_1_fk";
$result = $connection->query($query);
 while ($row = mysqli_fetch_assoc($result)){
        $data[$row['level_1_name']] [] = array(
            "name" => $row['level_2_name']
            );
    }

echo json_encode($data);

Produces:

{"Bob":[{"name":"Ted"},{"name":"Carol"},{"name":"Alice"}]}

Question:

How can I get the next level, level_3, and include the text "children" and level_3 children in the JSON as required in the JSON defined above?

I imagine I will need the PHP to be recursive given more children in the JSON.

SQL

Answer 1

This doesn't look like a decent design for hierarchical data. Consider another approach like adjacency list.

Solution #1 - MySQL 8 JSON support:

With MySQL 8 you can use JSON_ARRAYAGG() and JSON_OBJECT() to get the JSON result with SQL only:

select json_object(
  'name', l1.level_1_name,
  'children', json_arrayagg(json_object('name', l2.level_2_name, 'children', l2.children))
) as json
from level_1 l1
left join (
  select l2.level_2_name
       , l2.level_1_fk
       , json_arrayagg(json_object('name', l3.level_3_name)) as children
  from level_2 l2
  left join level_3 l3 on l3.level_2_fk = l2.level_2_pk
  group by l2.level_2_pk
) l2 on l2.level_1_fk = l1.level_1_pk
group by level_1_pk

The result is:

{"name": "Bob", "children": [{"name": "Ted", "children": [{"name": "Fred"}]}, {"name": "Carol", "children": [{"name": "Harry"}]}, {"name": "Alice", "children": [{"name": "Mary"}]}]}

db-fiddle demo

Formatted:

{
  "name": "Bob",
  "children": [
    {
      "name": "Ted",
      "children": [
        {
          "name": "Fred"
        }
      ]
    },
    {
      "name": "Carol",
      "children": [
        {
          "name": "Harry"
        }
      ]
    },
    {
      "name": "Alice",
      "children": [
        {
          "name": "Mary"
        }
      ]
    }
  ]
}

Solution #2 - Constructing JSON with GROUP_CONCAT():

If the names don't contain any quote carachters, you can manually construct the JSON string in older versions using GROUP_CONCAT():

$query = <<<MySQL
    select concat('{',
      '"name": ', '"', l1.level_1_name, '", ',
      '"children": ', '[', group_concat(
        '{',
        '"name": ', '"', l2.level_2_name, '", ',
        '"children": ', '[', l2.children, ']',
        '}'
      separator ', '), ']'        
    '}') as json
    from level_1 l1
    left join (
      select l2.level_2_name
           , l2.level_1_fk
           , group_concat('{', '"name": ', '"',  l3.level_3_name, '"', '}') as children
      from level_2 l2
      left join level_3 l3 on l3.level_2_fk = l2.level_2_pk
      group by l2.level_2_pk
    ) l2 on l2.level_1_fk = l1.level_1_pk
    group by level_1_pk
MySQL;

The result would be the same (see demo)

Solution #3 - Constructing nestet structure with PHP objects:

You can also write a simpler SQL query and construct the nested structure in PHP:

$result = $connection->query("
    select level_1_name as name, null as parent
    from level_1
    union all
    select l2.level_2_name as name, l1.level_1_name as parent
    from level_2 l2
    join level_1 l1 on l1.level_1_pk = l2.level_1_fk
    union all
    select l3.level_3_name as name, l2.level_2_name as parent
    from level_3 l3
    join level_2 l2 on l2.level_2_pk = l3.level_2_fk
");

The result is

name    | parent
----------------
Bob     | null
Ted     | Bob
Carol   | Bob
Alice   | Bob
Fred    | Ted
Harry   | Carol
Mary    | Alice

demo

Note: The name should be unique along all tables. But I don't know what result you would expect, if duplicates were possible.

Now save the rows as objects in an array indexed by the name:

$data = []
while ($row = $result->fetch_object()) {
    $data[$row->name] = $row;
}

$data will now contain

[
    'Bob'   => (object)['name' => 'Bob',   'parent' => NULL],
    'Ted'   => (object)['name' => 'Ted',   'parent' => 'Bob'],
    'Carol' => (object)['name' => 'Carol', 'parent' => 'Bob'],
    'Alice' => (object)['name' => 'Alice', 'parent' => 'Bob'],
    'Fred'  => (object)['name' => 'Fred',  'parent' => 'Ted'],
    'Harry' => (object)['name' => 'Harry', 'parent' => 'Carol'],
    'Mary'  => (object)['name' => 'Mary',  'parent' => 'Alice'],
]

We can now link the nodes in a single loop:

$roots = [];
foreach ($data as $row) {
    if ($row->parent === null) {
        $roots[] = $row;
    } else {
        $data[$row->parent]->children[] = $row;
    }
    unset($row->parent);
}

echo json_encode($roots[0], JSON_PRETTY_PRINT);

The result:

{
    "name": "Bob",
    "children": [
        {
            "name": "Ted",
            "children": [
                {
                    "name": "Fred"
                }
            ]
        },
        {
            "name": "Carol",
            "children": [
                {
                    "name": "Harry"
                }
            ]
        },
        {
            "name": "Alice",
            "children": [
                {
                    "name": "Mary"
                }
            ]
        }
    ]
}

demo

If multiple root nodes are possible (multiple rows in level_1_name), then use

json_encode($roots);

Answer 2

I will recommend the following recursive:

function getPeople($levelNum = 1, $parent = 0) {
    if ($levelNum > 3) return array(); // break recursion condition
    global $connection;
    $level = 'level_' . $levelNum; // also can check here if the table exist by this name
    $query = "SELECT * FROM ". $level; 
    if ($parent) // if there is parent add him to query
        $query .= "WHERE " . $level . "_fk = " . $parent;
    $result = $connection->query($query);
    while ($row = mysqli_fetch_assoc($result)) { // for each row:
        $idCol = $level . "_pk"; // get the primary ID key
        $id = $row[$idCol]; // get the ID
        $localResult[$id] = array("Name" => $row[$level . "_name"]); // set local array with key as ID and name
    }

    foreach ($localResult as $id => $elem) { // elem is array with only name
        $children = getPeople($levelNum + 1, $id); // recursively get all children
        if ($children)
            $elem["children"] = $children;
        $data[] = $elem;  // append the new elem to origin array
    }
    return $data;
}

Initial call should be like getPeople() or json_encode(getPeople());

Notice - I used max depth as recursive break assuming you know max depth - you can also (and I recommend you do) skip the break condition and just check if the table name exist ! (as $level string)

I wrote it as pseudo code as I didn't actually build the table - it may have syntax error but the logic should be solid...