Abstract

I could sort & save a nested list using mootools 1.1, but since 1.2 is out for about half a year now it is time for an update :) An extensive one this time. After working through this tutorial you are able to use MySQL to store a nested list, use PHP to retrieve the list (either as HTML list or as JSON object), sort it using Javascript and of course save it in your database.

Here we go:

Data

First, we make a little table in out MySQL database to store our menu. It’s quite simple:

CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) NOT NULL DEFAULT '0',
  `order` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY  (`id`)
)

Then we add some test data:

INSERT INTO `menu` VALUES (1, 0, 1, 'Item 1');
INSERT INTO `menu` VALUES (2, 1, 1, 'Item 2');
INSERT INTO `menu` VALUES (3, 1, 2, 'Item 3');
INSERT INTO `menu` VALUES (4, 1, 3, 'Item 4');
INSERT INTO `menu` VALUES (5, 0, 2, 'Item 5');
INSERT INTO `menu` VALUES (6, 0, 3, 'Item 6');
INSERT INTO `menu` VALUES (7, 0, 4, 'Item 7');
INSERT INTO `menu` VALUES (8, 0, 5, 'Item 8');

Logic

Next up, the logic to pull our menu out of the database. I use Crisp’s code, since it’s as easy as it can get, and added a function to generate the menu as JSON object. See the code here: data.phps. It basically creates a nested list from our menu table. I guess you can figure out how it works. Note: you need to have PHP5 to use the json_encode function.

Presentation

To turn the JSON-object into a sortable menu, I use moro’s Mif.Tree script. Works like a charm. To invoke the sortable tree, read the Mif.Tree documentation and use this javascript-code:

window.addEvent('domready',function(){
	tree = new Mif.Tree({
		container: $('tree_container'),
		forest: true,
		initialize: function(){
			new Mif.Tree.KeyNav(this);
			new Mif.Tree.Drag(this, {
				onDrag: function(){
					$('destination').innerHTML=this.target ? this.target.name : '';
					$('where').innerHTML=this.where;
				},
				onStart: function(){
					$('source').innerHTML=this.current.name;
				},
				onComplete: function(){
					$('destination').innerHTML='';
					$('where').innerHTML='';
					$('source').innerHTML='';
				}
			});
		},
		dfltType:'folder',
		height: 18,
		onCopy: function(from, to, where, copy){
			if(from.parent==copy.parent){
				copy.set({
					property: {
						name: 'copy '+from.name
					}
				});
			}
		}
	});
	tree.load({
		url: 'data.php?json=yesplease'
	});
});

See it in action at the first demo page. You now have a nested list you can actually sort. Next: saving the list and adding items.

More clientside logic

To save the list we’ll send a JSON object back to our webserver, and use PHP to process and save it in the database. To get a new JSON object you have to serialize the tree, and encode it. I added a serialize function to the Mif.Tree script:

serialize: function(items){
	var serial = [];
	if (!items) items = this.root.getChildren();
	items.each(function(el, i){
		serial[i] = {
			id: el.id,
			children: (el.getChildren()) ? tree.serialize(el.getChildren()) : []
		};
	});
	return serial;
}

You can download the script including this function here. Now we can add the code to serialize the list and send it using an ajax request. Something like this:

var request = function(e) {
	new Event(e).stop();
 
	var theDump = tree.serialize();
	var req = new Request({
			method: 'post',
			url: "save.php",
			onSuccess: 	function(html) {
				$('debug').empty().set('text',html);
			},
			onComplete: function() {
				tree.reload({
					url: 'data.php?json=yesplease'
				});
			}
	}).send('m='+JSON.encode(theDump));
};
$('save').addEvent('click', request);

Although not really neccesary, I think it’s a good idea to reload the tree after saving it. That way, you know for sure your changes are correctly saved.

We now have a nested sortable list, and a way to send the newly ordered list back to our webserver. All we need is some PHP logic to save it.

More serverside logic

I wrote a small function to save the incoming data:

function saveList($parent, $children) {
 
	$parent = (int) $parent;
	$result = array();
	foreach ($children as $k => $v) {
 
		$id = (int) substr($children[$k]->id, 4);
		$sql = 'UPDATE menu SET `parentId` = '.$parent.', `order` = '.$k.' WHERE `id` = '.$id."\n";
		mysql_query($sql) or die(mysql_error());
 
		if (mysql_affected_rows() > 0) {
			echo "\n". $sql.mysql_info();
		}
 
		if (isset($v->children[0])) {saveList($id, $v->children);}
	}
}
// use it like this:
if (isset($_POST['m']) ) {
	$aMenu = (array) json_decode(stripslashes($_POST['m']));
	saveList(0, $aMenu);
	die();
}

See it in action over here. Please note that you should always make sure you’re not vulnarable for SQL injections.

Adding items

Out last step today is to add items to the menu. We use an html-form, a bit javascript to send it using Ajax and a bit PHP to process it.

The javascript:

$('new-item').addEvent('submit', function(e) {
 
	new Event(e).stop();
	var url = this.getProperty('action');
	var method = this.getProperty('method');
 
	new Request.HTML ({
		url: url,
		method: method,
		update: $('debug'),
		onComplete: function() {
			$('new-item').reset();
			tree.reload({
				url: 'data.php?json=yesplease'
			});
		}
	}).post(this);
});

The PHP:

function addItem($title) {
 
	$title = mysql_real_escape_string($title);
 
	if ($uri != '' && $title != '') {
		$sql = "INSERT INTO menu SET `parentId` = 0, `order` = 0, name='".$title."'";
		$result = mysql_query($sql) or die(mysql_error());
 
		if (isset($result)) {
 
			echo "Item '".$title ."' succesfully added.";
		} else {
 
			echo "\n\n For some reason, item '".$title ."' is not added.";
		}
 
	} else {
		echo "Can not insert item without title."; exit();
	}
}
 
// use like this:
if (isset($_POST['addItem'])) {
 
	$_POST['name'] = (isset($_POST['name'])) ? $_POST['name'] : '';
	addItem($_POST['name']);
	die();
}

You can download the PHP script to save your list here: save.phps. Check the complete script at the last demo page. If the list changes on a page refresh, someone else is probably sorting it ;) .

Update

For your convenience and by request of Sébastien I made a ready to run archive. Click here to download. Please read the 4-steps readme.txt for instructions.