mdgArt

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

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

In this second part of this article (this is the first part) i’m going to show you how to retreive and serialize the tree structure using jQuery and sent the serialized data to a php script (using ajax) that saves it on the database. In this way, we can have the persistence of any change made on the tree.

Let’s start with the serializzation problem, that is simply this: how can i retreive the structure of the tree in a form that can be sent to a php script via POST or GET? To do that i have to read every single branch of the tree and save, for every single category, three values: category_id, parent_id and orderby:

0:1:1|0:2:2|2:3:1|2:5:2|2:4:3|0:6:3|6:7:1|

The group category_id, parent_id and orderby are separated by a pipe. To do that we need a function that reads every single branch of the tree and it’s relative information. The function will call a php file to save the serialized in the mysql database.
This is the jQuery function that perform this operation:

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

function(){	
	var serialStr = "";
	var order = "";
 
	$("ul.simpleTree li span").each(function(){			
		parentId = $(this).parent("li").parent("ul").parent("li").attr("id");
		if (parentId == undefined) parentId = "root";
		order = (($(this).parent("li").prevAll("li").size()+1))/2; 
		if ( parentId != "root") serialStr += ""+parentId+":"+$(this).parent("li").attr("id")+":"+order+"|";
	});
 
	$.ajax({
	   type: "POST",
	   url: "saveData.php",
	   data: "serialized="+serialStr,
	   success: function(msg){
	   	 $("#serializedList").html(msg);
	   }
	 });
 
	return false;
 
}

Let me explain a bit how the jQuery function work: it loops through each span of the tree, read the current id of the li and the id of the parent li, and finally it calculates the position of the current element to use as orderby value in the category table.
When the string is created, i passed to a php script with an ajax call. The php file that saves the serialized data on the server is this:

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

include_once("connection.php"); // the connection to the database
if ($_POST['serialized']) {
 
	// create an array from the string $_POST['serialized'] 
	// with the group category_id:parent_id:orderby for each array member 
	// i strip the last pipe, so i will not have an array with a empty member at the end
	$categories = explode("|", substr($_POST['serialized'],0,-1));
	$error =  false;
 
 	// loop through each array member
	foreach ($categories as $category) {
		// split the group by the colon and put the three value is separated variable
 		list($parentId, $categoryId, $order) = split(":", $category);
		// update the database
 
 		$result = mysql_query("UPDATE category SET parent_id = ".$parentId.", orderby = ".$order." 
 						    WHERE category_id = ".$categoryId ) or die(mysql_error());
 
 	}	
 
	echo "Tree updated.";
 
}
echo "<br />".$_POST['serialized'];

The php is straight forward and i commented every step. The output of the php script is output inside a div called serializedList, so we can see the result of the operation. At the end of the script, i print also the serialized string to debug and test purpose.

You can see a working example of the tree at this page.
As you can see, the jQuery function is fired every time a branch is moved, thanks to the afterMove: callback of simpleTree.

In my real word applications, to create a new category and delete a category i use 2 small buttons, while to update a category i use the double click callback functionality of simpleTree. This is a how it looks: persistent tree final result.
If you are interested to know how i do it please let me know, i will be glad to help you

You can download all the scripts of this article here:

Download persistentTree.zip

Please leave a comment if you like this article, thanks!

That’s all folks!