Database Schema Treeview PHP MYSQL

I am trying to create a treeview (ideally using bootstrap) from a MYSQL database table that is essentially a data dictionary structure. Every example I have seen creates a parentid in order to create the json/array input for the treeview however my data structure has all of the hierarchy levels on each line.

The hierarchy would be:

• Database

  • Schemas
    • Tables

• Database

  • Schemas
    • Tables

My database table has 3 columns… database, schema, and table. Each row has all three attributes, so the full hierarchy. What makes this a bit more tricky is the same schema and table can exist in multiple databases.

Any ideas for how I should go about approaching this?

Or perhaps as mentioned below how would I go from an array to a nested array of JavaScript objects that is the input of a treeview?

Here is the php for creating the array:

$stmt = $pdo->prepare('SELECT * FROM MyTable');
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);

Database Structure and Data Image

Answer

You’ll need to iterate through all rows and insert them into an appropriate map(associative array) in order to “inflate” the data structure.

Something like this:

//Fetch all rows from database into $result
$databases=[];
foreach($result as $row){
    $database=$row["database"];
    $schema=$row["schema"];
    $table=$row["object"];
    if(!array_key_exists($database, $databases))
        $databases[$database]=[];
    if(!array_key_exists($schema, $databases[$database]))
        $databases[$database][$schema]=[];
    array_push($databases[$database][$schema], $table);
}