Use PHP to extract data from MariaDB and generate hierarchical JSON - tree view of folders
Get the solution ↓↓↓
How to use PHP to dynamically create a hierarchical JSON from two MariaDB tables. The JSON will be used to render a tree view of folders via JavaScript in a web page.
I found the JavaScript library to render the tree, but I don't know what SQL queries to use and how to combine them in PHP to generate the JSON.
In this example there are some books, which are part of some folders. The folders are "arranged" hierarchical (with parent, respectively sub-folders), and each folder name is unique in all the folder tree.
Table Books:
id, name, folder, author, description
1, Book 0, , foo, bar
2, Book 1, Folder 1, foo, bar
3, Book 2, Folder 1, foo, bar
4, Book 3, Folder 2, foo, bar
5, Book 4, Folder 2, foo, bar
6, Book 5, Subfolder 1.1, foo, bar
7, Book 6, Subfolder 1.2, foo, bar
8, Book 7, Subfolder 1.1.1, foo, bar
9, Book 8, Subfolder 1.1.2, foo, bar
10, Book 9, Subfolder 1.1.1.1, foo, bar
Table Folders:
id, name, parent
1, Folder 1,
2, Folder 2,
3, Subfolder 1.1, Folder 1
4, Subfolder 1.2, Folder 1
5, Subfolder 1.1.1, Subfolder 1.1
6, Subfolder 1.1.2, Subfolder 1.1
7, Subfolder 1.1.1.1, Subfolder 1.1.1
Expected JSON Tree:
var structure = [{
type: Tree.FOLDER,
name: 'Tree',
children: [{
name: 'Book 0'
}, {
type: Tree.FOLDER,
name: 'Folder 1',
children: [{
name: 'Book 1'
}, {
name: 'Book 2'
}, {
type: Tree.FOLDER,
name: 'Subfolder 1.1',
children: [{
name: 'Book 5'
}, {
type: Tree.FOLDER,
name: 'Subfolder 1.1.1',
children: [{
name: 'Book 7'
}, {
type: Tree.FOLDER,
name: 'Subfolder 1.1.1.1',
children: [{
name: 'Book 9'
}]
}]
}, {
type: Tree.FOLDER,
name: 'Subfolder 1.1.2',
children: [{
name: 'Book 8'
}]
}]
}, {
type: Tree.FOLDER,
name: 'Subfolder 1.2',
children: [{
name: 'Book 6'
}]
}]
}, {
type: Tree.FOLDER,
name: 'Folder 2',
children: [{
name: 'Book 3'
}, {
name: 'Book 4'
}]
}]
}];
Tree Preview:
Answer
Solution:
Seems that I found what I was looking for.
The result of the function is a JSON that can be used to render a tree of folders. Probably there are better methods to generate the JSON tree, but this is good enough for me. (solved my problem)
INFO: The files 'tree.js' and 'tree.css' are from 'Simple Folder Tree With JSON And JavaScript'
<?php
$Conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
//Escape user data before SQL query
function e($input) {
global $Conn;
return mysqli_real_escape_string($Conn, $input);
}
function tree($current_parent) {
global $Conn;
$str="";
$folders = $Conn->query("SELECT `name` FROM `folders` WHERE `parent`='". e($current_parent) ."' ORDER BY `name` ASC;");
$folders_length = $folders->num_rows;
for($i=0; $i<$folders_length; $i++) {
$folders_row = $folders->fetch_assoc();
$str .= "{";
$str .= "type: Tree.FOLDER,";
$str .= "name: '". $folders_row['name'] ."',";
$str .= "children: [";
$str .= tree($folders_row['name']);
$str .= "]";
$str .= ($i != ($folders_length-1)) ? "}," : "}";
}
$books = $Conn->query("SELECT `name` FROM `books` WHERE `folder`='". e($current_parent) ."' ORDER BY `name` ASC;");
$books_length = $books->num_rows;
for($j=0; $j<$books_length; $j++) {
if(($i>0)&&($j==0)){
$str .= ",";
}
$books_row = $books->fetch_assoc();
$str .= "{";
$str .= "name: '". $books_row['name'] ."'";
$str .= ($j != ($books_length-1)) ? "}," : "}";
}
return $str;
}
?>
<LINK REL="stylesheet" HREF="html/css/tree.css">
<SCRIPT SRC="html/js/tree.js"></SCRIPT>
<BR>
<DIV ID="tree"></DIV>
<SCRIPT>
/* globals Tree */
'use strict';
var tree = new Tree(document.getElementById('tree'), {
navigate: true // allow navigate with ArrowUp and ArrowDown
});
var structure = <?php echo "[". tree("") ."]"; ?>;
tree.json(structure);
</SCRIPT>
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: mysqli::real_connect(): (hy000/2002): connection refused
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.