Drag and Drop Reordering of Database fields with Scriptaculous.

Rather than go into immense detail about why you would want to do this, let me present two real world examples of projects I have recently completed that have benefited from this kind of functionality:

  1. An ecommerce site where the site owner wanted to arrange categories and products in a more logical order (e.g popularity) as opposed to alphabetically.
  2. A real estate site where the site owner required the ability to upload multiple images of a property and then reorder them.


As always I will be using PHP as the server-side component to this recipe, but I aim to keep it as out-of-depth to enable an easy transition to the server-side language of your choice. Right, let’s get started.

Ingredients:

  • The latest scriptaculous libraries.
  • A database and scripting language of your choice (PHP / MySQL is used here).
  • A text editor.

Step One: Create the database:

Create a database as normal to hold whatever fields you require. Add an extra field called “position”, that will hold an integer, to your database; this will hold the position of the items but should contain a default value of 0 for when a new item is added. This means new items will go to the top of the list when we output them later on.

Here is my database schema for this example.

  • id => Integer, Auto Increment, Primary Key.
  • item => Varchar(30)
  • position => Integer

Now insert some data in there so we can write our re-ordering script.

StepTwo: The (X)HTML:

  1. Fire up your favourite HTML editor and create a new document.
  2. Include the following scripts from the scriptaculous libraries:
    • prototype.js
    • scriptaculous.js
  3. Output your database records into an unordered list with and id of “item_list”. Each list item should have an id of “item_{id}” where {id} is substituted by the auto increment of that item on the database. Don’t forget to add a condition of “ORDER BY position ASC” to your SQL so the records come back in the correct order once we have updated their positions.

Here is a rough idea of how your list should look:

<ul id="item_list">
<li id="item_1">Item One</li>
<li id="item_2">Item Two</li>
<li id="item_3">Item Three</li>
<li id="item_4">Item Four</li>
</ul>

Once you have yourself a nice list that looks like that, you start adding some drag ‘n drop magic.

Step Three: Introduce the Drag and Drop functionality

Our first pass at the JavaScript doesn’t involve any Ajax. Here we just want to make our list items draggable. Here is the code we need that must go anywhwere below the list. I put it immediately after my list normally so it is easy to locate for debugging. Here is the code:

<script type="text/javascript">
Sortable.create('item_list', {constraint:'vertical'});
</script>

We are calling on the magic inside the scriptaculous libraries to create a new sortable list with the function Sortable.create(). The first argument must be the id of the list we want to reorder. Other options are contained within the curly brackets. Here we are just telling the list that we only want items to move vertically – i.e. up and down.

Step Four: Saving the new order to the database.

This step is a little more involved so make sure that you have got everything up to this point working perfectly. You will probably want to spend a little time basking in the joy of drag and drop lists before you go any further, anyway!

Ok, here is a summary, followed by the details, of what we have to do to get the order of our records saved to the database

  1. Make our script call a secondary function when an item is dropped.
  2. Make this secondary function post the new order of items to a background PHP script
  3. Write that background PHP script to take the new order and update the database.

Ready?

Firstly, let’s tell our Sortable.create function what to do when an item is dropped. This is done with an option in those curly brackets we mentioned earlier. Change your Sortable.create function to look like this (the bold, italic part is the addition).

Sortable.create('item_list', {constraint:'vertical', onUpdate : updateOrder});

This tells Scriptaculous that we want to call our own JavaScript function called updateOrder whenever an item in our list is dropped.

Secondly we need to create that updateOrder function. It goes above our Sortable.create, in the same script block, and looks like this:

function updateOrder(){
var options = {
method : 'post',
parameters : Sortable.serialize('item_list')
};
new Ajax.Request('server_items_reorder.php', options);
}

Let’s take a quick under-the-bonnet look at that function. It is easier to start from the bottom up.

The penultimate line creates a new Ajax request to a PHP script called server_items_reorder.php. It also takes an argument of some options.

The next block of code up creates these options. It tells our Ajax request to serialize the list items in the current order they are in and then send them to the php script using POST as the method. The serialization creates an array in the form of [0]=>{id}, [1]=>{id}, [2]=>{id}. If you reall want to know, scriptaculous reads the list in its current order and strips the item_ from the id of each list item. This leaves us with an array of ids that reflect the order of our list.

The end is now in site. All we need is the PHP script (or any other language of your choice) that takes these post variables and updates the database. Here you are:

<?php
// ADD YOUR DATABASE CONNECTION PARAMETERS HERE!!!
$i=1;
foreach($_POST['item_list'] as $key=>$value) {
mysql_query("UPDATE items SET position='".$i."' WHERE id ='".$value."'");
$i++;
}
?>

