CODEWITHSUNDEEP
phpmysqlforms
Noobeske
Noobeske

Reputation: 11

Auto Increment ID in mysql and PHP

I'm facing a problem with the auto-increment ID in MySQL and PHP, here's the scene, I created a form that inserts the data to my database. I use a table to see all the data I stored from the form in the .php file. I stored 5 rows of data, when I delete the 3rd row, I noticed that the ID in the 4th row doesn't change to 3. Is there a way to set the Id to replace the deleted row?

Upvotes: 1

Views: 2379

Answers (1)

Zeth
Zeth

Reputation: 2578

It sounds like that you're using that ID in a way that you shouldn't.

As you can read here:

When you insert any other value into an AUTO_INCREMENT column, the column is set to that value and the sequence is reset so that the next automatically generated value follows sequentially from the largest column value. For example:

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');
SELECT * FROM animals;
+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

... So if it was because you are afraid that some value 'later on' (after the deletion of your 3rd row), will get an ID, that isn't the highest one, then you don't need to fear that.

Please update your question with further information on, why this is a problem, if this doesn't answer your question.

Upvotes: 4

Related Questions