Database Schema Treeview PHP MYSQL

Question

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

What exactly is the challenge here? Just query your table and load the data into an in-memory data structure(assuming the data does not exceed a reasonable amount) that matches your definitions(a multidimensional associative array would suffice). You would just then iterate through each level(effectively 3 nested loops) and print the hierarchy. This seems fairly straightforward to me. I think you will have better luck with an answer if you elaborate on the specific problems you see with implementing it. — akenion
– akenion, Commented Jul 8, 2020 at 1:50
I guess my question from there would be how to go from an array in PHP to a nested array of JavaScript objects, as that is the required input for a bootstrap treeview. — EpicFail18
– EpicFail18, Commented Jul 8, 2020 at 2:01
So converting a flat, two-dimensional array(i.e [["database", "schema", "table"], ["database", "schema", "table"], ...]) to an associative array with "database" and "schema" as keys referencing an indexed array of tables, correct? I can answer that. — akenion
– akenion, Commented Jul 8, 2020 at 2:07
When I use json_encode for the php array I get this structure ( [{"database":"Company 1","schema":"Production","object":"Brands"},{"database":"Company 1","schema":"Production","object":"Categories"}] ).... so I think it just needs to be converted into nested json instead of the array of json strings it is now. — EpicFail18
– EpicFail18, Commented Jul 8, 2020 at 2:11
Right. I understand your question now. I'll write an answer. — akenion
– akenion, Commented Jul 8, 2020 at 2:12

akenion · Accepted Answer · 2020-07-08 02:17:29Z

1

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);
}

answered Jul 8, 2020 at 2:17

akenion

8151 gold badge6 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EpicFail18 Over a year ago

Awesome, much appreciated!

Collectives™ on Stack Overflow

Database Schema Treeview PHP MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related