9 October 2010

An easy example of recursion. PHP treeview can be used to create hierarchical lists like menus including sub-menus, category trees, taxonomy trees or site maps. This tutorial shows you how to generate treeview in PHP using data from MySQL database. The treeview will be generated by recursion. We are using flat arrays for this, that will be fetched from sql database.

The concept of this is actually simple. We have 3 columns in our database id, parent_id and title. Recursive function goes through all of the rows and place them in right order. The result should be something like lists in list, of course you can design it or make it collapsible. You can use this technique to create menus, categories or anything else that has unknown number of subs.

Create MySQL Table:

The SQL table has 3 columns id(this is automatic; unique identifier), parent_id(id of parent for the child, null if it's main) and title.
Source code viewer
  1. CREATE TABLE IF NOT EXISTS `treeview_items` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `parent_id` int(11) DEFAULT NULL,
  4.   `title` varchar(50) NOT NULL,
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Programming Language: MySQL
Now we should insert some data. Main categories have to have parent_id with null values. Other categories have to have parent_id. Parent_id is the id of parent for the child.

Get Data From Database:

Since I use a framework, I use active record. But for this example we will get the data by using simplest PHP code for this example.
Source code viewer
  1. mysql_connect('host', 'mysql_username', "mysql_password");
  2. mysql_select_db('database name');
  3.  
  4. $result = mysql_query('SELECT * FROM treeview_items');
  5. $result = mysql_fetch_array($result, MYSQL_ASSOC);
  6.  
  7. // For avoiding some errors.
  8. if (mysql_num_rows($result) > 0) {
  9. 	// Start the list using ul.
  10. 	echo '<ul>';
  11. 	foreach ($result as $row) {
  12. 		// Print the item, you can also make links out of these.
  13. 		echo '<li>' . $row['title'] . '</li>';
  14. 		// Recursive function(made in next step) for getting all the subs by passing id of main item.
  15. 		get_children($row['id']);
  16. 	}
  17. 	// End the list.
  18. 	echo '</ul>';
  19. }
  20. // Some message, if the database is empty.
  21. else {
  22. 	echo 'No Items';
  23. }
  24. // Clear the memory.
  25. mysql_free_result($result);
Programming Language: PHP
This solution may vary. This example connects to mysql database and fetches the whole table.

Generate The Treeview:

This recursive function puts all the subs to our treeview.
Source code viewer
  1. function get_children($parent, $level = 1) {
  2. 	$result = mysql_query('SELECT * FROM treeview_items WHERE parent_id = '.(int)$parent);
  3. 	$result = mysql_fetch_array($result, MYSQL_ASSOC);
  4. 	// For avoiding some errors.
  5. 	if (mysql_num_rows($result) > 0) {
  6. 		// Start the list using ul html tag.
  7. 		echo '<ul>';
  8. 		foreach ($result as $row) {
  9. 			// Print an item, you can also make links out of these.
  10. 			echo '<li>'.$row['title'].'</li>';
  11. 			// This is similar to our last code, but
  12. 			// this function calls itself, so its recursive.
  13. 			get_children($row['id'], $level+1);
  14. 		}
  15. 		// Close the list.
  16. 		echo '</ul>';
  17. 	}
  18. }
Programming Language: PHP
To keep the menus ordered by titles then you simply use the order by function in your query.
Theming this is easy. You can use the levels to generate different kinds of styles etc. This is really a question of imagination.

I suggest using caching for this kind of generation. Bigger lists will thousands times faster if loaded from cache. I hope I didn't make any typos while writing the codes.