Search code examples
phpmysqlauto-increment

MySQL and AUTO_INCREMENT when deleting lines

I have a problem with my MySQL Database. I searched for a long time, but there was nothing, which could help me. I have a Database with three columns: id, username and password. For example there are 3 lines in the database.

1, test1, testpw1
2, test2, testpw2
3, test3, testpw3

When I delete line 2, how can I change the id (auto_increment) in the whole database?

Now it would be like this:

1, test1, testpw1

3, test3, testpw3

I want it to be like this:

1, test1, testpw1

2, test3, testpw3

Thanks a lot Jannes

Solution

  • What you do for that, is you create another column called "deleteFlag" and set it up as either a 1 or a 0 data value, and when your displaying data, make sure you include deleteFlag set as a 1....

    When you delete a row, you set deleteFlag to a 0. Thus when displaying the data, your code should be set to show only deleteFlags with 1.