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

[Chris Hope]

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

Merlin wrote:
[color=blue]
> 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.[/color]

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/

[Frank Schummertz]

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

Merlin wrote:
[color=blue]
> id = INT NOT NULL PRIMARY KEY
> data = TEXT NOT NULL[/color]
[...][color=blue]
> 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.[/color]

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

[Andy Hassall]

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

On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:
[color=blue]
>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.[/color]

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 <andy@andyh.co. uk> / Space: disk usage analysis tool
http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space

[Five Cats]

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

In message <ud4mc09101cak5 rddb6c8cckm8akp 7ns6t@4ax.com>, Andy Hassall
<andy@andyh.co. uk> writes[color=blue]
>On Sat, 12 Jun 2004 00:08:37 GMT, "Merlin" <.@.com> wrote:
>[color=green]
>>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?[/color][/color]

They will still go in order - it's just there will be a gap or two.
[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

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

[Geoff Berrow]

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

I noticed that Message-ID:
<9Qryc.2117$Wr. 1705@newsread1. news.pas.earthl ink.net> from Merlin
contained the following:
[color=blue]
>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.[/color]

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/