Page 1 of 1

Inserting Rows With Auto Increment Fields?

Posted: Wed Mar 17, 2004 12:13 pm
by webcan
Hello:

I'm currently writing some PHP code to display photos in a thumbnail gallery.

Each photo record in the

Code: Select all

 database has a record ID, filename, and group name field.  The group name field sorts it into different galleries, eg. gallery A, gallery B, etc.  The record ID is auto incrementing.

Heres the dillema:

If I have 10 records already in the database, so the next record ID is 11, and I want to add a photo between 3 and 4, how do I do that?

Do i have to run queries to change 10 to 11, then 9 to 10, then 8 to 9, etc. before inserting as the new number 4?  Or is there a better way?  If not, and I do the number change like this, does the auto-increment automatically recognize the next available number or is it going to come back with an error?

Thanks,
Peter.

Re: Inserting Rows With Auto Increment Fields?

Posted: Wed Mar 17, 2004 1:13 pm
by TheBentinel.com
webcan wrote:If I have 10 records already in the database, so the next record ID is 11, and I want to add a photo between 3 and 4, how do I do that?
The beauty of an auto-increment number is that you don't have to maintain it. If twenty users try to create a record at the same time, it's not a problem because the database makes sure there aren't any collisions.

Rather than try to use the auto-number field for this, I would suggest you create an extra field for sorting purposes. You could number that field by 100's, then insert records easily without reshuffling.