I am trying to build a drag and drop list in flash / php / mysql. I have the flash built and it always notifices with every drop the old and the new order in the list to the php app. It all works well so far ... here is what I'm using:
$query = "UPDATE CATEGORY_PROJECTS SET OrderNum = CASE WHEN OrderNum>'".$oldOrder."' AND OrderNum<='".$newOrder."' AND CategoryID='".$catid."' AND ProjectID!='".$projectid."' THEN OrderNum-1 WHEN ProjectID='".$projectid."' THEN ".$newOrder." ELSE OrderNum END";
$query = "UPDATE CATEGORY_PROJECTS SET OrderNum = CASE WHEN OrderNum<'".$oldOrder."' AND OrderNum>='".$newOrder."' AND CategoryID='".$catid."' AND ProjectID!='".$projectid."' THEN OrderNum+1 WHEN ProjectID='".$projectid."' THEN ".$newOrder." ELSE OrderNum END";
What I'd like to add is an additional loop checking whether there are any duplicates or gaps and a way to make sure that every OrderNum always exists only once... Is there a standard way to do this? or am I too paranoid?
I guess my last post was a little confusing. Basically I am just looking for an elegant way to fix a whole in the OrderNum Column. Let's say the CATEGORIES table has 5 categories with the following orderNum's :
How would I easily turn this into:
I guess I could sort it by OrderNum and then in php put it into an array and then Loop with $i through the array and do individual updates by setting the OrderNum=$i . But that seems like a terribly slow way (and probably also not very reliable, assuming there are 50 or 100 categories).
another way is by assigning order numbers as floats -- then you won't have to renumber any rows at all except the row that's moving, because in order to get a new order number, you just average the two numbers on either side, and the new float is guaranteed unique
You could use a stored procedure to grab a cursor of your resultset and iterate through it each time performing the required change to your number. Of course as you suggest this could be equally done in PHP. Unless you want to talk clever algorithms the loop approach is pretty much all that can be done to create an incremental set of numbers.
I'm not so sure that your solution is quite the answer Rudy, although I do like it and it certainly will have some good application. I think stephank was looking for a nice way just to reorder incrementally (in integer).
I guess the solution depends on the number of records in question...
But here's my stab at it anyhow
DECLARE @NewNum int, @OriginalNum int
SET @NewNum = 5
SET @OriginalNum = 8
SET OrderNum = OrderNum + 1
WHERE OrderNum >= @NewNum
SET OrderNum = @NewNum
WHERE OrderNum = @OriginalNum
EDIT: Hmm... This suggestion may leave gaps (but is that so much of a problem as long as they're in the right order?)