Sortables with Scriptaculous, PHP, and MySQL in 6 Easy Steps

View the Demonstration

Step 0: Include your libraries

For this demo I'm using scriptaculous and prototype. I also recommend Behaviour. Yeah, it's like 200+ kb of javascript, but I'm guessing you'll want to use these libraries in other parts of your app. They'll save you a ton of time.

In your <head> tag...

<script type="text/javascript" src="/js/prototype/prototype.js"></script>
<script type="text/javascript" src="/js/scriptaculous/scriptaculous.js"></script>

Step 1: Making your list

A scriptaculous sortable rule: each list item in a sortable needs to be in the format of "name"_#.

Let's say we have a MySQL table with the following schema:

create table items(item_id int NOT NULL AUTO_INCREMENT, 
	 	item_order int, item_name varchar (255), 
		PRIMARY KEY (item_id)); 

Let's populate the table with data:

insert into items(item_order,item_name) values ('1','Item 1');
insert into items(item_order,item_name) values ('2','Item 2');
insert into items(item_order,item_name) values ('3','Item 3');
insert into items(item_order,item_name) values ('4','Item 4');

Here's the data in the table...

mysql> select * from items;
+---------+------------+-----------+
| item_id | item_order | item_name |
+---------+------------+-----------+
|       1 |          1 | Item 1    |
|       2 |          2 | Item 2    |
|       3 |          3 | Item 3    |
|       4 |          4 | Item 4    |
+---------+------------+-----------+
4 rows in set (0.00 sec)

And here's the PHP we'll use to generate the list's HTML:

<?php
mysql_connect($host, $user, $pass);
mysql_select_db($db);
$sql = "SELECT * from items ORDER BY item_order";
$result = mysql_query($sql);
echo "<ul id=\"mylist\">\n";
while($data = mysql_fetch_assoc($result)) {
	echo "<li id=\"item_" . $data['item_id'] . "\">" . $data['item_name'] . "</li>\n";
}
echo "</ul>";
?>

Here's the final output of the HTML:


<ul id="mylist">
<li id="item_1" >Item 1</li>
<li id="item_2" >Item 2</li>
<li id="item_3" >Item 3</li>
<li id="item_4" >Item 4</li>
</ul>

Step 2: Make your list sortable, via scriptaculous

This code snippet tells scriptaculous to make the item with an id "mylist" to be sortable when the page has loaded.

Note the "onUpdate: updateList". This means we're going to call the Javascript function updateList() when the list's contents have changed.

window.onload = siteInitialize; 
function siteInitialize() {
	/* other stuff */
	Sortable.create('mylist', {onUpdate:updateList});
}

Step 3: Use AJAX to handle the sorting of the list

Now we need to send a request to a PHP file showing how the navigation has changed. As I mentioned earlier, when the list changes Javascript's updateList() is called.

function updateList() {
	var url = ""; // url to update_navigation.php
	var sorted = escape(Sortable.sequence('mylist'));
	var updateNavigation = new Ajax.Request(
			url,
			{
				method: 'get',
				parameters: "sort_order=" + sorted,
				onComplete: showUpdate

			});
	return true;
}

updateList() will send a GET request to url with sorted as the sort_order parameter. It may look like this: http://localhost/update_navigation.php?sort_order=1,4,3,2. Once the GET request is handled, the Javascript function showUpdate() is called.

Step 4: Use PHP to update the database and send a response back to our document

update_navigation.php takes our GET parameters churns through them and updates the MySQL item_order field in items.

<?php
/* update_navigation.php */
mysql_connect($host, $user, $pass);
mysql_select_db($db);
$item_order = 1;
$order_array = explode(",", urldecode($_GET['sort_order']));
foreach($order_array as $k=> $order) {
	$sql = "UPDATE items SET item_order = $item_order WHERE item_id = '" . mysql_real_escape_string($order) . "'";
	mysql_query($sql);
	$item_order++;
}

$output = "Item order has been updated.";
echo $output;
?>

Step 5: Handling the response with JSON

After update_navigation.php is hit, it sends a response back to the showUpdate function in Javascript. Here's what showUpdate() would look like:

function showUpdate(originalRequest) {
	alert(originalRequest.responseText);
}

Check the source

sort-php.zip contains update_navigation.php and sort-index.php

showUpdate will alert "Item order has been updated.". You can reload the page and see that the new order has been saved. Magic!

Creative Commons License
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.

Back to Andy Hill's homepage