Originally on wiseguysonly but he’s changed his blogging software and has yet to put this article back up
Drag and Drop Reordering of Database fields with Ajax.
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:
Fire up your favourite HTML editor and create a new document.
Include the following scripts from the scriptaculous libraries:
prototype.js
scriptaculous.js
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
Make our script call a secondary function when an item is dropped.
Make this secondary function post the new order of items to a background PHP script
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:
// 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.
.
