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>
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>
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});
}
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.
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;
?>
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);
}
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! Questions? Comments? Enter them here...