Mar 11

Drag and drop reordering of database fields with Scriptaculous

Ajax, Scriptaculous, User Interface, Web Development Add comments

Rather than go into 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 tutorial, but I aim to keep it as out-of-depth to enable an easy transition to the server-side language of your choice.

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 table to hold whatever fields you require (or use an existing table). Add an extra field called “position”, that will hold an integer, to your table; 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 an example 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!

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.

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.

77 Responses to “Drag and drop reordering of database fields with Scriptaculous”

  1. Edd Couchman Says:
    August 16th, 2006 at 10:43 pm

    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. daelan Says:
    August 22nd, 2006 at 10:54 pm

    This is without a doubt the easiest to follow script.aculo.us tutorial that i have come across. Very nice work

  3. Tim Says:
    August 23rd, 2006 at 7:24 am

    Thank you for your kind comments. I have never considered myself a natural writer so it is nice when something appears to hit the mark.

    I have a couple more Ajax related tutorials on thier way over the next two weeks.

  4. Tom Kerswill Says:
    September 15th, 2006 at 6:21 pm

    This is brilliant - looks so simple. Looking forward to implementing the steps soon…

  5. JJ Says:
    September 26th, 2006 at 5:50 pm

    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});

  6. Abraham Andres Luna Says:
    September 27th, 2006 at 2:17 pm

    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

  7. Tim Says:
    September 27th, 2006 at 5:51 pm

    Hi Abe,

    Not sure about the ASP.NET solution as I am not a .NET developer. However I expect in ASP the array would be contained within Request(”item_list”).

    Here is also a link with a good deal of information about loops in ASP.NET; VB etc. If you figure it out post it here then we can all take a look:

    http://www.informit.com/articles/article.asp?p=30110&seqNum=6&rl=1

  8. Tim Says:
    September 27th, 2006 at 5:57 pm

    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.

  9. Michael Says:
    October 4th, 2006 at 12:13 am

    Just so I understand . . . I’ve got

    … in my header,

    HOME
    CONTACT US
    FAQ
    ABOUT US

    … as the output of my php/mySQL interaction and after that, I’ve got

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

    … so now I should be able to drag the items around? Your instructions say to make sure everything is copacetic before going further, but if things are supposed to be draggable at this point I’m stuck because it’s nowt working.

    You have a very well-written, simple tutorial, and I’ve been over it a million times now, but I’m at a loss. Any ideas on what to check for?

  10. Tim Says:
    October 4th, 2006 at 7:51 am

    Hi Michael, unfortunately I can’t see what you have because Wordpress is removing the HTML.

    Can you send this comment to me using the address timstermatic {at-symbol} gmail.com and I will look over what you are doing.

    Cheers. Tim

  11. Michael Says:
    October 4th, 2006 at 8:10 am

    I will do that right now . . . thanks.

  12. Ash Says:
    October 4th, 2006 at 4:17 pm

    Excellent guide, nice and easy to follow and got me up and running really quickly.

    thanks

  13. Pete Says:
    October 11th, 2006 at 9:36 am

    Cheers for this - it was eactly what I was looking for and very easy to follow.

    I was wondering though if there was a way to return an error if the PHP script could not be found? Granted, I try and get my filenames right, but if there was an automatic error message if the file couldn’t be found it would make debugging that bit easier.

    Other than that, fantastic work and I look forward to reading more of your tutorials!

  14. Tim Says:
    October 11th, 2006 at 6:42 pm

    Hi Pete,

    Scriptaculous does support debugging, but I have never actually implemented it. However, here is my best guess at adjusting the above example:

    Change the whole function:

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

    to read as follows:

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

    Notice the addition of 2 new paramters: onSuccess and OnFailure.

    Now above the updateOrder function create the response handlers required:

    var handlerFunc = function(t) {
    alert(t.responseText);
    }

    var errFunc = function(t) {
    alert(’Error ‘ t.status ‘ — ‘ t.statusText);
    }

    Theoretically that should alert the output of your server script. Let me know if it works.

    Tim

  15. Raj Says:
    October 17th, 2006 at 2:04 pm

    Hi

    Thanks for your nice article, I am also building similer application, but my problem is little different,

    I have 2 blocks, i will place the blocks any where on the screen instead of a specific place holder.
    now i want to find what are the top and left positions currently i have dragged to?

    how can i find the current position of the draged block.

    Thanks

  16. binbarn Says:
    October 17th, 2006 at 10:12 pm

    Hi,

    Having a bit of trouble with it. Using php/mysql.

    My ’server_items_reorder.php’ code is this:

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

    Nothing is being written to the database though. Have I missed something?

    The recordset for the list page is pulling everything through ok so I figure I must have messed up here.

    Can you supply the example php code for reference anyway?

    Thanks

  17. Claude Says:
    October 17th, 2006 at 10:35 pm

    Here’s my dilemma. I have a gallery of photos similar to what you mentioned at the beginning of your article. Under each image, I have text that reads “Display order: 1″ where 1 is the number that the image is set to display. I want to update that text with the new order after it has been written to the DB. I’m new to javascript and therefore prototype and scriptaculous, so I’m working overtime to try to figure this out. Any help you could offer would be greatly appreciated. Great article by the way. Wish I had found it before I finally got my stuff writing to the DB!

  18. Tim Says:
    October 17th, 2006 at 10:46 pm

    Binbarn - Are you including your mysql connection parameters in the server file? I will try to get my example files posted by the weekend - I have a pretty busy week this week.

    Claude - Try using an OL instead of a UL. The numbers should update themselves, it may not be exactly what you want, but give it a try anyway.

    Tim

  19. Sean Says:
    October 19th, 2006 at 11:49 am

    Hey thanks for the great article! Just one quick question: How can I send one additional variable to my PHP script for processing?

    Right now I have this:
    parameters : Sortable.serialize(’photos_list’)

    And then my PHP script can find $_POST[’photos_list’]

    But since I have four photo galleries, I need to pass 1,2,3 or 4 to my PHP script so he knows which gallery table to update. Any easy way to pass that param along?

    Thanks in advance! Sean

  20. Tim Says:
    October 19th, 2006 at 1:47 pm

    Hi Sean,

    There is a pretty easy way to do this by appending a query string to the script you are calling:

    new Ajax.Request(’server_items_reorder.php?gallery=1’, options);

    Then you can pick up $_GET[’gallery’] in your server-side script.

    Tim

  21. Sean Says:
    October 20th, 2006 at 5:28 am

    Thanks Tim! So simple — so obvious :-) Works like a charm!!! :-D

  22. Larry Says:
    November 20th, 2006 at 4:27 am

    I’m having a devil of a time getting this to work. There seems to be an issue with the serialized list items. If I put in an alert, I get the expected (item_name[]=1&item_name[]=2&…). However, if I dump out the post array in my processing script, I get the following:

    Array
    (
    [item_list] => Array
    (
    [0] => 4
    )

    [_] =>
    )

    with the value of the zero index corresponding to the id of the last element in my list. Everything else seems to work fine, but I can’t get the backend working because the serialized list is fubar. Anyone have any ideas?

    Thanks,

    /L

  23. Jacob Says:
    November 22nd, 2006 at 3:04 pm

    Larry,

    try using the 1.6.4 version of scriptaculous! the new version seems to have a bug there… have u noticed you cant get onto the script.acul.us wiki today even? 500 server errors all day long!

    .j

  24. Martin Thielecke Says:
    November 25th, 2006 at 2:40 am

    Hello Larry,
    i got the same error. With script.aculo.us 1.7 beta 1 or later the bug is fixed.

  25. Larry Says:
    November 25th, 2006 at 4:40 pm

    Hi Giannis, Jacob, and Martin:

    I was actually able to find a solution to the problem…after several days of searching, pulling out my hair, etc. Anyway, in the “updateOrder” function, change

    “parameters : Sortable.serialize(’imageFloatContainer’)”

    to

    “postBody : Sortable.serialize(’imageFloatContainer’)”

    and everything works fine. This is specifically for v. 1.6.5 of the script.aculo.us libraries.

    HTH,

    /Larry

  26. Nicklas Says:
    November 25th, 2006 at 6:42 pm

    Thanks Martin.
    1.7 beta was a relief. Thought I was going nuts there for a while.

    I add myself to those wondering:
    Has anyone made an ASP clone on the updating script?

    I can’t seem to catch the array - with request(”item_list”) - in the proper way.

    Cheers,
    Nicklas

  27. Nicklas Says:
    November 26th, 2006 at 2:58 pm

    Got it.

    I usually don’t do things in ASP so I stumbled for a while.
    Anyway, here’s how I got it to work in Classical ASP.

    Call .asp instead of .php file in the updateOrder function:
    new Ajax.Request(’server_items_reorder.asp’, options);

    Then this is my clone of Tim’s update-order-script in the file: server_items_reorder.asp

    – start VBscript
    Dim myString, myString2, myArray
    myString = Request(”item_list”) ‘get the string/list by Request
    myString2 = Replace(myString, “&”, “”) ‘first replace the ampersangs in recieved string
    myArray = Split(myString2, “item_list[]=”)’secondly split the string with delimiter “item_list[]=” and put into array - NOTE: first index in array will be empty since splitted string starts with delimiter

    ‘open database connection
    Dim sConnection, objConn , objRS
    sConnection = “DRIVER=…” ‘put your connection parameters here
    Set objConn = Server.CreateObject(”ADODB.Connection”)
    objConn.Open(sConnection)

    ‘loop through the array of items - NOTE that i=1 since first index in array is empty
    For i=1 to UBound(myArray)
    objConn.Execute(”UPDATE items SET position=”&i&” WHERE id=”&myArray(i)&”") ‘update record
    Next

    objConn.Close
    Set objConn = Nothing
    – end VBscript

    You can probably embrace the “item_list” in a more beautiful way and it would be great if anyone could submit a more clean solution.

    /Nicklas

  28. MertonKnight Says:
    December 3rd, 2006 at 7:16 am

    Here’s an ASP .NET v2.0 solution for the update page requested by Abraham Andres Luna. All the code gets placed in the server_items_reorder.aspx page load event.

    ‘Place data source code here. The next 2 lines are an example
    ‘Dim ds As SqlDataSource
    ‘ds = New SqlDataSource(”Data Source=localhost;Initial Catalog=OrderDB;Integrated Security=True”, “SELECT * FROM [test]”)

    Dim i As Integer = 1

    Dim values() As String = Request.Form.GetValues(Request.Form.AllKeys(0))

    For Each value As String In values
    ds.UpdateCommand = “UPDATE test SET position=” + i.ToString + ” WHERE id=” + value
    ds.Update()
    i = i + 1
    Next value

  29. Marcus Says:
    December 15th, 2006 at 11:32 pm

    I’m having problems with that I think are with the server_items_reorder.php page.

    Using the debug line suggested by Tim my serialized string looks like this:

    item_list[]=2&item_list[]=4&item_list[]=3

    (the 2,4 and 3 of this correctly reflect the new order of my list)

    however when the database is updated after a few times it has set the position field for all of the items in the list to 1. This is my sql:

    $i=1;
    foreach($_POST[’item_list’] as $key => $value) {
    $sql = “UPDATE Articles SET List=’”.$i.”‘ WHERE Article_ID=’”.$value.”‘”;
    $db->query($sql);
    $i=$i+1;
    }

    Any ideas?

  30. Tom Says:
    December 18th, 2006 at 1:40 am

    How would I go about doing the exact same thing but with items in a table instead of an unordered list? I have a table of images (a slideshow) that I want users to be able to reorder. I have followed this tutorial, and the result works great, but now I want to do it for a table. Any ideas?

  31. Steve Donnelly Says:
    December 19th, 2006 at 10:58 pm

    This is sweet! I got it working within a few minutes. Only issue I had was that although you only have to include the two .JS files, you have to have a whole lot more present in the same directory (builder.js, controls.js, dragdrop.js, effects.js, slider.js and unittest.js I think). Anyway it works and I will be setting up the ASP page to do the database part shortly, that part should be a breeze.

  32. Tim Says:
    December 19th, 2006 at 11:03 pm

    Hi Tom, The only way I have got tables to work so far is by placing an individual table in each list item. It sucks, but it is the only way that I know of to make a more advanced layout for each row. The only other alternative is to factor is some advanced CSS.

    Hi Steve, check some of the previous comments if you need any pointers on ASP. I know a couple of posters have worked through the ASP options. If you make any new discoveries, please share them here.

  33. Marcus Says:
    December 20th, 2006 at 2:01 pm

    I fixed my problem and for the benefit of anyone interested here’s how:

    The serialized string wasn’t being read as an array by the php problem. So I converted it to a comma separated list using the str.replace() function, then exploded that into an array in the php function.

    Not the prettiest code you’ll ever see but at least it works!

  34. Steve Donnelly Says:
    December 20th, 2006 at 9:26 pm

    I got this working with ASP for anyone who wants the code. I started with the code provided by Nicklas but I found that the request variable name was different (Request(”item_list[]”) instead of Request(”item_list”)) and the list was comma-delimited. The code is below. Also for those who were asking about using a table instead of a list, I am using a list but it has images in it and looks like a table just by using this CSS style:
    .sortlist {
    background-color: #EEEEEE;
    display: inline;
    padding: 2px;
    float: left;
    margin-top: 0px;
    margin-right: 5px;
    margin-bottom: 5px;
    margin-left: 0px;
    border: 1px solid #999999;
    list-style-image: none;
    list-style-type: none;
    }

    This way each image and its title are in a grey box which makes it easy to see what you are dragging. To use this CSS style just do something like this in your HTML:

    ” class=”sortlist”>

    Image Description

    …

  35. Steve Donnelly Says:
    December 20th, 2006 at 9:33 pm

    Sorry my previous post got messed up because of the inclusion of HTML tags. To use the CSS style for the UL to make it look like a table use this HTML:

    <ul id="item_list">
    <li id="item_123" class="sortlist">
    <img src="image.jpg">
    </ul>

    Here is the ASP code:

    <!–#include virtual="/include/dbOpen.asp"–>
    <%
    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

    ‘loop through the array of items
    For i=0 to UBound(myArray)
    Conn.Execute("UPDATE tablename SET [position]="&(i+1)&" WHERE IDfield="&trim(myArray(i))) ‘update record
    Next
    %>
    <!–#include virtual="/include/dbClose.asp"–>

    Then just make sure you include “order by [position]” at the end of your SQL whenever you request the data from this table.

  36. Steve Donnelly Says:
    December 20th, 2006 at 9:35 pm

    OK one more time, here is the CORRECT html… (forgot to include sample text and closing LI tag)

    <ul id="item_list">
    <li id="item_123" class="sortlist">
    <img src="image.jpg"><BR>
    Some text here if you want
    </li>
    </ul>

  37. Sven Ivar Stakseng Says:
    January 12th, 2007 at 10:48 pm

    Fantastic… have been looking for something like this for a long time for my cms… thanks a lot!

    Greetings from Norway.

  38. tiger Says:
    January 20th, 2007 at 3:48 pm

    i am trying to get this to work and for some reason it isn’t updating the DB. I am using asp. Is there some way to set alerts or response.write in the asp file so i can see if the error is occurring when the new ajax.request is called or if it is even making it into the asp page??? Any help would be greatly appreciated!

    Thanks….

  39. Anthony Says:
    January 23rd, 2007 at 7:05 pm

    i am attempting to update a database when the drag n Drop occurrs like Nicklas November 26, 2006 @ 2:58 pm posts. I am having a problem cause it isn’t updating the database. anyway to debug to see what is happening in the asp? my code matches the post. any help would be greatly appreciated

  40. darth Says:
    February 1st, 2007 at 12:08 pm

    Hi -I’ve got this working for ASP - so if anyone needs a hand - drop me an email at :

    mtm81 (INSERT AT SYMBOL) hotmail . com

    and I’ll help as much as I can.

  41. John Prado Says:
    February 5th, 2007 at 10:11 pm

    Thanks a lot for this. It solve a huge problem in my CMS, my customers start do shout loud about the problems with the nasty Ajax.Net

    Using this the problems has gone forever

  42. Tara Says:
    February 6th, 2007 at 12:11 am

    Thanks so much for the tutorial! I’ve got everything up and running (thanks to you) and am thrilled with the results.

    With that said…does anyone know how I can go about refreshing the parent window once the Ajax.Request is finished executing?

    Basically I’d like to do something like the following once the new order has updated to the database:

    window.opener.location.href=”../gallery_cms.php”;

    (I’m using this drag & drop tool to reorder thumbnails from a popup admin window and would like to see the results in the parent (gallery page) window without having to manually hit refresh)

    Unfortunately, when I try sticking this in the updateOrder function…it reloads the parent…but not with the new database order.

    I was trying to research the onSuccess or onComplete Ajax options…but I’m new to all this and didn’t really understand it.

    Thanks!

  43. Tara Says:
    February 6th, 2007 at 12:26 am

    Scratch my last comment. I was able to do it with the following (I realize it is probably clunky and messy)

    function mySnip() {
    window.opener.location.href=”../gallery_cms.php”;
    }

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

    new Ajax.Request(’reorder.php’, options);
    setTimeout ( “mySnip()”, 1000 );
    }

  44. Ross Says:
    February 9th, 2007 at 1:57 pm

    This might help a few people scratching their head….

    new Ajax.Request(’http://www.somewebsite.com/update.php’, options);

    This won’t work for IE6.1 unless you have the www. at the front of the URL. It works fine with Firefox with or without it.

  45. Steven Says:
    February 26th, 2007 at 7:47 pm

    “On December 20, 2006 at 2:01 pm Marcus” posted about using str_replace to deal with a problem in the update script. I seem to be having the same problem, but I can’t figure out how to either (a) fix it or (b) even see what is being posted to the update script. If you’re out there Marcus, can you post your code? Otherwise, can Tim or someone else suggest a way to view the incoming array on the PHP update script?

  46. Steven Says:
    February 26th, 2007 at 8:10 pm

    Updated to Scriptaculous 1.7.0, and it’s working now.

  47. Mark Says:
    February 26th, 2007 at 9:30 pm

    Hey folks,
    Exactly how is this script passing the id for each item?

    Mark

  48. Zafer Says:
    March 12th, 2007 at 12:30 pm

    is there a possibility to get example files for a sortable photo gallery ?

  49. Zafer Says:
    March 12th, 2007 at 4:58 pm

    hi @ all

    i’ve tried it and it works fine with images
    but if i put every image in a div i can’t saveing the new order into the database

    div.main { width:800; height:800px; }
    div.container {
    background: #7D7D7D url(”../img/l/picset_bottom.gif”) no-repeat bottom center;
    width: 200px;
    height: 271px;
    float: left;
    margin: 0 4px 24px 4px;
    }

    connect($db);
    ?>

    query($sql);
    while($row = $auth->fetchAssoc($result)) {
    echo “”;

    }
    ?>

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

    //

    thank you for you support

  50. sabaz Says:
    March 13th, 2007 at 6:03 pm

    what should i change in the javascript if my image looks like these:

    div
    div a img /a /div
    div a img /a /div
    div a img /a /div
    /div

    cant save the new order into the database
    thanks

  51. Sujith Says:
    March 22nd, 2007 at 7:11 pm

    Hello,

    I am facing a problem with AJAX.I have one PHP script in which I used AJAX.This AJAX is of type drag and drop.If we drag one item from a specific category and drop it in between two items of similar categories or diff. category it is not working correctly when I load the page first time, but after that it works correctly.When I select any item to drag the replica of same item appears after third item from the selected item and then it is dropped at its original position.This happens for each time only when I select the item first time after loading the page.This happpens with IE but the same code works correctly in Mozilla.My IE version is 6.0.2800.1106.So please send me a solution for this.

    u can see this in this link,
    http://hollywoodleague.com/cwl_dev/
    login with
    user id : sujithegr8
    password: password
    if this is not working, then just register one yourself..

    thesn select a league from the drop down and click on the “draftpool” link in th right side.
    Now u try to drag and drop, u can see the problem
    But if i set the “check for newer versions of stored pages ” as “every visit to the page” in the IE options
    (tools->Internet Options->General->settings), then its working properly in the IE also.
    please send me a solution for this.

    Regards
    Sujith

  52. Matt Evans Says:
    April 11th, 2007 at 7:17 am

    Hello,

    This script looks excellent. I really want to get it working.
    I havent even managed to get the JS drag and drop working.

    The error I am getting is [”Sortable” is null or not an object.]

    Can you explain this? or help me in any way?

    I have loaded scriptolious into the head of my document, all paths and correct, it just doesnt seem to work.

    Im using IE 6.0

    Thanks,

  53. Seb Says:
    April 17th, 2007 at 11:20 am

    But there is a problem with the PHP part where you are generating the ids for the update statement.
    Imagin in you DB the ids are not continuously cause some was deleted…
    The script is not working anymore.
    Im thinking for a resulution all the time, but the only way I see, is to get 2 parameters back from the javascript serialize funktion: the id (old value) AND the new value…

  54. Christophe Says:
    May 10th, 2007 at 12:45 pm

    Seb,
    I need to test tonite and re-create the situation you are describing (situation of IDs not being continous due to deletion), and see if this really causes a problem. I have been thinking it but have not yet tested. If this is really a problem, did you find a solution yet ?. I know not enough ajax/javascript scripting to implement 2 parameters like you suggested. But the other idea I had was to get PHP to renumber the POSITIONs/IDs in the database everytime an item is deleted. A bit heavy on the server maybe, but it should work (and on my site, deletion will sometimes happen, but should not be frequent). My idea is, after the deletion query in MYSQL, I would immediately run another MySQL query which would take all the records one by one (order by POSITION ASC) and replace the POSITION by an incremental number. That way, you always have nice continous IDs, even if you delete the one in the middle…
    What do you think ?? (but then again, maybe this actually is a non-issue!). Thanks. C

  55. Luke Mackenzie Says:
    May 17th, 2007 at 10:18 am

    i’d like to change the colour of the item being dragged while it is dragging. any idea how to do that? i;ve tried using onChange and hoverclass but not having much luck. i’m already using ghosting but this isnt giving me enough contrast.

  56. Deacon Says:
    June 21st, 2007 at 12:55 pm

    Thanks so much for a really clear tutorial. Tweaked it slightly for what I needed but it sent me so far down the right road I had it done in a couple of hours. Good work fella!

  57. Mark Says:
    August 26th, 2007 at 3:06 pm

    Here is the opposite way to run the PHP code for those of you who would like to use DESC instead of ASC. I also left a print function in there for testing, you can view what the PHP does using Firebug. Very handy.

    include(”database.php”);

    //I used server_items_reorder.php?table=mytable so I could reuse this page
    $table = $_GET[”table”];

    //get Max value of table row position
    $sql = ”select max(position) as position from $table”;
    $res = mysql_query($sql);
    if ($res) {
    $row = mysql_fetch_array($res);
    $maxDisplay = $row[’position’];

    } else {
    echo mysql_error();
    }

    //loop through all rows to update position
    $i = $maxDisplay;
    foreach($_POST[’item_list’] as $key=>$value) {
    mysql_query(”UPDATE $table SET position=’”.$i.”’ WHERE id =’”.$value.”’”);
    print ”i is: $i and value is: $value\n”;
    $i–;
    }

  58. Mark Says:
    August 26th, 2007 at 3:09 pm

    I think the end of my code got messed up. It’s supposed to be $i(minus)(minus) instead of $i(plus)(plus). Or just $i=$i-1;

  59. christian volta Says:
    September 5th, 2007 at 8:36 pm

    tim, thanks great script…everything works great until the DB update script. I am using ColdFusion7 and do not know how to update your DB code to match properly. I do not know what characters in your code are PHP dependant and therefor need to extract.

    Could you please contact me to help, I will be willing to pay you for your time since I know you do not usually code in CF.

    Thanks!

    CEV

  60. mitt Says:
    September 21st, 2007 at 5:49 pm

    “Output your database records into an unordered list”

    how does your php code look for calling this list out of the database? can you post that? thank you very much.

  61. Jim Says:
    September 25th, 2007 at 8:16 pm

    Tim - Quick question: I have completed step three, and when I test it in Firefox (with the Firebug debugger running) I get the error message “too much recursion” repeated about 6 times. I am using your code exactly, with the exception of the the actual data pulled from the database to populate the list. But the list looks exactly the same as your example, with the same id and li id names.

    Why does the “too much recursion” error happen?

    Any ideas?
    Thanks!
    Jim

  62. Tim Says:
    October 1st, 2007 at 4:11 pm

    Mitt:

    I would use a database query then a while statement:

    while($row=mysql_fetch_assoc($rs)) {
    echo “

  63. ” . $row[’name’] . “

    64. “;
    }

    of course this is simplified and you may want to use some htmlspecialchars or other formatting on the output.

  64. Tim Says:
    October 1st, 2007 at 4:12 pm

    Jim,

    Can you show me your code somewhere and I will see if I can diagnose the issue.

  65. WiseGuysOnly » Drag and Drop reordering of database fields (sortables) with Mootools Says:
    October 16th, 2007 at 11:26 am

    […] recipe follows on from the work done in my Scriptaculous Drag and Drop recipe, only using Mootools an alternative Javascript library that I have been using lately. I am not […]

  66. Andrew Says:
    November 8th, 2007 at 8:15 am

    Excellent script. It’s short and simple. I have been all over the Internet looking for scripts and tutorials that teaches how to save from a sortable list and theirs was 4 to 6 pages long and too complicated. Thank you so much again for the script. It is exactly what i have been searching for.

  67. John Says:
    December 13th, 2007 at 7:07 am

    This is great! However, do you have any information on how to get this to work with multiple lists on the same page? In paricular, it would be great for a ‘tree’ type list.

  68. JJ Says:
    February 4th, 2008 at 4:41 am

    Any reason why this would be working in Firefox & Safari but not IE 7 or Opera?

  69. knowledge chikuse Says:
    February 15th, 2008 at 8:27 am

    i tried Nicklas’s solution and it was cool, but didnt seem to work. i had to modify my javascript to remove the list[]& data

    var listorder = Sortable.serialize(’list’);
    listorder = js_str_replace(”&list[]=”,”,”,listorder);
    listorder = js_str_replace(”list[]=”,”",listorder);

    then send listorder via AJAX. the asp used to process it is like so

    list = Request(”list”)
    myArray = Split(list,”,”)
    Set objConn = Server.CreateObject(”ADODB.Connection”)
    objConn.Open(MM_conn_aic_STRING)

    For i = 0 to UBound(myArray)
    objConn.Execute(”UPDATE tblPhotographers SET sortorder=”&i&” WHERE id=”&myArray(i))
    Next

    objConn.Close
    Set objConn = Nothing

    hope that helps someone.

  70. knowledge chikuse Says:
    February 15th, 2008 at 8:28 am

    oops. the js_str_replace() function is just my javascript copy of php’s useful str_replace()

    function js_str_replace(replaceChar,replaceWith,tString){
    for(i=0;i<tString.length;i++) tString = tString.replace(replaceChar, replaceWith);
    return(tString);
    }

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Login