Sortable List Example With Scriptaculous and PHP/mySQL

Jan

05

After spending a considerable amount of time looking for a decent example of "how to store the newly sorted order of a list after dragging and dropping", I realized that there isn't a whole lot of examples and documentation out there. I've decided that it's my turn to try and come up with a full blown, easy to understand example using XHTML, Javascript, PHP, and mySQL.

First off, you'll need both the scriptaculous and prototype javascript libraries (both available from script.aulo.us). Once you've downloaded these files, you'll need to reference them in the head of your file:

<script type="text/javascript"
src="scripts/prototype.js"></script>
<script type="text/javascript"
src="scripts/scriptaculous.js"></script>

Then, in your html file, make your list:

<ul id="items_list">
	<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>

Note that the ul and each li have unique ids. It is also important to note that the ids of the list items must have "_#" at the end (this number must be unique).

Next, add this after the list (because of IE):

<script type="text/javascript"
language="javascript">
Sortable.create("items_list",{dropOnEmpty:true,con
straint:false});
</script>

Cool! It works! But...

Now that you've done this, go ahead and check it out. Cool... You can now sort your list by dragging and dropping! This is all fine and dandy, but how do I actually store this new order in my database?

It's pretty simple actually. In comes "serialize", to make life easy. This little gem creates a string containing information on of the current state of the list being sorted. How do you use it?

var listorder = Sortable.serialize('items_list');

Let's say that you've sorted your list as follows:

  • item 3
  • item 2
  • item 1
  • item 4

When invoked, the variable listorder would return as "items_list[]=3&items_list[]=2&items_list[]=1&items_li st[]=4". And this is what you need to update your database. Notice, the values returned are associated to whatever is after the underscore (_) in the id of the list item.

Now that you have this information, you can easily update your database with a simple AJAX call pointing to your php file ('save_items_order.php' in this case):

function saveOrder() {
	listorder = Sortable.serialize('items_list');
	var url = 'save_items_order.php';
	var pars = listorder
		+ '&rnd='
		+ new Date().getTime();
	var myAjax = new Ajax.Request(
		url,
		{
			method: 'post',
			postBody: pars,
			onComplete:  showList
		});
}

Finally, the PHP/mySQL piece:

if($_POST) {	
  $items = $_POST['items_list'];
  $i = 1; # will represent the actual order (starting at
1).
  foreach($items as $itemid) {
    $query = "
      UPDATE items_table SET order_col = $i WHERE id_col =
$itemid
    ";
    mysql_query($query);
    $i++;  # add 1 to the current value of i (the next order
in the list).
  }
}

There you have it. An example of how to sort a list, and then store the new order in your database.

feedtag thisdigg this

17 CommentsComments

  1. Gravatar must be down Aaron

    Excellent tutorial. Thank you!

  2. Gravatar must be down Patrick

    You’re welcome. I appreciate the comment.

  3. Gravatar must be down zaver

    nice tutorial it was quite helpfull. I have searched for this kind of tutorials but as you said there arent many and those out there are not very helpfull

    Thanks

  4. Gravatar must be down webdude

    Hi. Do you have a working example as I am having problems with getting it to work.

    Thanks

  5. Gravatar must be down Steffen

    Hey, what kind of mySQL table do I need to create to make this work?

    Thanks

  6. Gravatar must be down Patrick

    @Steffen – You’ll have to have a column in your table dedicated to the list order. Notice in the example above, I’m updating the values in the ‘order_col’ column. Then, you can use the ‘ORDER BY order_col ASC’ in your SELECT statement to get the correct order when displaying the list. Hopefully this helps.

  7. Gravatar must be down Jon

    Patrick, is there a way you can post your full source code for the example so I can see how everything meshes together?

  8. Gravatar must be down William

    How could I live update the list. That is, without moving the div to another .php file and just add the new li? Excellent script, anyway! Very tight.

  9. Gravatar must be down jamus

    this looks awesome. Do you have a demo of it working?

  10. Gravatar must be down Patrick

    @jamus – I apologize, however, I don’t have a working demo of this. Back when I wrote this I created something for a client of mine that would modify the order of a set of images. This page has since changed, and the code for it is now gone. If I can find some free time, I’ll try and fix up a demo.

  11. Gravatar must be down Thomas Missonier

    Hi.

    Thank you for this tutorial.

    You help me to finish a project and understand sortable.create :)

  12. Gravatar must be down Jim

    Please note that the PHP code is not save against hackers. (SQL Injection)

  13. Gravatar must be down Mark

    nice tutorial, do you not need the ‘onUpdate’ in order to call the AJAX …

    Sortable.create(“items_list”,{onUpdate:saveOrder,hoverclass:’dragover’,dropOnEmpty:true,constraint:false});

    ?

  14. Gravatar must be down paul

    Short and clear tutorial. I like it. Thanks a lot.

  15. Gravatar must be down Craig Jardine

    Excellent – thank you, also thank to Mark for his tip.

  16. Gravatar must be down hmoyat

    In order to catch the even and save the information via ajax you need to create the sortable list in this way

    Sortable.create(”items_list”,{onUpdate:saveOrder,hoverclass:’dragover’,dropOnEmpty:true,constraint:false});

  17. Gravatar must be down Abel Antonio

    that’s a good tutorial, but i have some question, jeje i’m programing with RoR jeje, and i don’t know php, how can i save the listrecord??

Leave a Comment