Tricklin

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>
</ul>

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,constraint: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.