Drag and drop reordering of database fields (sortables) with jQuery

This tutorial explains how to display a list of items from a database that can be reordered in real time by dragging and dropping, using the jQuery library. Moreover, no page reload is required on every reorder. If you want a bigger introduction to why you may want to do this I suggest reading my tutorial on how to achieve the same functionality using scriptaculous.

Previously I have explained how to achieve this in Scriptaculous and Mootools.

Step one: Include the jQuery libraries.

Grab jQuery and jQuery UI libraries.

Include them in the head of your document:
<script type="text/javascript" src="javascripts/jquery-1.2.6.min.js"></script>
<script type="text/javascript" src="javascripts/jquery-ui-personalized-1.5.2.min.js"></script>

Step two: Build a list of items from the database.

Give your list and id – I am using item_list for this tutorial – and then make sure each list item has an id of “item_PRIMARYKEY” where PRIMARYKEY is the key of the item from the database. Here is something similar to the list you should have:


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

Step 3: Make the list sortable and add a callback to our database script.

Using jQuery’s onload function or by adding the following script below our list,  inject some javascript into our list to make the list items sortable

$("#item_list").sortable({stop:function(i) {
$.ajax({
type: "GET",
url: "server_items_reorder.php",
data: $("#item_list").sortable("serialize")
});

Let’s look at this line by line so you can understand what is happening.

Line one uses the jQuery selector to target the element with an id of item_list (our unordered list in this case). We then apply the sortable plugin from jQuery UI.

Sortable has a number of options available, but in this case we are going to use the stop option (when sorting has finished) to make an ajax call. The important options in our ajax call are as follows:

  • type: the type of call (in this case GET);
  • url: (where the ajax post should be made) in this case “server_items_reorder.php”;
  • data : the string the makes up our GET call;

The data can be easily built up by using more of the inbuilt functionality of jQuery’s sortable:

$("#item_list").sortable("serialize")

This takes the current order of our list and builds it into an array that can be passed on like a GET string:

item[]=1&item[]=2&item[]=3&item[]=4

jQuery automatically serializes the list item id’s  into this array so you can see why I chose to give them the id item_PRIMARYKEY for the ease offered by convention.

So now we are making a ajax call and padding on the new list order we can write a script to update the database.

Step four: The database script (updating the position).

I am assuming your database table has an extra field called “position” that you can use to hold an integer of each items position as relating to its peers. What we need to do now is create our server_items_reorder.php script to collect the array of items and update the database. Here is that script:

// YOUR DB CONNECTION HERE
foreach($_GET['item'] as $key=>$value) {
mysql_query("UPDATE my_items" SET position = '" . $key . "' WHERE id ='" . $value . "'");
}

This script is simple enough to be written in the language of your choice. It collects the array of items from the GET variable and for each one updates the position based on the id of the item.

That’s all there is to it.

This tutorial is pretty low level as I have written most of these concepts including troubleshooting in my Scriptaculous and Mootools versions of these, so if you experience any difficulties look at those tutorials first, read the user comments and/or post any questions here.

12 thoughts on “Drag and drop reordering of database fields (sortables) with jQuery

  1. I have tried all three of these tutorials now in hopes that I might be able to get one of them to work.

    I had problems with Mootools because I could not find the ajax section of javascript from anywhere. (its not on there site any longer)

    I had scriptaculous working minus the server side updating and I just tried this one (jQuery) and my Firebug Debugging Console keeps telling me “missing } after property list.”

    For the life of me I cannot get this to work. Any suggestions? BTW, I am using a local server. Are there any settings relating to Ajax, that I need to change?

  2. Never mind I figured it out… It was just a combination of me sending the wrong parameters to the server_items_reorder.php and missing a few brackets and parentheses.

  3. Hi,

    First of all thanks for the tutorial.

    A little snafu, though. I am getting this response:

    “Invalid argument supplied for foreach()”

    [php]
    foreach($_POST['array'] as $key=>$value) {
    $wpdb->query(“UPDATE $playlists SET order =’” . $key . “‘ WHERE id =’” . $value . “‘”);
    }
    [/php]

    any hints? the foreach looks perfectly alright to me. stumped.

  4. solved it by using ‘toArray’ instead and then

    $pikand = ($_GET['data']);

    $array = explode(“,”, $pikand);

    foreach ($array as $position=>$id

  5. Thanks! Just wondering if you know how to get it to just send down the items in the list that have been swapped… as only 2 rows in the backend database really need to change every time the user drops a row into a new position. I’m trying this but it gets slow when I’m updating hundreds of rows in the database when only 2 of them have changed position each time!

  6. Hi,

    Okay this was my thoughts originally too. But in the last step – updating the ENTIRE table (especially if you have hundreds or even thousands of records) just because few records were changed or even only 2 records were “swapped” like Wes suggests above, is very very ineffective..

    I was thinking of a better solution like updating only the involved records, swapping them, etc. but it seems that all solutions have their bugs.

    Things to keep in mind if you are trying to avoid updating the entire table (like I suggest):
    - how to insert a new record
    - how to delete a record
    - what if the same list is updated simultaneously by 2 different users in 2 different browsers (this one is the trickiest)

    Please send me your opinions to my email if i don’t receive notifications from this blog.

  7. You can’t really just update the ones that have been swapped, as if you move an element to position two, you probably already have a record in position two, which will cause a conflict. You could potentially solve this by having a ‘date_last_sorted’ field and order by this, also, but then you run the risk of encountering the dreaded ‘unexpected behaviour’. This approach is probably the best (and the safest) for most applications.

    As for tackling multiple concurrent users, you’d probably have to employ some kind of a locking mechanism to the sorter when any other user is logged on and currently using the sorter. Considering the query will be almost instantaneous for most applications, though, I don’t see this being such a problem for most.

    Cheers for the tutorial though – this is a far simpler approach to the one I’ve used in the past, where I’ve used toArray() instead of toSerialize() to the same end.

  8. Maybe instead of serialize use toArray then with that array do the following in your PHP file:

    // example array
    $key_value = array(
    “1″ => “5″,
    “2″ => “6″,
    “3″ => “8″,
    “4″ => “9″
    );

    $strVals = array();
    foreach($key_value as $k=>$v)
    {
    $strVals[] = ‘WHEN ‘.(int)$v.’ THEN ‘.((int)$k+1).PHP_EOL;
    }

    mysql_query(” UPDATE my_items SET position = CASE id
    “.join($strVals).”
    ELSE position
    END”);

    This way you don’t continuously poll the DB you can do the UPDATE with just the one query!

    If you had hundreds of items in your list, sorting just one could take a long time and resources!

  9. Why is it when people write these tutorials they ALWAYS leave out code or paste code that is BROKEN??? So frustrating.

    I’m no expert in this, so it makes me infuriated when I try examples like this and spend an hour debugging code that should work as instructed!! Grrrrrr…

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>