WGOTim Roberts CakePHP, MySQL and jQuery Developer

This recipe follows on from the work done in my Scriptaculous Drag and Drop recipe, only using Mootools an alternative Javascript library that I have been using lately. I am not endorsing either library and I suggest you read both tutorials and see which one feels most comfortable for you, or choose depending on the library you currently use.

This recipe also uses PHP and MySQL for the server side magic. However, it is abstract enough for you to substitute the PHP / MySQL aspects with your scripting language and data storage combination of choice.

It´s going to be straight to the point, so if you do have any issues the best thing to do is post them here and you usually get an answer, even if it’s not from me. Right enough preamble – let’s go:

Step one – Get Mootools and include it in the head of our (X)HTML document

Download Mootools by building your own custom version. I normally just select everything because I love playing with the features, but the base stuff you need to select are Ajax from the Remote options and Sortables from the Plugins options – some other dependencies will be automatically added by the download tool. I also select JavaScript Packer compression as we don´t need to be messing with the Mootools codebase.

Now include the Mootools library in the head of your document like so:

<script type="text/javascript" src="path/to/mootools.js"></script>

Step two: Build your list of items and the sortable script

Use your scripting language of choice to build your list of items direct from your data source. Output this in an unordered list with an id of “item_list” and then give each list item an id of “item_#” where # is substituted by the primary key of that item brought back from the database.

The two important fields that your table should contain are an “id” which should be an INT set as PRIMARY KEY and “position” an INT field that will hold the current order of the item

Your list should look something like this in structure:

<ul id=”item_list”>
<li id=”item_1″>Item One</li>
<li id=”item_2″>Item Two</li>
<li id=”item_3″>Item Three</li>
<li id=”item_4″>Item Four</li>
<li id=”item_5″>Item Five</li>
</ul>

So far this has been similar to the Scriptaculous version
of this recipe however now things change a little.

Include the following chunk of JavaScript after your unordered list (the puritans amongst you may wish to push it on the list using Mootools DOMReady function):

<script type="text/javascript">
new Sortables($('item_list'), {
onComplete: function() {
new Ajax("reorder.php?order="+this.serialize(function(el) {
return el.id.replace("item_","");
})).request();
}
});
</script>

The above function creates a new “Sortables” item out of our “item_list”. The oncomplete option tells our application to send an Ajax request to reorder.php with a GET variable containing the current order of items in comma separated format.

Step three: The reorder.php script

This is the easy bit. The following script can be written in the server side language of your choice to write the new position to the database. This is the script that is called whenever our item list order changes, by the science of Ajax. This example is in PHP – please adapt it to suit your needs:


// put your DB connect stuff here

foreach(explode(“,”,$_GET['order']) as $key=>$value) {
mysql_query(“UPDATE sortables SET position=’” . $i . “‘ WHERE id = ‘” . $value . “‘”);
$i++;
}

What this does is create an array from the GET variable we passed to it and use that to update the position of every list item.

And that’s it!

Troubleshooting for the impatient

Of course things can go wrong, and if they do please check the following points, check any comments on this page and then post your own if you are still without a solution:

• Check you have included the Mootools library correctly;
• Ensure that your unordered list is correctly formatted with id attributes
• Be certain to include the Sortables function after your list
• Check that your database connection paramters are correct (call the script reorder.php directly to make sure there are no errors)

This entry was posted on Wednesday, October 10th, 2007 at 7:58 pm and is filed under Personal. You can follow any comments to this entry through the RSS 2.0 feed. You can leave a comment, or trackback from your own site.