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 and their Cities. In this case you required a Treeview. Using a Treeview easily you can display Countries & related Cities hirarchically. In this session let us share sample 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 styles giving expand and 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. During an user enter a record to this SQL table I need the primary field entry need to jump 1 after 1 automatically. Thats why here I declared id filed with “auto_increment” property. To store Treeview node details Create 2 other Columns name and title. These fields are with datatype varchar(255). While inserting records keep noted these fields will accept only 255 charecters. You can’t entry a null value to these fields as NOT NULL attributes specifies. Then to establish parent child relationship created one more column “parent_id”. This will store reference of parent node. If parent_id is zero then that record is itself 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 first you need to insert some sample records to “tab_treeview” SQL table. While inserting sample records stay careful about parent_id. A wrong parent_id can change your Treeview structure and nodes.

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'),
(8, 'Russia', 'Country', '0'),
(9, 'Moscow', 'Popular City of Russia', '8'),
(10, 'Saint Petersburg', 'Popular City of Russia', '8')
(11, 'Bihar', 'State of India', '3'),
(12, 'Uttar Pradesh', 'State of India', '3'),
(13, 'Himachal Pradesh', 'State of India', '3');

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. In this function using a foreach loop I am dealing each record from the database. Then dynamically using HTML5 ol li and echo function from PHP writing the ordered list as a treeview.

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

Using the recursive function buildTree() inside a foreach loop over $array length I am building each li element of ordered list. To position the cursor for next item here I am using $currLevel++ increment operator. Here orderlist id is “menutree”.

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

As natural first using mysql_connect I am pointing my PHP Compiler to localhost mysql database as the user root. In next step using mysql_select_db pre-defiend php function I am selecting treeview specific database from MySQL server. Now we need to execute the sql query “SELECT * FROM tab_treeview” to fetch data from db. To do that using mysql_query and storing all records to an array $arrayCountry.

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

Finally on array of data using while loop to build the tree. Inside while loop after checking data persist for the existing row calling the recursive function buildTree($arrayCountry, 0);. Where the first parameter is the $arrayCountry. The function buildTree() accepts 4 parameters. Using this you can customize your treeview as needed.

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 for PHP Treeview

To remove default bulleted list from my ordered list items here I am using list-style: none; for my menu menutree. On clickable node of tree to my the cursor with hand symbol here I am using cursor: pointer for all menu labels.

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