Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder darksecu's Avatar
    Join Date
    Dec 2011
    Location
    India
    Posts
    99
    Thanks
    11
    Thanked 1 Time in 1 Post

    Reordering Tables Id's

    I have 1 table table_name ..
    i am allowing users to edit and delete rows ..

    i need to reorder table according to table id if middle values are missing..

    supposing i have table with these id
    1
    2
    3
    6
    7
    8
    9
    11

    i want to update table and make id in same interval of 1, like this

    1
    2
    3
    4
    5
    6
    7
    8

    Could Not Express Properly How To Ask This Question, I Hope You Understand :P
    Last edited by darksecu; 12-06-2012 at 02:32 PM.
    When You Begin To Practice,That's The Time You Begin To Learn New Things.

  • #2
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,327
    Thanks
    60
    Thanked 525 Times in 512 Posts
    Blog Entries
    4
    If that is a primary key or a foreign key to another column in another table then do not reorder them as it will break your program.

    If it's auto_increment then the same applies. Don't recycle id numbers because thats how records with other tables can become corrupt (unless you renumber everything that relates to it - a big task).
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #3
    New Coder darksecu's Avatar
    Join Date
    Dec 2011
    Location
    India
    Posts
    99
    Thanks
    11
    Thanked 1 Time in 1 Post
    nothing is related with those tables.
    i have only single table, using it to store some records.

    Anyway to rearrange and update them ?
    When You Begin To Practice,That's The Time You Begin To Learn New Things.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Are you referring to a table's surrogate key? If so, then simply don't. It will cause more headaches than its worth to reuse keys than any benefit of reordering them especially if cascading relationships are not set up.
    Of course you can just not use surrogates and apply an actual property for the primary key instead.

    If you refer to something within the output, than that's no problem. Building a table with an incrementing column value is as simple as using a print ++$i; in it. Although that likely isn't the case here since you will only see a problem if you are referring to a PK or if JS is used to remove in which case it then becomes JS' job to worry about.

    Edit:
    ahh, beaten this time.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by darksecu View Post
    nothing is related with those tables.
    i have only single table, using it to store some records.

    Anyway to rearrange and update them ?
    Yep, manually change every number above the deletion down, or copy the entire table without the PK's into a copy of the same table and rename.
    Why you want to do it is completely beyond me.

  • #6
    New Coder darksecu's Avatar
    Join Date
    Dec 2011
    Location
    India
    Posts
    99
    Thanks
    11
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Fou-Lu View Post
    Yep, manually change every number above the deletion down, or copy the entire table without the PK's into a copy of the same table and rename.
    Why you want to do it is completely beyond me.
    Thanks,

    Looping Tables Row With ++1; And Updating ++1 As It's ID
    When You Begin To Practice,That's The Time You Begin To Learn New Things.

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Quote Originally Posted by darksecu View Post
    Thanks,

    Looping Tables Row With ++1; And Updating ++1 As It's ID
    I assume you mean you are decrementing and not incrementing. If you delete a record and you want to fill the "gap" it creates, you need to decrement every record > the deletion key down by one. You can do that in a single query without the looping.
    At least I'm pretty sure you can manually alter the primary key; I haven't used a surrogate in a quite awhile now. Oh and after your done you'll need to reset the key to the new location as well otherwise it will happily continue with wherever it left off.

  • #8
    New Coder darksecu's Avatar
    Join Date
    Dec 2011
    Location
    India
    Posts
    99
    Thanks
    11
    Thanked 1 Time in 1 Post
    Oops :P..
    Clear Now

    Thank's Again
    Last edited by darksecu; 12-06-2012 at 04:12 PM.
    When You Begin To Practice,That's The Time You Begin To Learn New Things.


  •  

    Posting Permissions

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