Delete Problem With Id Id as auto_increment, and identifier for a row

[serialmike 0]

Whenever I use 'delete from table where id = 4' from a table with several rows up to 10, the 4th row is removed and the update simply shows the rest from 1 to 10 without 4. This is not what I want. I want the table to now read 1 through 4 to 9 so that futher delete actions will be in that sequence. I want to identify my row with the id (number). Can anybody help out here. Very urgent.Serialmike.

[shadowx 0]

The reason this happens is because you havent told the database to update the ID fields, you've only told it to remove the fourth row and do nothing else. I cant think of an efficient and simple way to solve this, there could be a built in function for it but i dont know of one. The only solution i can think of is to manually update each ID field that comes after the deleted row with a "-1" instruction that should result in each ID being in sequence and you'd have to use a loop to do this for each field which could be very long.

[serialmike 0]

The reason this happens is because you havent told the database to update the ID fields, you've only told it to remove the fourth row and do nothing else. I cant think of an efficient and simple way to solve this, there could be a built in function for it but i dont know of one. The only solution i can think of is to manually update each ID field that comes after the deleted row with a "-1" instruction that should result in each ID being in sequence and you'd have to use a loop to do this for each field which could be very long.

Shadowx,
You are very rigth in theory. I have thought along your idea, but it sounds complicated hence my coming to this board if there is a shorter approach to the problem. I still hope there is an inbuilt command or sql statement for this. It appears to be a common problem, and as such, would have attracted attension. Will you be kind enough to translate your idea into code (php)? Is foreach() a useful function for this?
Regards, serialmike
Edited December 11, 2007 by serialmike (see edit history)

[galexcd 0]

There is only one fix that I can think of that doesn't require manually relabeling every row, but it is sloppy and I do not recommend it. If you have your sql query delete the ID column and remake it with auto_increment it will relabel every row properly. But like I said it is very sloppy and I do not recommend it because if you have many rows and you you delete a column that other pages may need and remake it it may cause some errors if somebody else is loading another page at exactly the right time.

[jlhaslip 4]

serialmike,In order to answer this question more intelligently, we would need to see your database structure. Since the "id" is presumably the Primary Key for the Data Table you want 'sequentially' numbered, what other data sets are affected? Assuming this is a 'members' table, is this a Foreign key for other tables? Are members assigned to groups using this key? Is this key used to track their attendance at a forum? Or their posting activity? The Database structure will let us see if their are complications to deleting and re-organising the Data.If you simply need a 'count' of the members, can you use the count function? One of the greatest advantages to using a database for recording information is the ability to use this 'key' to track data rows. The 'ID' number does not need to be sequential. You risk major contamination of the Database by affecting the 'id' of a dataset. Forewarning is given.