Resorting id field in database after deleting data

Question

I have a problem. I created a database (my_database), created a table inside it(members), and created fields (id, name, lastname, address). The id field is set to auto-increment. Now I have 5 rows, which means id has a value of 1, 2, 3, 4 and 5. When I delete id 3, then what's left is '1 2 4 5'. My question is what should I do to resort '1 2 4 5' to '1 2 3 4'? Any help would be much appreciated.

Update: I have to do this resorting because on my site I am displaying all the members using loop and accessing the id number. Or is it even possible to get the id of a data? Example I have data this data: id=2, lastname=clyde, address=switzerland. How can I get the id value just by the lastname value?

Answer 1

You really don't want to do that for a number of reasons.

First off, things might go wrong if a page is open. Let's say a user is on the edit page of the entry with id = 4 when you delete the entry with id = 3 and update the ids as you suggest. Now, when the editing person hits submit, the id will be taken from his page and he will be updating the new entry 4 instead of the old entry 4. Now this is a blunt and simple example, but beyond those there are lots more things that can go wrong at this level.

Secondly, there is a lot of work involved when programming this. First, you need to update your columns. Then, you also need to make sure MySQL knows what number to give the next entry. And then all of that has to work in an environment that makes sure we don't get quite as many problems with the point mentioned above (which is quite a hard thing). This is a lot of work compared to the alternative of not doing anything.

The third problem is that there is a huge performance overhead. Imagine having a database with thousands upon thousands of entries, and then removing an entry with a low id. Suddenly, all entries with higher ids have to be updated. This might well mean that your site becomes unresponsive because it is doing this task and can't handle too much else at the same time (in fact, in order to make sure that we don't get problems like in the first point, we have to make sure that we don't do anything else at the same time (or make sure we work on different copies of the data or something) because we could end up with a result that comes from during this whole update process.

My suggestion would be in line with what others are saying: just leave it as it is and do not worry about this. auto_increment is meant for just one purpose: giving each value a unique identifier easily. Use this identifier to identify and refer to the same entry only. Perhaps one could also make a case about sorting on these identifiers, but no further than to have a certain order (and even then people will disagree with this use of it).

Instead of trying to update the ids, we should look for another place to solve this problem. If the problem is purely how you feel about it, that's easy. You may not feel good about it, but you just need to convince yourself that updating all those ids is not the solution you are looking for. If you use the numbers elsewhere, the problem can be a little more complex to solve. However, there is always the possibility of using PHP to generate numbers for each entry, which most definitely is the logical place to do so if the numbers are used in the generating of your html content. If you provide more details about where you use the sequential numbers, a look could be taken at how to solve it in that case.

Answer 2

You can either manually update id value by the following statement :

update members set id = (id-1) where id > 3;  

or my advice would be to leave as it is. Its not causing any ambuiguity.

Answer 3

You could do an UPDATE query but mostly developers just leave the id's as is. You can do the numbering in the PHP if it's important to you.