#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    30
    Rep Power
    15
    Here is the senerio. I have 1 table (members) and an primary ID that is autoincrmenting. I rely on this ID to be in order i.e 1,2,3,4,5..... If I go and say do this

    Delete from members where (ID = 3);

    My order now reads... 1,2,4,5....
    Is there anyway to execute a specific MySQL command to make those numbers sync up after deleting the spcific row? Thanks!!!!
  2. #2
  3. .Net Developer
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2000
    Location
    London
    Posts
    987
    Rep Power
    15
    see, once you delete a row from the auto_increment field then you can't save that row number.

    ie , 1,2,3,4,5

    if delete 3rd row .that means 3rd row can't be reused.

    now your sequance will be 1,2,4,5.....


    If you delete the row containing the maximum value for an AUTO_INCREMENT column, then value will be reused(ie, last row).




    ------------------
    SR -
    shiju.dreamcenter.net

    "The fear of the LORD is the beginning of knowledge..."
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    36
    Rep Power
    15
    If you reorder the table then the relation ship between the id and the member will change, this will create a mess of your info. Instead of deleating the member I would add a field that would indicate the member status ie active or inactive. I do not know what the importance of having the data in order but you could have a seperate table that is in numerical order that referances the member table and instead of deleting info rows from this table delete the data and have a script that looks for vacant rows before adding a new row you could also do the same for your member table.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    30
    Rep Power
    15
    Thanks for the help everyone, but I found out what I should have been doing in the first place. Instead of doing the logic in my PHP code, I should have been doing it with my query. I ended up using the LIMIT command with mysql to return exactly what I needed. Then I was not relying on the AUTO_INCREMENTING ID for reading in input, but just to make changes.

IMN logo majestic logo threadwatch logo seochat tools logo