SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Renumber row in sequential order using PDO

    lI have a table with an ID column which is the Primary key and auto incremented. I have another column named ORDER that contains a number. This number is used to define the order in which the row data is displayed on a page.

    When I delete a row, the ORDER column will have a gap and I would like to renumber the subsequent rows to remove the gap. ie:

    I want to renumber the rows:
    ID ORDER
    1 1
    2 2
    4 3
    5 4

    When I insert a new row I want the ORDER row to be given the NEXT sequential number

    How do I do that using PHP PDO?

    Thanks

  2. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    The next sequential number as in leaving the gap, so if you remove ID=4, you just put it at 5 anyways?

    Or the next number as in filling the gap, so if you remove ID=4, you should get 3 for order?

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    The next sequential number as in leaving the gap, so if you remove ID=4, you just put it at 5 anyways?

    Or the next number as in filling the gap, so if you remove ID=4, you should get 3 for order?
    If you delete ID 4

    You have
    ID ORDER
    1 1
    2 2
    3 3
    5 4
    6 5

    I need it to reorder starting at 1... ie 1,2,3,4,5,6

  4. #4
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    You need ID to reorder?

    If that's the case, I'll warn you now: that is a BAD idea. You never ever want to change your id value.

    Also, if you recorder them, doesn't that mean ORDER always equals ID, so it doesn't really matter?

    Can you elaborate a bit more on what you're trying to do. There is likely a better way to do what you're trying to accomplish.

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    You need ID to reorder?

    If that's the case, I'll warn you now: that is a BAD idea. You never ever want to change your id value.

    Also, if you recorder them, doesn't that mean ORDER always equals ID, so it doesn't really matter?

    Can you elaborate a bit more on what you're trying to do. There is likely a better way to do what you're trying to accomplish.
    No, not the ID, that stays the same, the ORDER row will be renumbered... It wont be the same as the ID because ID may be deleted

  6. #6
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Okay, now that I know the question. =p

    The simplest way to do this would be to do a SELECT query and get all of the elements, ordered by ID. Then just go through and do an UPDATE on each one, changing the ORDER to an ever incrementing value.

    However, there are two potentially better ways to do this:
    - If the ID and ORDER are always related, then why have order? Just arrange stuff based on ID.
    - When you remove a single row, you could do an UPDATE SET ORDER = ORDER - 1 WHERE ORDER > removedOrder.

    If I knew more what you were doing with this I could help a bit more.

  7. #7
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    Okay, now that I know the question. =p

    The simplest way to do this would be to do a SELECT query and get all of the elements, ordered by ID. Then just go through and do an UPDATE on each one, changing the ORDER to an ever incrementing value.

    However, there are two potentially better ways to do this:
    - If the ID and ORDER are always related, then why have order? Just arrange stuff based on ID.
    - When you remove a single row, you could do an UPDATE SET ORDER = ORDER - 1 WHERE ORDER > removedOrder.

    If I knew more what you were doing with this I could help a bit more.
    The idea is that the user enters a number to determine the order of the images (ORDER) so the ID will not be in any specific order,

    ID ORDER
    10 1

    The 10th ID would be in first postions

    So I need to Select the query in order of ORDER ASC then renumber ORDER 1,2,3,4,5, etc..

  8. #8
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    That's what I figured. In that case, this:
    - When you remove a single row, you could do an UPDATE SET ORDER = ORDER - 1 WHERE ORDER > removedOrder.

    Is how you would want to do it. It would just shift everything down to fill in the gap you just created, preserving the order but giving you no gaps.

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    A better solution would be to not store the order in the database at all but to generate that field as you read the records. That way the order values are automatically updated when a record is deleted.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    That wouldn't work with user-controlled ordering though... would it? If it would, can you elaborate?


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
  •