mdgArt

Persistent drag and drop tree with jQuery, PHP and MySQL – part 1

Posted on the March 15th, 2009 under Javascript, MySQL, PHP, Programming, jQuery by Mauro

I’m developing a website that have a catalog with nested categories; this categories are also ordered and with a not predefined depth levels. I needed to find a way to manage with category in a fast and intuitive way for the user, so i decided to use a directory like tree allowing the users to drag and drop the categories to change the order and even the position on the tree: of course i use jQuery to do this, and PHP/MySQL to save the structure of the tree in a database.
I found a nice tree jQuery plugin on the web, that i used as a starting point for my project: http://news.kg/wp-content/uploads/tree/. The plugin work well on all browser i tested (FF3, Safari 3, IE6, IE7). It provide the drag and drop funcionality and some interesting callback, like afterClick and afterDblClick.
But let’s start from the beginning. First of all the structure if the categories table is the follow:

• category_id – the id of the category
• parent_id – the id of the parent category (or 0 if is a main category)
• category_label - the name of the category
• order – the order of the category. This order is local to the branch (more later…).

This information are enough to build a ordered tree like this:

Example of a categories tree, click to enlarge

As you can see every branch have is own progressive counter number, to indicate the order of the category inside the branch.
The tree will be rendered in a unordered list like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

<ul class="simpleTree">
 <li id="0" class="root"><span>Categories</span>
  <ul>
   <li id="1"><span>Category one</li>
   <li id="2"><span>Category two</span>
    <ul>
     <li id="3"><span>Category three</span></li>
     <li id="4"><span>Category four</span>
      <ul>
       <li id="5"><span>Category five</span></li>  
      </ul>
    </li>
   </ul>
   <li id="6"><span>Category six</span>
    <ul>
     <li id="7"><span>Category seven</span></li>
    </ul>
   </li>
  </ul>
 </li>
</ul>

With jQuery and simple tree correctly included in the page, you should see someting like this.

As you can see i already added a class simpleTree to the first ul and a main category with at the beginning with a class root, that wrap the whole tree of mine categories. I do that because i’m going to use the simpleTree plugin to manage the drag and drop. Please refer to the plugin documentation for further information.

The database

Let’s create the database table, that is very simple:

1
2
3
4
5
6
7

