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!!!!
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).
"The fear of the LORD is the beginning of knowledge..."
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.
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.