SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Thoughts on reording part of a table...

    I keep track of the order in a table with field called 'the_order' and it has integers that increment as you add content. However, when I need to reorder a record using a custom CMS I end up moving 'the_order' number to whatever position and UPDATE all items below that up one.

    Is there a better solution than this? If you end up having 1,000's of records you can tie up the table. I wondered if anyone had a quicker method.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    One trick is to number them 10,20,30,etc..
    That way if you want to reshuffle them you can order them as 10 ,15, 20.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    are you issuing an individual update statement for each row, or are you doing something like
    Code:
    update datable
       set the_order = the_order + 1
     where the_order > 11
    ?
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  4. #4
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ya...that's how I would be doing it. I was just thinking that there would be a delay for thousands of records though.

    Just did a test on 42,739 records and it did it in 1.89 seconds. Not bad.

    Just wondering if there was a quicker way.

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    eruna's idea is a good one.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i used to suggest the same thing (have done, several times, here on sitepoint), except my values were 100, 200, etc.

    the drawback is that it leaves open the possibility that you might some day need to re-order the order numbers

    well, the way around that is to use FLOAT for the order numbers

    that way, you can always insert a number in between two existing numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    floating might be best. Using whole numbers ...you'd potentially run out quickly. If they were 100 apart then you move one between 100 and 200 it'd be 150. Then doing it again it'd be 125, then 112, 106..103,102,101 and your done. You couldn't move it anymore. With floating you'd have more but technically it would end because it floats only so much.

    You could do the idea above and then run a cron job at night or something to flaten out the order again but that seems tedious and unnecessary.


Bookmarks

Posting Permissions

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