script.aculo.us Ajax Sortable Lists Tutorial

Let’s make sortable lists that remember their order like the ones found in the To-Do lists for 37signals’s Basecamp. With Ajax calls and sortables implemented in Prototype and script.aculo.us, this is just a matter of putting the puzzle pieces together. And for those of you impatient, here’s the final result.

First off, you’ll need the Prototype and script.aculo.us libraries. The documentation is relatively good, especially for Prototype. Include these in your file.

  1. <script type="text/javascript" src="path"></script>

Remember that you need to include Prototype before script.aculo.us, a common first time mistake.

So in this tutorial, we’re going to sort list elements, but technically you could sort anything that the Sortable.create function of script.aculo.us supports.

Make a new list (with an id) and notice specifically the id of the elements. They have to be in the form string_identifier, where identifier is a unique number, for Sortable.serialize to work. You will probably want to retrieve each element from a database where the identifier would be the unique id. Also, your database table should contain a column that saves the order. For this example, we’ll call this column order_column and the list list_to_sort. Creative names. Here’s some pseudocode.

  1. <ul id="list_to_sort">
  2. // SQL Query: SELECT * FROM table ORDER BY order_column ASC
  3. // foreach: <li id="item_<? row.id; ?>"><? row.content ?></li>
  4.  
  5. // The following is the output that would get generated.
  6. <li id="item_2">Item 2</li>
  7.  
  8. <li id="item_3">Item 3</li>
  9. <li id="item_1">Item 1</li>
  10. </ul>

You can insert some entries into your table to check that everything works so far. Next, we’ll liven this up with Sortable.create.

  1. Sortable.create("list_to_sort", {
  2. onUpdate: function() {
  3. new Ajax.Request("file_to_call.php", {
  4. method: "post",
  5. parameters: { data: Sortable.serialize("list_to_sort") }
  6. });
  7. }
  8. });

Okay, let’s break this down. Check the documentation on Sortable.create for more information. The syntax is Sortable.create(id_of_list, parameter_hash); One of the parameters is a callback function onUpdate that is called when the user changes the order of the list and finishes the mouse click. Without this callback, the list will work on the client-side but nothing would be saved. So we’re going to make an Ajax POST request to the file file_to_call.php with the Prototype function Ajax.Request. Again, the documentation contains more useful information. But for here, we’ll need to specify a PHP file, which we will make later to interact with the database. We’ll send it as a POST request with a parameter data, the variable string that contains the current order of the list.

Sortable.serialize creates a string like: list_to_sort[]=2&list_to_sort[]=1&list_to_sort[]=3 where the numbers are the identifier parts of the list element ids after the underscore. Perfect.

Now we need to code file_to_call.php, which will take this string, passed as $_POST[’data’] and execute it.

  1. <?php
  2. parse_str($_POST['data']);
  3. ?>

There’s a PHP function you may not have seen before. parse_str parses a strings into variables, somewhat similar to executing the string. So for the above string, parse_str will result in a variable $list_to_sort that contains a 3-element array (2, 1, 3).

Now, all we have to do is update the database so the order is actually saved.

  1. <?php
  2. for ($i = 0; $i < count($list_to_sort); $i++) {
  3. // SQL Query:
  4. // UPDATE `table` SET `order_column` = $i WHERE `id` = $list_to_sort[$i]
  5. }
  6. ?>

And you’re done! Assuming all went well, your sortable list should remember its order in all its Ajax goodness. Again here’s the working final result for your viewing pleasure. Enjoy.

digg | del.icio.us

