Help with php and mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Help with php and mysql

Post 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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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
Post Reply