Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    16

    Unanswered: reordering elegantly...

    Hello Forum

    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:


    if($oldOrder<$newOrder){
    $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";
    }else if($oldOrder>$newOrder){
    $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?

    Any insight appreciated.

    Many thanks

    stephank

  2. #2
    Join Date
    Aug 2007
    Posts
    16
    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 :
    0,1,3,6,6

    How would I easily turn this into:
    0,1,2,3,4

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

    Can this be done in mySQL?

    Or is there a more elegant way in php?

    Thanks

    stephank

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    more elegant way? consider allowing gaps, in fact, consider assigning the order numbers in increments of 100, say

    so if you have 100, 200, 500, 900 and you want to move the last entry "in between" the first two, you end up "renumbering" only one row, instead of most of them -- 100, 150, 200, 500
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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).

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by aschk
    ...
    cursor
    ...
    Evil...
    I guess the solution depends on the number of records in question...
    But here's my stab at it anyhow
    Code:
    DECLARE @NewNum int, @OriginalNum int
    SET @NewNum = 5
    SET @OriginalNum = 8
    
    UPDATE MyTable
    SET OrderNum = OrderNum + 1
    WHERE OrderNum >= @NewNum
    
    UPDATE MyTable
    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?)
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •