Hey,
I was wondering if there is an easy way to splice id fields in such a way that if you have the fields:

ID
1
2
3
4
5

and you remove field 2, then 3,4 and 5 would become 2,3, and 4. In other words:

ID >> ID
1 >> 1
2 >> 2
3 >> 3
4 >> 4
5 >>

If you remove '2' from the first.

Thankyou for your help.

No
Imagine the problems it would create if you were using PK/FK relationships on the ID.

You could probably do it by running the query "UPDATE table SET id = id -1 WHERE id > $theidyouaredeleting" after you've done the deletion.

As chris says though if you want other tables to refer to the data in your first table, all the numbers will be off by one unless you update the references too. It's far better just to leave gaps.

If you want to display numbered lists of your data, you can leave the id as they are and use PHP/ASP to print out a counter variable with each row, that way your lists appear contiguous, but your data integrity is maintained.

I see your point, I hadn't thought of that. But suprisingly this still works as my references are all to other content in the table rather than the ID. I think that the bit of code in the last post will help me a lot. thanks.

[EDIT]I have another question, if I used that last bit of code to shift all the ID's after the removed table down, and ID is set to AUTO-INCREMENT, will the autoincrement still be on track(i.e. will it pick up at 5, or after the last ID it created--at 6.

The autoincrement will probably pick up at 6, but you can reset it with "ALTER table AUTO_INCREMENT = $newvalue".

Again I woul stringly recommend you take a good look at your database design - you say you are referring to items in your table by other content, not the ID field. What happens if any of these fields have duplicate entries? What happens if they have awkward characters and escape sequences? You are far better off leaving the auto number column to do the job it was designed to do, and use the id column to refer to things since the auto numbering guarantees that it is unique.

I also agree with 0beron. This sounds like a bad idea and you could run into problems later down the line.

I don't know much about the way databases handle data internally, but I can imagine doing what you're proposing to do would probably create a lot more of a performance hit than you really need - you're shifting around a huge chunk of records when you only really need to be playing with one. You may not notice the performance hit now, but later down the line, as the number of records in the table increases, your application will get slower and slower.