Here is what is happening in our php script:

  • We set $i to 1. This variable increments to create the position on our database.
  • We loop through the array posted by our Ajax call, updating the position field on the current record to the value of $i. At each pass, the value of $i is increased.

Now when you reorder your items within the list and refresh your page, they will stay exactly where you left them. Why not try add some css and get your items looking sexier, or create a list of images that can be reordered. There are also a few options available including one that lets you create a handle that is thepart of your list item that you can drag by. Take a look at the Sortable.create wiki entry for a comprehensive list of items.

As always, if you find any errata in this piece, speak up and post a comment and I will fix it up good ‘n proper.

Troubleshooting

I hope that you are as impatient when it comes to reading troubleshooting guides as I am when writing them. So here goes:

  • Ensure you included the scriptaculous libraries and in the correct order.
  • Double check the source code to make sure your list has the correct structure.
  • Check that you are referring to the correct list id in your Sortable.create call and Ajax.request options.
  • Check you are calling the right script in your Ajax.request call
  • Make sure that you are connecting to your database correctly in the server side script.

If all else fails, post a comment here and I will try to help.

22 thoughts on “Drag and Drop Reordering of Database fields with Scriptaculous.

  1. This is the first real introduction to prototype and script.aculo.us that I have seen. Even though it isn’t aimed at proto-beginners I found it really easy to follow (your writing style was clear and concise) and have implemented this in a project I am working on.

    Thanks a lot for this.

  2. Hi,

    I have tried to do this with image tags, but it doesn’t seem to work. I don’ think it’s even sending anything to my php file to update the database:

    CODE:

    Fashdax | Your Fashion Connection

    ‘.”\n”;
    }
    ?>

    function updateOrder(){
    var options = {
    method : ‘post’,
    parameters : Sortable.serialize(’imageFloatContainer’)
    };
    new Ajax.Request(’http://www.fashdax.com/gallery_update.php’, options);
    }
    Sortable.create(’imageFloatContainer’, {tag:’img’,overlap:’horizontal’,constraint: false, onUpdate : updateOrder});

  3. thank you for your tutorial. it has set me on the right track to creating the killer app i need. i know you use php, but would you happen to know how to loop through the items using asp.net?

    thanks for your help,
    abe

  4. @JJ – After you build the options in updateOrder():

    var options = {
    method : “post”,
    parameters : Sortable.serialize(“imageFloatContainer”)
    };

    add this line for debugging the serialized string:

    alert(options.parameters)

    If this doesn’t alert a string of your item_list then the problem is in your Sortable.create is wrong. If it does it is your server script.

  5. hi,

    this is my code

    function updateOrder(){
    var options = {
    method : ‘post’,
    parameters : Sortable.serialize(‘item_list’)
    };

    new Ajax.Request(‘server_items_reorder.php’, options);
    }
    Sortable.create(‘item_list’, {constraint:’vertical’, onUpdate : updateOrder});

    is this all correct?
    as it is not updating my table

    this is my code in the server_items_reorder.php file, with my connection parameters above.
    $value) {
    mysql_query (“UPDATE jobimages SET jobimagesOrder=’”.$i.”‘ WHERE id =’”.$value.”‘”);
    $i++;
    }

    any help please

    thanks
    carl
    ?>

  6. Hi
    I have tried your example and its working properly but when i have used it for my scripts it is raising js error called “Sortable is not Defined”. The whole error msg says like “Sortable is not defined
    comparables()()comparables (line 268)
    [Break on this error] Sortable.create(‘item_list’,{tag:’tr’,…cal’, onUpdate : updateOrder,tree:true})”

    What is the Possible Reason For this ?
    Srinivas.

  7. I’m trying to use this in an app I’m writing but cannot get the drag & drop part to work.

    The two .js files are being included correctly.

    But I’m not getting any drag & drop functionality.

    [code]

    Item One
    Item Two
    Item Three
    Item Four

    Sortable.create('item_list', {constraint:'vertical'});

    [/code]

    Any ideas?

  8. I’m trying to use this in an app I’m writing but cannot get the drag & drop part to work.

    The two .js files are being included correctly.

    But I’m not getting any drag & drop functionality.

    Item One
    Item Two
    Item Three
    Item Four

    Sortable.create(‘item_list’, {constraint:’vertical’});

    Any ideas?

  9. Solved that problem. But I’ve got another quick question.

    I’ve added a save button and moved the function that calls the reorder script to the onclick event for that button.

    I’ve also added a reset button. which currently works by simply refreshing the page. – onClick=”location.reload(true)”

    However, there must be a way to reset the item order to how it was when the page first loaded without having to reload the whole page again?

    Any idea how to do it?

  10. Hi, this is my code

    Untitled Document

    <li id=”item_” style=”cursor:move”>

    function updateOrder(){
    var options = {
    method : ‘post’,
    parameters : Sortable.serialize(‘item_list’)
    };
    new Ajax.Request(‘server_items_reorder.php’, options);
    }

    Sortable.create(‘item_list’, {constraint:’vertical’, onUpdate:updateOrder});

    This is my php code

    // ADD YOUR DATABASE CONNECTION PARAMETERS HERE!!!
    include (“connect.inc.php”);
    $i=1;
    foreach($_POST['item_list'] as $key=>$value) {
    mysql_query(“UPDATE items SET position=’”.$i.”‘ WHERE id =’”.$value.”‘”) or die(“UPDATE items SET position=’”.$i.”‘ WHERE id =’”.$value.”‘”);
    $i++;
    }

    The problem I have is that the updateOrder function does not fire the php script. The table in fact doesn’t update. I have checked the code time and time but cannot see any mistakes…

    Can you help me…?
    Thanks a lot in advance

  11. My code clearer ..sorry

    Nicola

    Barbara

    Maria

    Ada

    Giovanni

    Paolo

    Gennaro

    Rossella

    function updateOrder(){
    var options = {
    method : ‘post’,
    parameters : Sortable.serialize(‘item_list’)
    };
    new Ajax.Request(‘server_items_reordersasaasa.php’, options);
    }

    Sortable.create(‘item_list’, {constraint:’vertical’, onUpdate:updateOrder});

  12. Don’t call me dummy…that’s the final one

    Nicola

    Barbara

    Maria

    Ada

    Giovanni

    Paolo

    Gennaro

    Rossella

    function updateOrder(){
    var options = {
    method : ‘post’,
    parameters : Sortable.serialize(‘item_list’)
    };
    new Ajax.Request(‘server_items_reorder.php’, options);
    }

    Sortable.create(‘item_list’, {constraint:’vertical’, onUpdate:updateOrder});

  13. thanks for the script above…i got the drag and drop function to implement perfectly but i don’t think its sending anything to my php page to update the information in the database….here’s my script in the html

    <li id=”item_”>.        

    function updateOrder()
    {
    var options =
    {
    method : ‘post’,
    parameters : Sortable.serialize(‘myList’)
    };
    new Ajax.Request(‘reordermenu.php’, options);
    }
    Sortable.create(‘myList’,{ghosting:false,constraint:true,hoverclass:’over’,
    onChange:function(element)
    {
    var totElement = ;
    var newOrder = Sortable.serialize(element.parentNode);
    for(i=1; i<=totElement; i++)
    {
    newOrder = newOrder.replace(“myList[]=”,”");
    newOrder = newOrder.replace(“&”,”,”);
    }
    $(‘myList_serialize’).innerHTML = ‘New Order: ‘+newOrder;
    $(‘newOrderInput’).value = newOrder;
    },
    onUpdate:updateOrder});

    and here’s my script in the php page…

    $value)
    {
    $updateSQL = sprintf(“UPDATE menu_main SET position=’”.$i.”‘ WHERE menuid =’”.$value.”‘”);
    $results = mysql_query($updateSQL, $con) or die(mysql_error());
    $i++;
    }
    mysql_close($con);
    ?>

    what am I doing wrong???

  14. Is there a way to include a php file from elsewhere within the requested file?? I’m trying to set this up but include my database connection file so I don’t have to write out the connection details more then once.

    a standard php include doesn’t work?

  15. Is there a way to include a php file from elsewhere within the requested file?? I’m trying to set this up but include my database connection file so I don’t have to write out the connection details more then once.

    a standard php include doesn’t work?

    This is my script:

    $value) {
    mysql_query(“UPDATE items SET position=’”.$i.”‘ WHERE id =’”.$value.”‘”);
    $i++;
    }
    ?>

  16. sorry about that, ignore my code above, this comment thing got rid of a lot of it for some reason so the code above is not what i have.

  17. ASP:
    on the AJAX page…..

    Dim myString, myArray
    myString = Request(“item_list[]“) ‘get the string/list by Request
    myArray = Split(myString, “,”) ‘split the string at commas and put in array

    For i=0 to UBound(myArray)
    objConn.Execute(“UPDATE TABLE SET [Position]=”&(i+1)&” WHERE ID=”&trim(myArray(i))) ‘update record
    Next

    HTH

    TW

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>