PHP/MySQL Delete Row...Automatic Shift Numbers Down?

Merlin

Hello all!
I'll make it short and sweet...

I have a database, it looks something like this:

id data
--- -------------------------
0 Some Data
1 Some Other Data
2 Some More Other Data
Note:
id = INT NOT NULL PRIMARY KEY
data = TEXT NOT NULL

Now, I:
DELETE FROM whatever WHERE id='1';

Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again? I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

Thanks!
- Merlin

Jul 17 '05 #1

Chris Hope

Merlin wrote:

Then the ID numbers go "0, 2".**Is*there*a*way*to*automatically*shift*all
the ID numbers, after the one deleted, to go in order again?**I*can*write
a script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

I'm not quite sure exactly why you'd want to do this but you'd need to do it
manually like so:

DELETE FROM whatever WHERE id = X ;
UPDATE whatever SET id = id - 1 WHERE id > X;

and subsitute X for the number.

Note that if you're referring to this id number in other tables you are
going to have a much more difficult time as you also need to update allthe
values in all of those tables as well.

Unless you have a specific need to do so, is there really any reason todo
this? If there isn't, you'd probably be better off using an auto
incrementing primary key so you don't need to work out what the id value
should be each time you do an insert.

--
Chris Hope
The Electric Toolbox - http://www.electrictoolbox.com/

Jul 17 '05 #2

Frank Schummertz

Merlin wrote:

id = INT NOT NULL PRIMARY KEY
data = TEXT NOT NULL [...] Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again? I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

I am sure you do not want to take care of id's by yourself. Normally
id's are created as AUTO_INCREMENT:

my_id BIGINT NOT NULL AUTO_INCREMENT

Now MySQL takes care about this. Adding a new item to the db increases
my_id by 1.

Rgds,
Frank

--
http://landseer-stuttgart.de

Jul 17 '05 #3

Andy Hassall

On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:

I'll make it short and sweet...

I have a database, it looks something like this:

id data
--- -------------------------
0 Some Data
1 Some Other Data
2 Some More Other Data
Note:
id = INT NOT NULL PRIMARY KEY
data = TEXT NOT NULL

Now, I:
DELETE FROM whatever WHERE id='1';

Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again? I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

Automatically? No, since MySQL doesn't support triggers or similar constructs.
Besides, why would you want to anyway? Deleting a row shouldn't change the
identity of other rows.

--
Andy Hassall <an**@andyh.co.uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

Jul 17 '05 #4

Five Cats

In message <ud********************************@4ax.com>, Andy Hassall
<an**@andyh.co.uk> writes

On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:

I'll make it short and sweet...

I have a database, it looks something like this:

id data
--- -------------------------
0 Some Data
1 Some Other Data
2 Some More Other Data
Note:
id = INT NOT NULL PRIMARY KEY
data = TEXT NOT NULL

Now, I:
DELETE FROM whatever WHERE id='1';

Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again?
They will still go in order - it's just there will be a gap or two.
I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

Automatically? No, since MySQL doesn't support triggers or similar constructs.
Besides, why would you want to anyway? Deleting a row shouldn't change the
identity of other rows.

Some database designs use triggers to delete rows in other tables using
a deleted key as a foreign key - they need to be darn sure they really
want to delete it...

--
Five Cats
Email to: cats_spam at uk2 dot net

Jul 17 '05 #5

Geoff Berrow

I noticed that Message-ID:
<9Q****************@newsread1.news.pas.earthlink.n et> from Merlin
contained the following:

Now, I:
DELETE FROM whatever WHERE id='1';

Then the ID numbers go "0, 2". Is there a way to _automatically_ shift all
the ID numbers, after the one deleted, to go in order again? I can write a
script to update every row, I just want to know if there is a PHP/MySQL
function that automatically does it.

The ID ( or primary key) simply has to be unique. It doesn't have to be
in order, it doesn't even have to be a number, any random unique string
will do. Furthermore, it should not change

Any order in the results from a database is specified by you, as it
should be. For instance if you wish to sort the results in the date they
were entered you should incorporate a field including a timestamp and
order the results on that.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

Jul 17 '05 #6