Page 1 of 1

Help with php and mysql

Posted: Wed Feb 26, 2003 3:31 pm
by nigma
In my mysql database users I have a table with 3 fields, ID, fName, lName. The ID field is what everything is sorted by and its auto_increment. Now lets say I fill up this table with these values:

0 Kris Ennay
0 Bob Barker
0 Tommy Hildude

The ids would be 1, 2, and 3. Now lets say I want to delete Tommy from the table so I get rid of his row. Now the data in table is:

0 Kris Ennay
0 Bob Barker

I then add a row, and that rows id turns out to be id 4?? Is there I way I can get the previous rows ID then increment it one and make that the new rows ID?

Thanks for all help and advice provided.

Posted: Wed Feb 26, 2003 4:02 pm
by volka
mysql (probably like all databases do) maintains a (more or less) hidden field for each table that contains an autoincrement-col. The number stored in this field is used to determine the next auto-id. But neither does mysql check for holes nor wether a DELETE took the highest current id.
The purpose of such a counter and an autoincrement-col is to provide a unique id for each row in a table regardless of other actions (like delete), do not play tricks on this ;)

btw: Not a client side question