Comments

  • Great thanks Paul! It exactly what I needed for admin part! It's more than SUPER!

    Denis Korablev
    Sat Sep 13 2008
    6:03 AM
  • This's great!!!! Im very happy with it.

    Thank you for sharing it.

    but,

    Im wondering something more.

    How can i check value of $_POST['data'] it?.

    I want to change even name of menu with ajax.

    not just sorting.

    Can you help me paul?

    Nicky Park
    Fri Oct 17 2008
    5:14 AM
  • Awesome! Thanks a lot for this tutorial, it's really simple and allowed me to make some great list sorting tools!

    Jordan
    Wed Nov 12 2008
    4:21 PM
  • Thank you SO much for providing this - it's exactly what I needed. You are a wonderful person!

    Amy
    Thu Nov 20 2008
    4:19 PM
  • very good.

    It's what I need,Thank youvery much!

    jessie
    Wed Jan 14 2009
    10:16 PM
  • thank you so much for this tutorial. i have my list items sorting now which is great. just one problem: the ajax end of things doesn't seem to be working. also perhaps i should note that one your demo the ajax end of things doesn't seem to be working either. no matter how quickly i refresh the page the items are always back to the same way they were before i resorted them. just letting you know. okay take care and thanks again!

    Ezra
    Thu Jan 22 2009
    11:13 PM
  • Thanks for this easy to follow tutorial. I've not dabbled with draggables before now - but you have definitely shown me how easy it is! thanks again!

    Mon Jan 26 2009
    1:27 PM
  • molar es mucho

    Molo
    Thu Jan 29 2009
    9:54 AM
  • The demo doesn't save the results to the database. On refresh it goes back to it's original order.

    Nate
    Thu Mar 5 2009
    1:52 PM
  • Thank you very much.

    Simple, straight to the point.

    Sat Mar 7 2009
    9:05 AM
  • Wow,

    Very nice thank you

    Wed Mar 11 2009
    8:55 PM
  • Very nice thank you

    Wed Mar 11 2009
    8:55 PM
  • Where are you putting the "Now, all we have to do is update the database so the order is actually saved." code?

    Is that going into the "filetocall.php" file? Or somewhere else...Love some help on this...All the DRAG/DROP is working great, but I'm a little lost on how exactly to save the data.

    Randy
    Mon Mar 23 2009
    5:24 PM
  • Great!

    Sat May 2 2009
    8:18 PM
  • asdfasdf

    foo
    Wed May 6 2009
    6:18 AM
  • Thanks a bunch.

    Works great!

    Tue May 12 2009
    12:19 PM
  • Great - thanks for this.

    Worth emphasising that the <li> "id" has to be of the form stringnumber (eg a1, a_2) and not (a1,a2) or it won't work. Took me a while to figure it out, but it is stated in the Scriptaculous doc for Sortable.

    CMG
    Sun May 17 2009
    8:38 AM
  • oop .. previous post didn't display the way I expected.

    The important thing to note was that the <li> "id" has to be formatted as string-underscore-number, otherwise it doesn't work.

    CMG
    Sun May 17 2009
    8:44 AM
  • Great tutorial, however, your example assumes that you have entries in the database which have an id of 1, 2, 3 and whose position can be ordered 1,2,3 etc.., but what if you need to identify the list items in a different way?

    Supposing the items from the database had ids of lets say 8, 9, 10 and their positions rather 1,2,3 then your example would not work because you would not be updating the right entries in the database..

    Tue May 19 2009
    10:25 AM
  • I got this to work in Rails with a bit more work, with any given subset of rows from the database.

    For example, let's say you have 4 elements in the list, and their ids in the desired order are {9, 15, 8, 25}, after drag'n'drop. First, use this "desired order" list, POSTed to the server, to get the rows from the database, ordered by their existing ordercolumn values (I use a descending order).

    In the database, let's say their "existing order" is {15, 25, 9, 8}, when ordered by their respective ordercolumn's, which are {25, 15, 9, 8}...

    Loop through both arrays, the desired order and the existing order. For row id=9, update ordercolumn = 25. For row id=15, update ordercolumn = 15. For row id=9, to ordercolumn = 9. Then for row id=25, update to ordercolumn = 8.

    That should put them in the correct order and maintain the uniqueness of the order_column, just as id's should be unique.

    Sat May 23 2009
    3:07 PM
  • Oh, I forgot to mention... This assumes the html ids of the li elements are based on the database ids. Rails does this automatically with content_tag_for :li, @post, or similar... So post _ 9, post _ 15, post _ 8, and post _ 25...

    Sat May 23 2009
    3:39 PM
  • Good WORK.... my list now is perfect. This a simply and powerful sorting tool!

    Thank You!

    enrico
    Mon May 25 2009
    3:16 AM
  • Does anyone know of a ColdFusion example of this?

    Maya
    Tue May 26 2009
    1:58 PM
  • I was trying to copy this and it wont update the database. it seems everything is in place and the sort works on the page but it doesnt update the DB. any help would be appreciated.

    Tony Leone
    Thu May 28 2009
    3:58 PM
  • Sorry Brain FART!! :) helps to have database connectivity.. :)

    Tony Leone
    Thu May 28 2009
    4:06 PM
  • This is great. After the database has been updated with new list order, how can you update outputted data on the page with the list so that it reflects the updated info? For example, if your list was numbered, how would you update/refresh those numbers once an item has been moved without refreshing the whole page?

    Devin
    Mon Jun 1 2009
    2:01 PM
  • Thanks for writing a clear, easy to follow tutorial for this. The scriptaculous document is short on practical applications, especially for newbies.

    Max
    Fri Jul 17 2009
    8:27 PM
  • Wow, finally a nice simple tut with very clear examples and explanation. Why can't all tuts be like this? Thanks much!

    Brad
    Thu Aug 13 2009
    9:25 AM
  • My comment !!!

    NN
    Wed Sep 16 2009
    2:27 AM
  • This is a short an amazing tutorial that strikes to the point and deserves many thanks!! :)

    Wed Sep 23 2009
    1:09 PM
  • superrrrrrrrrrr

    Me
    Wed Mar 3 2010
    10:05 PM
  • q2yPZQ Cool lol hey bla bla bla bla

    Fri May 21 2010
    2:12 AM
  • 318ayY Cool lol hey bla bla bla bla

    Fri May 21 2010
    3:53 AM
  • wVmH9b Cool lol hey bla bla bla bla

    Fri May 21 2010
    5:35 AM
  • This very cool and was really very helpful

    Mangesh
    Fri Jun 4 2010
    6:58 AM
  • This was very cool and really very helpful

    Mangesh
    Fri Jun 4 2010
    6:58 AM
  • this was very good,nice script.

    This is [qeehu.com](http://www.qeehu.com/ "qeehu") my website.

    ken
    Thu Jun 24 2010
    7:34 PM
  • well done!thanks.<a href="http://www.51guest.com/">builder</a>my site

    Thu Jun 24 2010
    7:36 PM
  • THX that's a great anwser!

    Wed Apr 20 2011
    2:30 AM
  • EpwKIa <a href="http://cyjmnkfxemzo.com/">cyjmnkfxemzo</a>

    Wed Apr 20 2011
    2:25 PM
  • I need to save sortable list.. so when we sort something will save as the new sort..

    somebody help me??

    Thu May 12 2011
    2:22 AM
(required)
(required, not shown)
(anti-spam)
[ comments may be held for moderation before appearing ]