How to Create Dynamic Treeview Menu using PHP, MySQL and AJAX? Most modern websites use tree view to display the dynamic sidebar menu for easy navigation. In case you don't know, a Treeview is a hierarchical representation of elements in a tree-like structure. You can go for jquery solution in this context, but I would recommend 'Bootstrap Treeview' plug-in if you use the bootstrap framework to build your websites.
The plug-in uses JSON dataset to create a hierarchical tree structure. I have already discussed the creation of static treeview menu using bootstrap treeview. But you can also generate a dynamic tree where you pull off the data elements stored in the database. In this tutorial I'm going to show you about creating dynamic treeview using php, mysql, ajax and bootstrap.
How to Create Dymanic Treeview in PHP & MySQL?
For the demo, I'm going to load all the required libraries via CDN. So there's no need to download them to your web server.
Here are the simple steps to build a dynamic tree view structure.
STEP-1) First create the mysql database required for the example. I would suggest you follow the same schema given below to maintain the hierarchy structure.
CREATE DATABASE `my_demo`;
USE `my_demo`;
CREATE TABLE `tbl_categories` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`item_name` varchar(50) NOT NULL,
`parent_id` int(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=18;
INSERT INTO `tbl_categories` (`id`, `item_name`, `parent_id`) VALUES
(1, 'Electronics', 0),
(2, 'Televisions', 1),
(3, 'Tube', 2),
(4, 'LCD', 2),
(5, 'Plasma', 2),
(6, 'Computers and Laptops', 1),
(7, 'Desktops', 6),
(8, 'Laptops', 6),
(9, 'Netbooks', 6),
(10, 'Tablets', 6),
(11, 'Android', 10),
(12, 'iPad', 10),
(13, 'Mobile Phones', 1),
(14, 'Basic Cell Phones', 13),
(15, 'Smartphones', 13),
(16, 'Android Phones', 15),
(17, 'iPhone', 15);
STEP-2) Next create a php script to be executed by ajax call. This will fetch the menu data from the 'tbl_categories' table, create hierarchical tree structure and return it as JSON data.
fetch_categories.php
<?php
$db = mysqli_connect('localhost', 'mysql_username', 'mysql_password', 'my_demo');
$sql = 'select id, item_name as name, item_name as text, parent_id from tbl_categories';
$result = mysqli_query($db, $sql);
$tree_data = mysqli_fetch_all($result, MYSQLI_ASSOC);
foreach($tree_data as $k => &$v){
$tmp_data[$v['id']] = &$v;
}
foreach($tree_data as $k => &$v){
if($v['parent_id'] && isset($tmp_data[$v['parent_id']])){
$tmp_data[$v['parent_id']]['nodes'][] = &$v;
}
}
foreach($tree_data as $k => &$v){
if($v['parent_id'] && isset($tmp_data[$v['parent_id']])){
unset($tree_data[$k]);
}
}
echo json_encode($tree_data);
?>
STEP-3) Finally, create an HTML file and add placeholder to show the tree view. Here you have to load all the required libraries such as bootstrap, jquery and bootstrap treeview libraries.
index.html
<!DOCTYPE html>
<html>
<head>
<title>PHP and MySQl Dynamic Treeview Example</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet" type="text/css" />
<link href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-treeview/1.2.0/bootstrap-treeview.min.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div class="container">
<div class="row">
<div class="col-sm-4">
<h3 class="text-center bg-primary">Dynamic Treeview Example</h3>
<div id="myTree"></div>
</div>
<div class="col-sm-8">
<!-- here goes other page contents -->
</div>
</div>
</div>
<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-treeview/1.2.0/bootstrap-treeview.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(document).ready(function(){
$.ajax({
url: 'fetch_categories.php',
method: 'GET',
dataType: 'json',
success: function(data){
$('#myTree').treeview({data: data});
}
});
});
</script>
</body>
</html>
In the success function of the ajax() call, we have invoked the treeview() method of the 'bootstrap-treeview' library to display the tree structure on the corresponding placeholder.
When you run 'index.html' a tree menu similar to this one will appear,
Similarly, you can create treeview dynamically using php, mysql and ajax. You can also add icons, check boxes and filters to the nodes of the tree menu. Check the documentation to learn more about it. I hope this tutorial is useful for you. Please share it on social media if you like it.
