Musings
Extending the jQuery Sortable With Ajax & MYSQL
3rd January 2009
I’ve had a lot of response to the jQuery sortable list post I wrote a while back, and a lot of them ask how to send the results to a database. I’ve often said the best thing to do would be to update it by AJAX, but I’ve never really explained how. So here it is!
- You can see the demo here
- And download the source code here
There was a bug a few people noticed with the new jQuery UI a short while ago that stopped the update callback working. That bug has since been rectified and all is kosher again. I have since updated the example to the latest jQuery and UI downloads. The examples and downloads have also been updated accordingly.
Step one: Create the page with the sortable, and the CSS
(described on my previous post) We have however altered it slightly, so here is the updated code (without any css, the css is in the sourcecode)
Include your javascript files, you’ll need jQuery and the jQuery UI files (sortable)
<script type="text/javascript" src="jquery-1.3.2.min.js"></script>
<script type="text/javascript" src="jquery-ui-1.7.1.custom.min.js"></script>
<link rel='stylesheet' href='styles.css' type='text/css' media='all' />Then you’ll need to create your Javascript function. We’re going to listen for the list to be updated, then call the php page, pass it the new order by GET and update the #info div with whatever the PHP file prints out:
<script type="text/javascript">
// When the document is ready set up our sortable with it's inherant function(s)
$(document).ready(function() {
$("#test-list").sortable({
handle : '.handle',
update : function () {
var order = $('#test-list').sortable('serialize');
$("#info").load("process-sortable.php?"+order);
}
});
});
</script>Now here’s the HTML to output the list etc. etc:
<pre>
<div id="info">Waiting for update</div>
</pre>
<ul id="test-list">
<li id="listItem_1">
<img src="arrow.png" alt="move" width="16" height="16" class="handle" />
<strong>Item 1 </strong>with a link to <a href="http://www.google.co.uk/" rel="nofollow">Google</a>
</li>
<li id="listItem_2">
<img src="arrow.png" alt="move" width="16" height="16" class="handle" />
<strong>Item 2</strong>
</li>
<li id="listItem_3">
<img src="arrow.png" alt="move" width="16" height="16" class="handle" />
<strong>Item 3</strong>
</li>
<li id="listItem_4">
<img src="arrow.png" alt="move" width="16" height="16" class="handle" />
<strong>Item 4</strong>
</li>
</ul>
<form action="process-sortable.php" method="post" name="sortables">
<input type="hidden" name="test-log" id="test-log" />
</form>Step two: Create the PHP to handle the serialized list jQuery has created
The PHP page will simply take the url it’s been handed and pick out the items from the array. You can then manipulate your database with the new values, or just format however you want. The code could look like this:
<?php
/* This is where you would inject your sql into the database
but we're just going to format it and send it back
*/
foreach ($_GET['listItem'] as $position => $item) :
$sql[] = "UPDATE `table` SET `position` = $position WHERE `id` = $item";
endforeach;
print_r ($sql);
?>You could then happily run each sql statement.
As always, I’d love the feedback, it’s great for improving the example etc. Enjoy!
Discuss
Get involved in the discussion by using the comment form below.
8th January 2009
You could update all rows using just one query which should save some overhead in long lists:
9th January 2009
Very nice @Karl, thanks!
31st January 2009
thanks for this. didn’t take too much searching to find and it’s exactly what i’m looking for.
1st February 2009
Nice article!
I’ve tried it with the following with no success:
jquery-1.3.1.min.js
jquery-ui-personalized-1.5.3.min.js
(UI Core, Draggable and Sortable)
It works fine with:
jquery-1.2.6.min.js
jquery-ui-personalized-1.6rc4.min.js
(as provided in the source code for this article)
Has anyone else tried?
1st February 2009
Thanks for the heads up @fabsanchez I’ll look into it and post an update shortly.
3rd February 2009
Great job! o/
3rd February 2009
@fabsanchez
I was having problems with it as well. I was using 1.3.1 from google’s library. When I switched to 1.2.6 (also on google’s library) it worked.
Both times I was using jquery UI 1.5.3
3rd February 2009
@fabsanchez & @Gathilas The problem seems to be that the UI isn’t compatible with the new version of jQuery yet. It will be tonight though apparently (2nd Feb 09.) See this post about it. So basically, my advice would be to hold off upgrading for a bit.
5th February 2009
@fabsanchez and @Wil.
This is absolutely awesome and just what I need - I love it.
Hoever I am having the same problem as fabsanchez and the following versions are not playing:
jquery-1.3.1.min.js
jquery-ui-personalized-1.5.3.min.js
(UI Core, Draggable and Sortable)
But seriously, this is my favourite tutorial for a long while.
10th February 2009
Dude, I’ve been searching for a simple script like this for aaageeees!!! Thank you so much! Much appreciated! m/
12th February 2009
Simple things like this make me happy….thanks!
15th February 2009
Thanks for posting this. I am new to jquery and this has helped me immensely.
In my case I prefer to have a button or link that will trigger the update to the database rather than instantly via every change. Here is the most simple case of trigging the update onclick:
$(document).ready(function() {
$(”#test-list”).sortable({
handle : ‘.handle’
});
$(”#update_order”).click(function () {
var order = $(’#test-list’).sortable(‘serialize’);
$(”#info”).load(“process-sortable.php?”+order);
});
});
15th February 2009
Thanks for that @Jade, that’s a useful tip.
25th February 2009
Thanks a lot. Very nice example.
25th February 2009
Hi im a little new to all this but learning every day.
Is it possible to order the list from jquery after the page has loaded?
so if an array was echoed out in javascript
var sortableorder = “item1&item3;&iten2;”;
then when the jquery loads it would reorder the sortables into that order automatically or would you just have to echo the array in php then order the sortables when you echo out the php? (to make my life easier i would love to know how to do it within jquery)
thanks for any advice
26th February 2009
You certainly could do it with jQuery, but it would make a lot more sense to do it with PHP - that way the list items are already in the order they were prescribed. So if you were retreiving the list items from a database the code might look like:
<ul id=“test-list”>
<?php
$sql = “SELECT * FROM t1 ORDER BY t1.position”;
$result = $db->query($sql);
$i=1;
while ($row = mysql_fetch_object($result)) : ?>
<li id=“listItem_<?= $i ?>”>
arrow.png
<?= $row->data ?>
</li>
<?php endwhile;
?>
</ul>
26th February 2009
Thanks for the reply 
i thought the php version would be the better way to do it. unfortunately this project im working on i could do with it being a jquery solution…. i’ll keep searching google
26th February 2009
You could do it with JavaScript, here’s an example how (pseudo code - whatever you do don’t copy and paste it, it’s just to give you an idea.)
// Reset the list (removes all list items)
$(”#test-list li”).remove();
// iterate the list we got from the sortable update
// (you don’t want to serialize this time though
for(i=0;i<order.length;i++) {
$(”#test-list”).append(’<li id=“listItem_’+i+’”>’+sortable[ i ].html+’</li>’);
}
Like I say - that’s not the code but it’s a suggestion for how you could re-write the list with javascript and not PHP - should really be PHP though 
17th March 2009
great!
23rd March 2009
I get a bug while using this script. After I call my php handler, the updated list is not shown ( I get a blank) at all.
Sometimes the order is not updated. Does anyone else get the same problem, or does anyone have a suggestion?
25th March 2009
If you have a look at the other comments, it’s a bug in the new jQuery release. I do really have to get to creating a workaround for this.
26th March 2009
Thanks a lot!
29th March 2009
Hello,
I want to get the following function: With the option ‘items: “li”’ I can do everything sortable. Also anything in unsorted lists.
But I will not list one by one into the other to move it. How can I prevent that?
The following example:
span>products
span>product 2
span>product 1
span>product3
span>home
There you don’t be move any item of any unsorted list to another unsorted list, because serialized does not appear which item is in which list and under what part of the parent element is located.
Has anyone an idea?
Thank you very much.
29th March 2009
@rvturnage I’m not sure I fully understand your question, but are you perhaps asking if you can have multiple lists and transfer from one sortable list to another (from parent to child and vice versa?)
30th March 2009
Hi Wil
Do you know how to make the serialization in revert order. I don’t want the list items to be entered into the database in ascending order but descending.
I hope you can help me
Greets
Rod
30th March 2009
@Rod You could always reverse the array in the PHP, so first sort the array based on the position then reverse it, then iterate through it…
<?php
$listItems = sort($_GET[‘listItem’]);
$listItems = array_reverse($listItems);
foreach ($listItems as $item) :
$sql[] = “UPDATE `table` SET `position` = $position WHERE `id` = $item”;
endforeach;
print_r ($sql);
?>
That should do it, let me know.
30th March 2009
Thank you for yyour superfast response!
I get somehow an error on array_reverse
Warning: array_reverse() [function.array-reverse]: The argument should be an array in C:xampphtdocswebjquery_versionprinterwebbackendetageprocess-sortable.php on line 11Warning: Invalid argument supplied for foreach() in C:xampphtdocswebjquery_versionprinterwebbackendetageprocess-sortable.php on line 12
30th March 2009
Not so super fast this time, I went out! That sounds like the serialised url string isn’t passing over correctly. Do a little debugging in the JavaScript, perhaps alert out the order variable before you do the AJAX, that might help you find the problem.
6th April 2009
Hi, would someone mind going over the solution that Karl posted (08-01-2009) for updating the db in one statment.
7th April 2009
Really nice!
15th April 2009
Nice code but its not subject to SQL injections. You should use PDO instead.
15th April 2009
@david by SQL I mean the lines needed to insert / update a MYSQL db, still PDO would be a nice way to access an SQL db: good tip.
4th May 2009
Wil, thanks for sharing this!
Everything works great with the information you have provided.
The one thing I am trying to figure out is how to drag a with the rows under it.
like:
one
two
So essentially we could drag the whole as a group or just the within the .
Does any of that make sense? I haven’t spent a ton of time trying to figure this out yet, wanted to get some opinions first.
4th May 2009
Sorry, my code got destroyed.
basically like this:
ul <- handle
-li <- handle
-li <- handle
Dragging the ul moves the whole group and dragging the li just lets you drag within the ul. I can’t seem to find any examples of this anywhere, any ideas? thanks!
8th May 2009
hi, can we transform the _GET to _POST ?
I try, but i don’t find…
Any help ?
Thanks
21st May 2009
Thx a lot for that, really helpfull!!! Maybe it will help somebody - I use this to UPDATE a table in 1 query:
$item){
$statement_middle.=“WHEN “.$item.” THEN “.$position;
$statement_ids.=$item.’,’;
}
$statement_end=” END WHERE id IN (”.substr($statement_ids,0,-1).”)”;
$statement_final = $statement_start.$statement_middle.$statement_end;
?>
7th June 2009
thank you for your effort, and i have a question:
how to make more than one list, and enable all to drag and drop block within each of them at the same time, i.e., list1 has (block11,block12) and list2 has (block21,block22) , i want to drag block11 (in list1) to list2???
thanks in advance
16th June 2009
I am using jquery 1.2.6 for all my existing functionality,
i try to integrate sortable with my image gallery.., But having version problem.. your script is using jquery 1.3.2 and ui is 1.7.1…
If i using this version, my existing functionlity is affected, so i want to use this sortable list using 1.2.6…
is there any possbilities to use with existing version…?
16th June 2009
Thanks for this. Came in handy
21st June 2009
Thank you so much for this! a real time saver!
30th June 2009
Is it possible to use this for nested lists ?
(does sortable support it ?)
Thanks !
4th July 2009
Hi Wil:
thx for your post, that’s great work.
i was wondering if there is a way to display sotable list with new order afterwords?
10th July 2009
Well, this is really nice. But there is one more thing I would like to know. How do I disable and then enable the drag and drop based on the drop actions. I want to disable drag and drop, execute the ajax and then re-enable it when the ajax is done. Thanks
10th July 2009
@Solomon you could unbind the event handler for the sortable, then re-enable it upon successful AJAX operation. You can use the unbind event to do that. If you put the code to add / remove the sortable in a function you could call it at will from wherever in your code.
17th July 2009
Thank you, very nice.
Just a thought however..
If my lis looks like something like this
Is there any chance for the plugin to even change the ID number in the input field also?
Thank you
22nd July 2009
jQuery for FUN????...LOLZ….i like it!!! 
….creative person…
26th July 2009
Thanks dude, thanks for sharing - much appreciated!
26th July 2009
cool!
is there any way to do this with lines from a file rather than a db
27th July 2009
@alex yes you could use PHP’‘s file function to do just that.
27th July 2009
thanks wil.linssen ive got it working that way but decided to use tables from my db got it all working ok but im stuck on the easy bit lol
while($rowf = mysql_fetch_row($fsql)){
$eem = “listItem_”.$rowf[1].”“;
echo”
” . basename($rowf[0]) . “
“;
}
as you can see ive changed the id to $eem wich is a row count but im srugling on how this would wright to the db im not sure on how it associates i want to sort the value of li id basename($rowf[0]). could you show me an example of how i can do this
Thanks
6th August 2009
This is working perfectly on Firefox, but apparently the AJAX call “$(”#info”).load(“process-sortable.php?”+order);” is not working correctly on IE8.
Any solution for this? (sorry im new to jQuery, im a prototype person).
6th August 2009
Ok, i found the solution for this not working on Internet Explorer. Apparently IE saves on cache even ajax http calls, and to avoid it doing this we need to add some random number to the call. This is how i edited the code.
Edit line:
$(”#info”).load(“process-sortable.php?”+order);
For:
$(”#info”).load(“process-sortable.php?random=”+Math.random()*99999+”&”+order);
Regards,
Bruno Q.
7th August 2009
@Bruno Thanks for sharing!
24th August 2009
Thanks for this script! I’m a Javascript and JQuery noob, so your explanations have been very helpful
I have a question though; I’ve got a page with several sortable lists. I want to pass an extra variable to the process-sortable.php so it will update the rows associated with that specific list. Ideally the query would be like…
UPDATE table SET position = $position WHERE id = $item AND listname = $listname
What’s the simplest way of going about this?
25th August 2009
Wow, great article. My boss asked if I could do this today as she saw a similar effect in the basecamphq software.
You’ve just saved me a lot of searching! Bookmarked!
Many Thanks
25th August 2009
@Bruno re the IR caching ajax calls:
You’ll need to generate a random number/string to the querystring as this tutorial uses the .load() method. If you had used the .ajax function you can setup caching within the call, but with .load you need to it manually.
I mentioned the subject here: http://www.web-design-talk.co.uk/30/simple-ajax-content-loading-with-jquery/
26th August 2009
Hi,
I can het this to work on a sinlge list by giving the list an ID. E.g and $(“ul#list-items”).sortable({
However, I have multiple ul on the page that I need to sort, but this doesnt seem to work with a ul class E.g. and $(“ul.list-items”).sortable({
Any ideas appreciated.
31st August 2009
Thank you ever so much! It works like a champ and it saved me an incredible amount of frustration!
4th September 2009
Hello,
thanks for your nice explanation. It helped me by using sorting content for my on CMS.
It was very easy to handle the thing with is very good and easy blogentry.
I really like JQuery very much. It’s so easy.
Thanks!
Looking forward to bookmark your page.
Yours, Hans
4th September 2009
Hi ! thanks for the tip, i’m looking for that long time ago ^^
Just one question :
I would like to save an array order into a row in a mysql user table. So each user can have a differente position of the list. How can i do that ?
for exemple in table ‘users’ an row ‘position’, i would like to keep the array order perhaps with serialize function…
well i don’t really know !
Perhaps you’ll can help me ^^
Thanks in advance
7th September 2009
Thank you so mucho for this tutorial.
You really made me learn a lot and above all…. save a huge amount of time!
—seb
16th September 2009
Nice one Wil! Very useful for the admin system i’m developing. Its amazing what jQuery can do in so few lines of code.
Hope all is well
29th September 2009
Cheers @Farid, great to hear from you!
1st October 2009
Excelent script nice and easy drop and get ordered !
Thanks. bookmarked!
5th October 2009
Is this possible to do with 2 columns? Ordering and dragging in multiple columns, but not netvibes like. It needs to preserve structure (just rearrange, and not adding additional row)
7th October 2009
Wil,
Thank you SO much for this. I’m using it for an image gallery to let users rearrange images in the order they want their slide show to run. Everything works fine - assigned the “handle” class to the images, the update callback works great, etc.
One problem, though: The image gallery is generated by a separate PHP file. When it’s included through a require(), everything works as expected, but whenever a new image is uploaded, I need to refresh the gallery, so I do a simple jQuery load().
When I do that, the gallery is refreshed properly except that it loses all the sortable functionality. Any ideas?
Thanks again for the great work.
15th October 2009
Great Tutorial Wil!
But could you help me, please? I’m trying to combine mySQL with a connected sortable list. I’m trying to merge this demo:
http://jqueryui.com/demos/sortable/#connect-lists-through-tabs
with your Database tutorial, but I just can’t do it. I have a database column named ‘tab’ and a column named ‘position’ so that only the position numbers change when sorting within each tab, but the ‘tab’ and ‘position’ numbers change when moving between tabs (lists).
Are you up for the challenge, and can you please help!
Kindest Reagrds,
Andrew
16th October 2009
The tab should have a different ID, but you could use a class name instead of the ID as the identifier for jQuery:
<ul id=‘something’ class=‘sortable_list’>...
$(“ul.sortable_list”).sortable({...
If you rename the IDs of the second list to something different the serialized results will come through with that name i.e. “testListTwo_x”
16th October 2009
2 questions:
1. What is the specific syntax for simply executing the code in the databse, rather than having it simply display on the page?
2. I’m a big fan of not having any “unnecessary” files—would it be possible to somehow have the process-sortable.php code embedded in the same page as the html code?
16th October 2009
@Cody 1) That’s the SQL you need to query the database, if you’re not sure of how to do that I’d suggest you read the PHP MYSQL reference.
2) I don’t particularly see that file as unnessecary, but you could just add an if (!empty ($_GET[‘listItem’]) : statement. I’d definitely advise against it though.
20th October 2009
@Greg: Make you you re-attach the sortable handler to any new elements. The handlers are ordinarily attached when the document is loaded, but if the document changes after this, the handlers are not attached to any new elements. I tend to create a wrapper function to assign the handlers and call it document.ready, then again to re-attach to any new elements after that.
20th October 2009
Thank you so much for the post.
Is there any way that i can get the dragged element id.
My app. needs to say “You have dragged THIS element”.
Thanks in advance
24th October 2009
Hi Will,
I have a big problem with IE6/IE7.
When implemented your code on my site I got the following problems:
I have a template with 2 columns(divs) left and right. Right one is with the list inside.
When I try to drop the selected li, after landing, all the images inside that li container disappear, including the handle image.
When I pick another element and play with it hovering up and down, the missing images are being displayed.
So after first ordering, I remain with 1 container without images inside and without the chance to move if. It becomes available to move only after I drop another element above or beneath it.
Please help me out with this, It’s driving me crazy.
24th October 2009
HELP!
I have 3 sortables on one page, buf after appending a row and refreshing, the result of sortable(‘serialize’) is undefined.
After some testing i found that just the sortable(‘refresh’), ffff’s up all the sortables. i’m pointing to the right UL, because appending works, and the serialize output works fine for all the sortables before appending and/or refreshing.
Anybody have an idea why refresh doesnt work?
Thanks!
8th November 2009
Thanks for this! This is what i needed for my admin-page!
11th November 2009
Hi everybody!
It’s great tutorial and was very helpful, but I’m afraid about DB overhead by sending save request on every reorder (I have about one houndred items to reorder). I’m newbie in jQuery and I can’t figure it out how to save items (send request) order on button click or widow onClose event?
Thanks
17th November 2009
Hy!
Thanks for the code, working 100%.
Well done!
Greetings!
22nd November 2009
I’m able to get it to display the query but it’s not updating my database. The queries that it’s displaying 100% work when I run them in phpMyAdmin. But, the page doesn’t do anything but display the queries.
Do I need to reorder the list and then submit a form?
30th November 2009
@Jeremy that code is just the sql you’ll need to insert them. I haven’t gone as far as to provide MYSQL connection and execution details.
My advice would be to look for a MYSQL class you can quickly include and use. Something like http://www.ricocheting.com/scripts/php_mysql_wrapper.php.
You might find this tutorial useful too: http://www.php-mysql-tutorial.com/ Good luck!
1st December 2009
Thanks, Wil. The connection/execution details are there. But I’m getting this error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘Array’ at line 1
1st December 2009
@Jeremy sounds like it’s trying to execute all rows in the array at once. You’ll need to use a foreach statement, something like
foreach ($sql as $query) :
$db->query($query);
endforeach;
1st December 2009
Well, I got it working as you’ve got it, displaying each query. But even with the MySQL connection, it’s not updating the database. From your example, index.html is now index.php with a query to display the rows that I’m wanting to update. Rather than use <li id=“listItem_1”> , I’ve used <li id=“listItem_$rowid”>. The UPDATE statement is displaying properly when I reorder the list so it must be working. But it’s just not communicating with the database.
7th December 2009
Great! Thanks wil.linssen. It’s exactly what I searched for UI of new EE extension . You save me a minimum couple hours.
7th December 2009
Really looking for an example that can handle reordering nested lists. Attempting this script on a nested list will only produce an array with only the top level ‘items.’
7th December 2009
here’s an oddball question: I have this tutorial (which I LOVE by the way) running on two identical websites on the same server…same database settings, etc. (I’ve started to build a CMS). On one of the sites the script as you have it functions perfectly. On another [using Firebug] I realize that the process-sortable.php page throws off a 500 error.
thoughts? ideas? also, what’s the purpose of the #info DIV and the FORM underneath the UL in your tutorial? I don’t understand what they’re for…is the #info DIV supposed to be updated with text when the script runs? (because if so, it doesn’t)
9th December 2009
I’m using jquery 1.3.2 and the personalized ui lib download from you demo, the drag & release works in charm but the my update handler isn’t triggered. I read the previous comments on this issue, am wondering if anybody has found the solution yet?
Thanks.
9th December 2009
@stephan if you’re getting a 500, I’d suggest looking at your server logs to determine the culprit. The 500 thing is quite often just a permissions glitch.
@shallway if you’re using the latest of both libraries you should be ok. A good idea would be to follow @stephen’s lead and watch Firebug for errors etc.
9th December 2009
Thank you Wil, I forget about those error logs. my directory was writable so I was getting yelled at. all is fixed now…and as to the other part of my ?? the output is now being written into the #info DIV.
thanks again!!!!
10th December 2009
Working now. The problem is I was using the code from the demo http://www.wil-linssen.com/demo/jquery-sortable-ajax/ which uses jquery-1.2.6 while the source code package uses jquery-1.3.2. Now I’m using the package source and it’s working perfectly.
21st December 2009
It’s nice but would be even better if it could remember user’s order of elements, perhaps with cookies. Is it achievable?
21st December 2009
@milos - that’ kinda the point of the MYSQL statements. You could of course use cookies if you wanted. You could just drop the serialized array into a cookie and voila - order stored.
5th January 2010
This is great, exactly what i was looking for. Cheers!
28th January 2010
do you only have to use li’s or can you use td’s within a table. would there be a way around to doing that
4th February 2010
Just wanted to say thanks, this is great. After doing it (of course after following your great example) it seemed so easy.
16th February 2010
You want it, you search it (thanks to google), you got it (thanks to you !).
18th February 2010
I know this is already an old article, but still very usefull and easy-to-understand. thx
26th February 2010
hi there,
is there anyway to reload the div after moving an item?
9th March 2010
Thanks for sharing the example. I do have a question on using this for a nested list.
What you have shown works for me but it only works for the top level item. I would like the PHP foreach to loop through all level items.
My list works perfectly with nested items. Just need a bit of help with the PHP page to loop through the additional levels.
14th April 2010
I was preparing myself to have to create a a system for sorting through around 800 field entries in a CMS I am making and was more than pleasantly surprised at how easy you’ve made it for me :D
Ta!
rvturnage
4th January 2009
Thanks Wil! I know this will help a lot of newbies like me to understand the process better. I especially appreciate seeing that you can send a response back from the processing php file.