Drag and drop with jQuery UI Sortable
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.
Tags: jQuery
Ahmet
This demo works fine with jQuery 3.1.1 and jQuery UI 1.12.1. Are you sure you’re using compatible versions of jQuery and jQuery UI?
Hi Simon, thanks a lot for this useful work. It works without any problem with the old version of jquery.min.js but doesn’t work with the new version. How can we use it with the updated version of jquery.min.js?
nice piece of coding Simon and just what I was looking for. Thanks.
Something is wrong if your ID parameter is not being passed. Check the console window to see what is being posted by your code.
Yup, I followed exactly but it doesn’t update database.
`Notice: Undefined index: ID in C:\xampp\htdocs\FYP\update_displayorder.php`
Do you have the ID correctly in your html as described above? Check the console to see what data is being posted via ajax.
Hi Simon, my update_displayorder doesn’t seem to recognize the id from the previous page..
That shouldn’t be too difficult. You just need to amend the PHP code that’s called on save, and possibly amend the function
saveDisplayChanges()to suit your database.Very detailed, thank you 🙂
Can you guide how to save from one list to another using database,
https://jqueryui.com/sortable/#empty-lists
I am using above empty sortable list, with only two columns one has data from database other one is empty. Once I have selected , dropped and sorted my list from one list to empty, I would like to save the changes of both list on to the database.
Thanks.
Super helpful. Thanks so much for keeping this post alive. Saved my bacons!!!
This was really helpful, I was looking for a tutorial like this for a project I hope to start.. Thank u very much for sharing this.
Not quite sure what you’re asking here. The items in your sortable list should have their html id set to ID_1,ID_2 etc, as in my example. I’m then using sortable to serialize this and post it as an array called ID. It looks like this is not happening in your setup, which is why you’re getting the error on foreach().
Hi , Sorry i have problem . My code error . I don’t know $newOrder = $_POST[‘ID’]; . Where are ID ? And my Error Code Warning: Invalid argument supplied for foreach() in C:\AppServ\www\htmltutorial\mos_admin\update_displayorder.php on line 5
Thanks for this code tutorial, very helpfull even it’s an old post.
bye
I’m using the serialize method of .sortable to create the post data which does all this for you.
See here: http://api.jqueryui.com/sortable/#method-serialize
I don’t understand where the ID post value is passed – shouldn’t there be an explode or something like that to split the ID away from the underscore?
Sharon
The code for the update is just the code published above – my demo doesn’t actually update a database. If your script isn’t working properly you can debug it by using echo in the PHP script to display results and errors, and then view this as the response to the ajax call in your browser console (e.g Firebug, Chrome Dev Tools etc).
Dear Simon,
thank you so much for your script and explanation. I think i almost got it to work, but i think i have some errors in my submit script, would you be able to post your update_displayorder.php script as your have it now working on your demo? That would really be of great help, thank you!
Joe
I’m not really sure what you mean I’m afraid. Happy to try and help if you want to explain a little more.
I was wondering if instead of setting the order by using the code that you have with the while look that I could set the order afterwords? Similar to the user rearranging the page but calling it after the page is loaded?
Awesome stuff! I spent a long time trying to get this feature to work in my application, but it was not working. Your demonstration and code works perfectly for me. Thank you very much!
Thanks Simon, it’s very useful to have a straightforward working example.
FYI to anyone reading this, don’t copy the SQL verbtim – you should really use parameterised SQL, or at very least use intval() on $recordIDValue:
e.g. $query .= “WHERE ID = ” . intval($recordIDValue);
Otherwise a malicious or malformed POST request could corrupt/delete the database (and/or access things you’d rather were private).
Thanks! Very useful!
Hi Colin
Yes, that’s right.
Very good explanation.
Should the message box have the id categorysavemessage?
Very uselful! Thanks for the practical explanations.
I guess you could do it with buttons but that’s a complete rewrite. If your problem is the button being clickable, per your earlier post, then another option would be to run the “save to database” option automatically once the drag is completed using the UI sortable
.update.I think it’s just the stupid iPhone, myself.
Would it be possible to add arrow buttons (at least up and down) to the divs to allow reordering by clicking buttons instead of dragging?
Ah, right, that sounds like there’s another element on top of the button then, possibly arising from however the dragging is handled on a touch screen. If you’re building your own page, can you position the button differently?
Yeah. There are specific JavaScript iOS touch handlers, and once enabled this does work. However, I lose the ability to submit the order of div elements because the button won’t press anymore.
jQuery UI doesn’t support touch interfaces, I think.
Argh!! Doesn’t work on mobile touch interfaces. =(
Still, pretty sweet. =)
Jens
Thanks for your comment. I have pretty much posted my entire code. There is no form, the submit.php is just called from a click event attached to a button. You can view source on the demo page to see any other details.
You should post the entire code. I am (for instance) unsure how you set up the Form to send to the submit.php using ajax. cool post though.