CREATE TABLE `category` (
  `category_id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT '0',
  `category_label` varchar(100) DEFAULT NULL,
  `orderby` int(11) DEFAULT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

for the initial tests i also prepopulated the category table with the follow data:

1
2
3
4
5
6
7

INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('1','0','category one','1');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('2','0','category two','2');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('3','2','category three','1');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('4','2','category four','2');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('5','4','category fine','1');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('6','0','category six','3');
INSERT INTO `category` (`category_id`,`parent_id`,`category_label`,`orderby`) VALUES ('7','6','category seven','1');

You can start with an empty database if you want, but i strongly reccomend that you to put some data inside the table to test the tree.

Retrieve the data and create the tree with PHP

To retrieve the data from the database i use this sql:

1

SELECT * FROM category ORDER BY parent_id, orderby

Notice the order by statement: what i have is a list of categories preordered by parent_id (so i have the 0 parent at the beginning) and also by orderby, so i will have every group of category that are related with the same parent ordered by orderby.

At this point i put the results of the query inside an array, so i can manage it inside the function that will create the tree. The complete code to retrieve the data and put it in an array is the follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql_connect("<b>host</b>", "<b>user</b>", "<b>password</b>") or die(mysql_error());
mysql_select_db("<b>database</b>") or die(mysql_error());
 
$rsCategories = mysql_query("SELECT * FROM category ORDER BY parent_id, orderby");
 
// create the empty array
$arrayCategories = array();
 
// put the results inside the array
// i use the category id as key for the array, and i store the parent_id 
// and the name of the category in a hash with the keys parent_id and name (of course)  
while($row = mysql_fetch_assoc($rsCategories)){ 
	$arrayCategories[$row['category_id']] = array("parent_id" => $row['parent_id'], "name" => $row['category_label']);	
}

The code is straightforward, read the comments if you dont understand how the array is created.

How to build the tree structure from the array

At this point i have to create the tree based on the information that i put inside the array.
Because I don’t know prior how may level I will have in the category tree (i dont know the deepest of the branches), to recreate the tree i use a recursive function, which is a function that call itself.
This is the bare naked function, that print the ordered list of categories, but without the html markup. Instead, i use asterisks to indicate the level of nesting.
The final function will render a complete unordered list.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// function to create a tree based on a unordered array
function createTreeT($array, $currentParent, $currLevel = -1) {
	foreach ($array as $categoryId => $category) {
		if ($currentParent == $category['parent_id']) {
 
			// print the asterisks based on the current level of nesting		
			for ($i=0;$i<$currLevel;$i++) echo "*";			
 
			echo $category['name']."<br />"; // print the category name
 
			$currLevel++;
		 	createTreeT ($array, $categoryId, $currLevel);
		 	$currLevel--;
		}	
	}
}
 
// create the tree starting from the root (parent_id = 0)
createTree($arrayCategories, 0);

If you used the data that i provided you sould have something like this:

category one
category two
*category three
*category four
**category fine
category six
*category seven

Let me explain how it works. As you can see the function accepts 3 arguments: the first is the array with the category that we build with the category in the database, the second is the parent_id of the current level of category, and the third is the current level of nesting. I know that this seems confusing now, i will try to make it more clear.

Inside the foreach statement (that simply extract all the category one by one form the array), the if compare the current parent value, that is initially 0, with the whole array again, the value of the parent_id of the category extracted: if the value match it print the category name (with eventually the asterisks in front of it) and then call itself with the value of the id of the current category as new $currentParent, and the current level increased by 1.

The new call start the foreach again, but with the new values, and search for the category that have the parent_id that match the category_id passed. If it finds something, the process start again, but with a new category_id and a new level of nesting.
If nothing is found the function exit, and the process is passed to the calling function, the current level is decreased and the next for each is processed.
I know, is not so simple to understand, but when you got it you have a very powerful thing in your hands. Inthis article, you can find a simple example of recursive function in python to calculate the factorial of a number.
Notice that you can retrieve only a branch of the tree by passing a different value as second parameter. For example when i calls the function like this:

createTree($arrayCategories, 2);

I will have the branches with the categories that have the parent_id set to 2, with all the subcategories. Cool!!

Now that we have (hopefully) understand how the function works, let’s see how to print the tree with all the necessary markups. The function is the same, but i introduced a new variable to keep track of the level of nesting of the calling function:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

function createTree($array, $currentParent, $currLevel = 0, $prevLevel = -1) {
 
	foreach ($array as $categoryId => $category) {
 
		if ($currentParent == $category['parent_id']) {						
 
			if ($currLevel > $prevLevel) echo " <ul> "; 
 
			if ($currLevel == $prevLevel) echo " </li> ";
 
			echo '<li id="'.$categoryId.'"><span>'.$category['name'].'</span>';
 
			if ($currLevel > $prevLevel) { $prevLevel = $currLevel; }
 
			$currLevel++; 
 
		 	createTree ($array, $categoryId, $currLevel, $prevLevel);
 
		 	$currLevel--;	 		 	
		}	
 
	}
 
	if ($currLevel == $prevLevel) echo " </li>  </ul> ";
 
}

Essentially, it uses the variables $prevLevel and $currLevel to know what to print in order to render a correct unordered list structure: if $currLevel > $prevLevel this means that we are at the beginning of a new level, so it prints a ul, that prints the category name with the open li element and assigns the same value to $prevLevel and $currLevel, because we are currently in the same level.
When the foreach ends, it prints the li and ul to close the structure correctly.

At this point we must call this function inside our html page:

1
2
3
4
5

<ul class="simpleTree">
	<li id="0" class="root"><span>Categories</span>
		<?php createTree($arrayCategories, 0); ?>
	</li>
</ul>

And you will have a a nicely formatted tree, similar to the one I showed you, but created with the category retrieved from the database.

In the next (and last) part of the article, i will show you how to save the state of the tree in the database when you drag and drop the category to reorder it. Of course, I will do it with jQuery!