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]

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pi****@comcast.net> wrote:

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?

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]

"Ian.H" <ia*@WINDOZEdigiserv.net> wrote in message
news:7r********************************@4ax.com...

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pi****@comcast.net> wrote:

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?

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/

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]

On Fri, 03 Sep 2004 02:43:13 GMT, "kingofkolt"
<je**********@comcast.net> wrote:

"Ian.H" <ia*@WINDOZEdigiserv.net> wrote in message
news:7r********************************@4ax.com.. .

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pi****@comcast.net> wrote:

>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?

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 =)

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

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]

On Thu, 02 Sep 2004 23:45:08 GMT, Neal <pi****@comcast.net> wrote:

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?

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