Page 1 of 1

PHP and MySQL auto_increment

Posted: Tue Mar 11, 2003 5:41 pm
by icesolid
Is there ANY way to reset the next autoindex value using PHP after a new table row has been created?

I have a table where there are only 50 rows of actual information, but next autoindex is set at like 600. So like it goes from id=3 to the next id which is id=32.

Any ideas?

Posted: Tue Mar 11, 2003 5:50 pm
by volka
only a drop tablename or truncate tablename resets the autoincrement-counter. If you really(!) need it you might create a temporary table, transfer the data, truncate the original table and re-create/fill it.
http://www.mysql.com/doc/en/CREATE_TABLE.html wrote:From MySQL Version 3.23, you can use the TEMPORARY keyword when you create a table. The name is restricted to the current connection, and the temporary table will automatically be deleted when the connection is closed. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted.). From MySQL 4.0.2 one must have the CREATE TEMPORARY TABLES privilege to be able to create temporary tables.
http://www.mysql.com/doc/en/TRUNCATE.html
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html

Posted: Wed Mar 12, 2003 10:59 am
by daven
If you need to have your ID field (which you auto_increment) be continually sequential (1,2,3,4. . . .) you might want to think about setting it manually in your code.

ie--do a query to get the MAX(id), and use MAX(id)+1 as the ID for the next row. Not as nice as using auth_increment, but it ensures that your rows are sequential.

Posted: Wed Mar 12, 2003 6:40 pm
by McGruff
It's a bit of a hack but I suppose you could delete the column, then create it again. All the numbers should now be sequential. Not very elegant but might help.

Note that, if URLs use row IDs to link to specific pages on your site, bookmarks to such pages will be thrown off if you reset ID numbers.

manually setting the id

Posted: Thu Mar 13, 2003 5:10 pm
by musashi
I agree with the notion of manually setting the id. However keep in mind that this creates, as does the auto_increment method, a need to auto-adjust the id during the removal process.

Since it seems that you do remove rows from the table, and have been hit by the auto_increment frustrations of lack of direct control (although, mind you, you can assign a value to the auto_increment field during the insert statement which should override the mysql internal incrementor) you need to consider that in order to maintain a consecutive list, you MUST reset things when you delete.

Actually you could get away with reseting the auto_increment column (like deleting it as was stated before), and then from then on only address the issue of non-consecutive entries when you remove a row.