PHP / MySQL - auto increment id

[Neal]

I just did my first PHP/MySQL database (a simple survey) that works
fine. It is ordered by an id field that auto increments. Apparently, one
entry repeated (I'm thinking the user hit the submit button twice). So,
I deleted a record. My question is, how do I make it so the next entry
will use the id# that I deleted, so there won't be a gap in the id numbers?

[Ian.H]

Re: PHP / MySQL - auto increment id

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pissed@comcast .net> wrote:
[color=blue]
>I just did my first PHP/MySQL database (a simple survey) that works
>fine. It is ordered by an id field that auto increments. Apparently, one
>entry repeated (I'm thinking the user hit the submit button twice). So,
>I deleted a record. My question is, how do I make it so the next entry
>will use the id# that I deleted, so there won't be a gap in the id numbers?[/color]


Login to SQL (phpMyAdmin for example) and change the auto_increment
value back one.. so if it currently reports 12, make it 11.

In phpMyAdmin, this is under the 'Operations' tab after selecting the
required table.


HTH =)



Regards,

Ian

--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[kingofkolt]

Re: PHP / MySQL - auto increment id

"Ian.H" <ian@WINDOZEdig iserv.net> wrote in message
news:7rdfj0drr2 ok2a76v43rp7km0 ecjq32elg@4ax.c om...[color=blue]
> On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pissed@comcast .net> wrote:
>[color=green]
> >I just did my first PHP/MySQL database (a simple survey) that works
> >fine. It is ordered by an id field that auto increments. Apparently, one
> >entry repeated (I'm thinking the user hit the submit button twice). So,
> >I deleted a record. My question is, how do I make it so the next entry
> >will use the id# that I deleted, so there won't be a gap in the id[/color][/color]
numbers?[color=blue]
>
>
> Login to SQL (phpMyAdmin for example) and change the auto_increment
> value back one.. so if it currently reports 12, make it 11.
>
> In phpMyAdmin, this is under the 'Operations' tab after selecting the
> required table.
>
>
> HTH =)
>
>
>
> Regards,
>
> Ian
>
> --
> Ian.H
> digiServ Network
> London, UK
> http://digiserv.net/[/color]

Neal --

While this would certainly fill the ID gap =), it's generally not a good
idea to change an auto_increment ID in a database, in case the ID is used as
a foreign key in another database. Having gaps in ID #'s in a database is
not necessarily a bad thing. More often than not it's inconsequential . Best
to just leave the ID #'s where they are and not change them.

- JP

[Ian.H]

Re: PHP / MySQL - auto increment id

On Fri, 03 Sep 2004 02:43:13 GMT, "kingofkolt "
<jessepNOSPAM@c omcast.net> wrote:
[color=blue]
>"Ian.H" <ian@WINDOZEdig iserv.net> wrote in message
>news:7rdfj0drr 2ok2a76v43rp7km 0ecjq32elg@4ax. com...[color=green]
>> On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pissed@comcast .net> wrote:
>>[color=darkred]
>> >I just did my first PHP/MySQL database (a simple survey) that works
>> >fine. It is ordered by an id field that auto increments. Apparently, one
>> >entry repeated (I'm thinking the user hit the submit button twice). So,
>> >I deleted a record. My question is, how do I make it so the next entry
>> >will use the id# that I deleted, so there won't be a gap in the id[/color][/color]
>numbers?[color=green]
>>
>>
>> Login to SQL (phpMyAdmin for example) and change the auto_increment
>> value back one.. so if it currently reports 12, make it 11.
>>
>> In phpMyAdmin, this is under the 'Operations' tab after selecting the
>> required table.
>>
>>
>> HTH =)[/color][/color]

[color=blue]
>
>Neal --
>
>While this would certainly fill the ID gap =), it's generally not a good
>idea to change an auto_increment ID in a database, in case the ID is used as
>a foreign key in another database. Having gaps in ID #'s in a database is
>not necessarily a bad thing. More often than not it's inconsequential . Best
>to just leave the ID #'s where they are and not change them.
>
>- JP
>[/color]


Agreed =)

I rarely change the IDs.. only occasionally within my dev environment.
As you rightly point out, changing these can have disastrous effects in
sometimes, the least expected places if not careful.



Regards,

Ian

--
Ian.H
digiServ Network
London, UK

http://digiserv.net/

[Andy Hassall]

Re: PHP / MySQL - auto increment id

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pissed@comcast .net> wrote:
[color=blue]
>I just did my first PHP/MySQL database (a simple survey) that works
>fine. It is ordered by an id field that auto increments. Apparently, one
>entry repeated (I'm thinking the user hit the submit button twice). So,
>I deleted a record. My question is, how do I make it so the next entry
>will use the id# that I deleted, so there won't be a gap in the id numbers?[/color]

Could you explain why you want to do this?

AUTO_INCREMENT gives you a unique identifier that't not been used before. You
shouldn't make any assumptions that the sequence of numbers you get is
gap-free. And besides, the row with that ID has been deleted - why should
another different row get the same identifier?

If you're worried about running out of numbers, consider that an INT UNSIGNED
will last over 136 years at one new record every second - that's 4,294,967,295
rows.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool