My db table appears like this pic. http://prntscr.com/22z1n
Lately I have produced remove.php page. it really works correctly however when i erased 21th user next registered user will get 24th id rather than 21.
Can you really put recently registered customers info to first empty row? (In cases like this 21th row)
During my registration form, recently signing up user can write names of existing customers, and become buddies together after registration. With this friendship i've another table that affiliates id of recently registered user and existing user.
For this function i am using mysql_place_id throughout registration to obtain id for brand new user. But after deletion of 21th row throughout nex registration process mysql_place_id provided number 21. but saved in 24th row. And offer associations table 21 for brand new user. I wanna solve this issue
If you use an autoincrement id column, the worthiness the next entry is going to be designated won't be reduced by removing an entry. That's not how much of an autoincrement column can be used for. The database engine will invariably increment time on the new place rather than decrement time on the remove.
auto_increment column keeps several internally, and can always increment it, despite deletions. If you want to complete a clear space, you need to handle it yourself in PHP, instead of make use of the
auto_increment keyword within the table definition.
Moving to complete empty row ids may cause a variety of difficulty for those who have foreign key associations to keep, also it really is not advised.
auto_increment could be totally reset utilizing a SQL statement, but this isn't advised since it may cause duplicate key errors.
-- Doing this will cause problems! ALTER table AUTO_INCREMENT=12345;
EDIT To enforce your foreign key associations as referred to within the comments, you need to supplment your table definition:
FOREIGN KEY (friendid) REFERENCES registration_table (id) ON DELETE SET NULL;
Complete the right table and column names. Now, whenever a user is erased in the registration, friends with them association is nulled. If you want to reassociate having a different user, that needs to be handled with PHP.
mysql_insert_id() is no more useful.
If you want to discover the greatest designated id still within the database after deletion to connect with buddies, make use of the following.
SELECT MAX(id) FROM registration_table;
Auto increment is really a sequence key that's monitored included in the table. It doesn't return whenever you remove a row.
Easily, no. You skill (however i don't suggest doing) is making an SQL function to look for the cheapest number that is not presently occupied. Or produce a table of IDs which were erased, and obtain the littlest number after that. Or, which is the very best idea, disregard the gaps and realize the database is okay.
What for you to do is quite possible with the addition of an additional column for your table known as something similar to user_order. After that you can write code to handle card inserts and deletions to ensure that this column is definitely consecutive without any gaps.
By doing this you steer clear of the problems you might have playing around by having an auto_increment column.
It's a bad practice to totally reset auto_increment value, but when you will need to get it done, so that you can:
ALTER TABLE mytable AUTO_INCREMENT = 1;
Run this question after every remove. Auto_increment value won't be set to
1, this can set the cheapest possible value instantly.