PHP Treeview Example using data from MySQL Database

PHP Treeview Example using data from MySQL Database

To present bulk of data with parent child relationship Treeview is a classical approach. The major advantage of Treeview is using a Treeview we can show more data in less space. Assume that you have a global recruitment portal. You want to display job opportunities depending upon Countries & their Cities. In this case you required Treeview. Using a Treeview easily you can display Countries & related Cities. In this session let us share codes for a PHP Treeview using data from MySQL Database. In front-end using PHP I am binding data to ol li element of HTML. Then by applying CSS giving expand & collapse effects to the Treeview. Let us explain this PHP Treeview Example Step by Step.

1. Create a MySQL table “tab_treeview”. Consider id as the Primary key. To store Treeview node details Create 2 other Columns name and title. Then to establish parent child relationship create one more column “parent_id”. This will store reference of parent node. If parent_id is zero then that record is a parent node.

Query to Create table tab_treeview

CREATE TABLE IF NOT EXISTS tab_treeview (
id int(12) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
title varchar(255) NOT NULL,
parent_id varchar(12) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7;

2. To start with insert some sample records to “tab_treeview” table. While inserting sample records stay careful about parent_id. A wrong parent_id can change the Tree structure.

Query to Insert sample Records

INSERT INTO tab_treeview (id, name, title, parent_id) VALUES
(1, 'Mumbai', 'The Film City', '3'),
(2, 'New Delhi', 'Capital of India', '3'),
(3, 'India', 'Country', '0'),
(4, 'United States', 'Country', '0'),
(5, 'Washington', 'Popular City of US', '4'),
(6, 'New York', 'Popular City of US', '4'),
(7, 'Olympia', 'Capital of Washington', '5');

PHP Treeview Example (Binding Data from Database)

3. Create a blank HTML page. Then in body inside PHP tag Copy n Paste the below recursive function “buildTree()”. Keep notice this is the function which generates Treeview html.

buildTree() Recursive function

function buildTree($array, $currentParent, $currLevel = 0, $prevLevel = -1) {
foreach ($array as $categoryId => $category) {
if ($currentParent == $category['parent_id']) {
if ($currLevel > $prevLevel) echo "<ol id='menutree'>"; 
if ($currLevel == $prevLevel) echo "</li>";
echo '<li> <label class="menu_label" for='.$categoryId.'>'.$category['name'].'</label><input type="checkbox" id='.$categoryId.' />';
if ($currLevel > $prevLevel) { $prevLevel = $currLevel; }
$currLevel++; 
buildTree ($array, $categoryId, $currLevel, $prevLevel);
$currLevel--;   
}
}
if ($currLevel == $prevLevel) echo "</li> </ol>";
}

4. Below the “buildTree()” function Copy n Paste the below PHP codes. Here using this block I am fetching data from tab_treeview table.

Fetching data from MySQL Database

/*Connecting to Database tempdb*/
mysql_connect('localhost', 'root');
mysql_select_db('tempdb');

/*Executing the select query to fetch data from table tab_treeview*/
$sqlqry="SELECT * FROM tab_treeview";
$result=mysql_query($sqlqry);

/*Defining an array*/
$arrayCountry = array();

while($row = mysql_fetch_assoc($result)){ 
$arrayCountry[$row['id']] = array("parent_id" => $row['parent_id'], "name" => $row['name']);
}

/*Checking is there any records in $result array*/
if(mysql_num_rows($result)!=0)
{
/*Calling the recursive function*/
buildTree($arrayCountry, 0);
}

5. Now you can able to watch Treeview data in your html page. Apply the below CSS styles to implement expand and collapse effects. Add a style tag in html file head section and copy the below CSS styles. To customize this dynamic Treeview including the below classes you can add your additional CSS Classes.

CSS Styles

#menutree li { list-style: none; }
li .menu_label + input[type=checkbox] { opacity: 0; }
li .menu_label { cursor: pointer; }
li .menu_label + input[type=checkbox] + ol > li { display: none; }
li .menu_label + input[type=checkbox]:checked + ol > li { display: block; }