problem with query for AJAX drag and drop re-ordering

@nemesis_256Aug 15.2008

So I’m doing one of those drag and drop re-ordering systems but I’m having problem with the query. It works fine if I drag an item only one row down, but if I drag it further down, the order gets messed up. So for example switching from 1-2-3-4 to 2-1-3-4 works, but going to 4-1-2-3 does not.

Here’s my query:
update schedule set sortOrder = ” . $newOrder[$count] . “, modified = 1 where sortOrder = ” . ($count + 1) . ” and modified = 0

the modified column is to make sure I don’t update the same entry twice. I set them all back to 0 after all updates are done. Any visible problems?

to post a comment

PHP

10 Comments(s) _↴

@nemesis_256authorAug 17.2008 — #Anyone? Still need help with this.

BTW, $newOrder is an array containing the new order of the items in the database. It's what I'm looping through.

@ayveghAug 18.2008 — #Instead of querying each time the row moves, why not wait until the mouseup event, when you know the user has completed moving everything, to query back with the new row order?

@nemesis_256authorAug 18.2008 — #That's actually what I'm doing. Querying every time the row moves would make this current query work all the time, but it seems risky because the user might move rows more quickly than the bandwidth can handle.

So I am updating the database only when the user lets go of the mouse button, but my query still isn't right...

@ayveghAug 18.2008 — #Canyou post more code than just the query? It's hard to understand what you're doing (and what might possibly be wrong) without seeing a bigger picture.

While you're at it, use the mysql_error() function to get some sort of feedback as to what is actually going wrong.

@nemesis_256authorAug 18.2008 — #Ok, I've got a list (what I'm trying to sort) that looks like this:

[code=html]
 <ul id="myList" class="myList">
 <li id="item_1">1</li>
 <li id="item_2">2</li>
 <li id="item_3">3</li>
 <li id="item_4">4</li>
 </ul>
 [/code]

Then using scriptaculous and prototype, I get the data and send it via AJAX to the PHP script. The url looks something like schedule.php?newOrder=1-3-2-4.

Then here's the entire contents of the schedule.php

[code=php]<?php
 $DOCUMENT_ROOT = $_SERVER['DOCUMENT_ROOT'];
 include_once("$DOCUMENT_ROOT/includes/db-open.php");
 
 $newOrder = explode("-", trim($_REQUEST['newOrder']));
 
 for ($count = 0; $count < sizeof($newOrder); $count++)
 {
 $query = "update schedule set sortOrder = " . $newOrder[$count] . ", modified = 1 where sortOrder = " . ($count + 1) . " and modified = 0";
 echo $query . "<br>";
 $results = $db->query($query);
 }
 $query = "update schedule set modified = 0 where modified = 1";
 $results = $db->query($query);
 
 ?>[/code]

I get an odd feeling that I'm not sending the right data or not setting it in the correct order. In the example I provided, 1-3-2-4 means 2 and 3 changed spots from the UL I provided as an example. A simple update like this works, but if I were to send it 2-3-4-1 (meaning I dragged the first item to be the last) the order is incorrect when I reload the page.

@ayveghAug 18.2008 — #From what I can tell, unless the first query has finished saving, the new query will not be changing from the second "saved" order - it will be reordering based on the first order.

Example:

Current order is 1-2-3-4.

I move 1 to the end, so the new order is 2-3-4-1.

The AJAX executes and the query is sent to the PHP script.

I decide I don't want it all the way down, so I move 1 up, so the new order (based on the [B]original[/B] order) is 2-3-1-4, and based on the [B]last query sent[/B], is 1-2-4-3.

The query is re-sent as 1-2-4-3, but meanwhile, the saved order is still 1-2-3-4, so the saved state becomes: Original: 1-2-3-4 New: 1-2-3-4

I.E. the original 1 doesn't get moved, instead the original 4 gets moved.

What you need to do is wait for feedback from the PHP script before allowing the AJAX to send another query, or just block the user from reordering until the server response is received.

Example logic:

Order changes > Send query

Order changes again > If the server responded with "OK", send a new query; If not, add a notification which is checked for when the readystate changes.

----------------------------------------

That all probably sounds a little skewered, but the basic idea is this: [B]Don't allow the order to be changed until the original order has been saved.[/B]

A good example of this is in WordPress's Widget organizer: You re-arrange to your hearts content, then click save.

Let me know if you got the idea, or you need more explaining/examples.

Cheers,

ayvegh

@nemesis_256authorAug 18.2008 — #That's not even the problem I'm having. I'm still trying to take care of the first call. I understand the problem you're explaining, but I'm not at that point yet. The problem I'm having is when dragging a row more than one space. This results in only one call to AJAX/the database, because the call is only made when you let go of the mouse.

Could you post a link to the wordpress widget organizer? I tried doing a search but couldn't find it.

@ayveghAug 18.2008 — #It's built into WordPress. WordPress uses the jQuery library, so you can find whatever they're using in there.

To see the organizer in action, open a free wordpress.com account, or install a copy yourself. Then go to the Admin panel, under Design, Widgets.

@Kyleva2204Aug 19.2008 — #You should use Scriptalicous and Prototype (its what I use.. it has never let me down)... Check it out here:

Scriptalicous Make Sortable

Then use prototype to make the ajax request, and set a parameter and set it to the Sortable.seralize and then unserializer on the PHP end to get the order..

Serialize

Heres a tutorial on it:

Tutorial

@nemesis_256authorAug 19.2008 — #I got it! It turns out my where clause was the problem. I was comparing the previous sort order but I really should have done it based on the ID. So my new query is:

$query = "update schedule set sortOrder = $count where id = " . $newOrder[$count];

Also in #PHP _↴

dynamically loading cities.[RESOLVED] question about PHP 5 Admin Script

Success!

Help @nemesis_256 spread the word by sharing this article on Twitter...

Tweet This

problem with query for AJAX drag and drop re-ordering

10 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

problem with query for AJAX drag and drop re-ordering

10 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

10 Comments(s) _↴

Also in #PHP _↴