Re: Newbie SET AUTO_INCREMENT question

----- Original Message ----- From: "Verdon Vaillancourt" <vern@verdon.ca> To: "'PHP-General'" <php-general@lists.php.net> Sent: Friday, April 18, 2003 10:49 AM Subject: [PHP] Newbie SET AUTO_INCREMENT question > Hi :) > > I have some data that each week needs to be moved to an archive and deleted > from the original. Archived data is also recycled each week. I mostly have > the process working and it flows like the following... > > // empty a pair of tables (archives data) > DELETE FROM listingsDB_archive > DELETE FROM listingsDBElements_archive > > // copy the contents from a pair of similar tables (current data) > // to the recently emptied tables (archives data) > INSERT INTO listingsDB_archive SELECT * FROM > listingsDB > INSERT INTO listingsDBElements_archive SELECT * FROM listingsDBElements > > // empty the similar tables(current data) > DELETE FROM listingsDB > DELETE FROM listingsDBElements > > This works fine except the auto incrementing 'ID' field in > listingsDB (the > autoindex field) is reset to 0 each time. I need to NOT have the autoindex > reset, but instead to have it keep running uninterrupted. Some reading at > mysql.com makes me think I can do something like the following, but I'm > stuck... > > ALTER listingsDB SET AUTO_INCREMENT=xx or > ALTER listingsDB SET INSERT_ID=xx > > Actually, I know I would need to somehow find and store the correct value > for AUTO_INCREMENT before I delete from listingsDB and then > restore it. > > I'm just wondering, am I going in the right direction or am I barking up the > wrong tree? > > TIA, verdon > PS please cc me in any replies to list. Well you could create a new table with a single cell set to 'unsigned integer' and increment that value each time you insert a new row into 'listingsDB'. If you have no other chioce than to delete the 'listingsDB' table of all rows, thereby resetting the auto-increment value, then you need to dissassociate the row count from that table. - Kevin