Last September I wrote a bespoke e-commerce solution for my wife’s website, including all the accompanying admin screens. One admin screen allows the amendment of the display order of items in a particular category. It was, if I’m honest, a bit clunky, as you had to type in the display order as a number, and then refresh the screen to see what the resultant re-order looked like. In response to repeated requests of “Can’t you make it drag and drop…?” I switched from responding with hollow laughter and actually had a look at how this might be done. And surprisingly, it was pretty straightforward. Here’s how
Setting up the database
I already had a database with a table called items, each of which had a unique ID field and another field called Display_Order. The images for each item are all named in the format [ID]-x.jpg to make life easier.
Displaying the items on the “Maintain Display Order” admin page
I started off just setting up the page to display all the items. Straightforward php, and pretty much what I already had. I’m retrieving the items from the database and displaying them in an unordered list, which I’ll use later for the drag and drop.
$result = mysql_query("SELECT * FROM items ORDER BY Display_Order ASC");
echo "<ul id='categoryorder'>";
while ($item = mysql_fetch_array($result))
{
echo "<li id='ID_".$item['ID']."'><img src='.$item['ID']."-1.jpg'
height='90' width='60' alt='No file on server' title='Preview'></li>";
}
echo "</ul>";
A couple of things to note here – I’ve assigned an id categoryorder to my list, and each item in the list has its html id set as “ID_” followed by the database ID. This is important later on, as we’ll see – don’t miss the underscores. Your resultant html should look like this:
<li id="ID_1">...</li> <li id="ID_2">...</li>
Then, I added a bit of css to make my list into two columns of images to match the real page layout.
Adding the drag and drop
I used the jQuery UI sortable plugin. You can download jQuery, jQuery UI base and the plugin from http://jqueryui.com/download. Having done this, I then need to add links to these javascript files in the head of my page
<script type="text/javascript" src="/jquery-1.4.2.min.js"></script><br> <script type="text/javascript" src="/jquery-ui-1.8.1.custom.min.js"></script>
In order to make my list items draggable then I need to add just the following code:
<script type="text/javascript">
$(document).ready(function(){
$("ul#categoryorder").sortable({
opacity: 0.6,
cursor: 'move'
});
});
</script>
All I’m doing is invoking the “sortable” plugin when the page loads and assigning it to items in my #categoryorder list. I’m also setting the opacity of the dragged item to be 0.6, so it’s a bit faded when it’s actually being dragged. Finally, I’m setting the cursor to change when I’m dragging an item. And that’s it for the draggable bit – my items can now be dragged and dropped into whatever new order I want.
Giving a visible indication that the order has changed
Once I’ve changed the order, it’d be nice to display a message to say that the are changes that haven’t yet been saved, and I can do this by using the update Event on sortable:
<script type="text/javascript">
$(document).ready(function(){
$("ul#categoryorder").sortable({
opacity: 0.6,
cursor: 'move'<span>,
update: function(){
$('#categorysavemessage').html('Changes not saved');
$('#categorysavemessage').css("color","red");
}</span>
});
});
</script>
Here I’m using the update option to populate a predefined message field (#categorysavemessage) with some red text to say “Changes not saved” whenever the sort order is changed.
Updating the database
I don’t want to update the database every time I move an item, just at the end when I’m happy, so I’ve added a “Save now” button. I now need to create a javascript function to fire when that button is clicked, and here’s the javascript I need:
function saveDisplayChanges()
{
var order = $("ul#categoryorder").sortable("serialize");
$('#categorysavemessage').html('Saving changes..');
$.post("update_displayorder.php",order,function(theResponse){
$("#categorysavemessage").html(theResponse);
$('#categorysavemessage').css("color","green");
});
}
</script>
Here I’m first populating a variable called order with the “new” order of my items, using the “serialize” method of the sortable plugin. This looks at the html id of each list item which refers to the ID in my database, and builds a query string. I’m then passing the resultant string via AJAX to a separate php file called update_displayorder.php which updates the database. Here I’m using the jQuery form of AJAX call. Finally, when I get a message back from the php file I’m updating the message field.
Here’s the php code which will actually update the database:
$newOrder = $_POST['ID'];
$counter = 1;
foreach ($newOrder as $recordIDValue) {
$query = "UPDATE items SET Display_Order = " . $counter;
$query .= "WHERE ID = " . (int)$recordIDValue;
mysql_query($query) or die('Error, insert query failed');
$counter ++;
}
echo 'Changes saved';
So here I’m just extracting the data from the post, and then stepping through each item, updating the display order for each one with a variable which I’m incrementing for each item.
And that’s it – it works, with very little code, thanks to the jQuery UI plugin. Nice. I’ve tested in IE6, IE7, IE8, Chrome, FF3, Opera 9, Opera 10 and Safari 4, all on Win/XP, and Safari 5 on Mac OS X/Snow Leopard.
Here’s a little demo – it doesn’t talk to a real database, but you can see the drag and drop and the resultant code that would be passed back to drive the update.