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.
Excellent tutorial. Thank you!
You’re welcome. I appreciate the comment.
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
Hi. Do you have a working example as I am having problems with getting it to work.
Thanks
Hey, what kind of mySQL table do I need to create to make this work?
Thanks
@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.
Patrick, is there a way you can post your full source code for the example so I can see how everything meshes together?
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.
this looks awesome. Do you have a demo of it working?
@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.
Hi.
Thank you for this tutorial.
You help me to finish a project and understand sortable.create :)
Please note that the PHP code is not save against hackers. (SQL Injection)
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});
?
Short and clear tutorial. I like it. Thanks a lot.
Excellent - thank you, also thank to Mark for his tip.
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});
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??