On Wed, August 29, 2007 12:49 pm, Jason Pruim wrote: > I think after I get this question answered, I can stop asking for > awhile since my project will be done, at least until the users say > "What happened to XYZ????" then I'll ask again :) > > I asked on a MySQL list about "Resetting a auto increment filed" so > that there arn't any gaps in the record number. > > So to say it another way, I have a table that has 900 records in it, > I've added 3 records, but then deleted 2 of those which puts the > actual record count at 901 but my auto increment field starts at 904 > on the next insert. > > Is there away with PHP that I can pragmatically change that value to > the total records in the database more so then a representation of > the actual record number? Can you do this? Sure. update whatever set ID_field = 901 where ID_field = 904 Of course, if that 904 is in some OTHER table, then you MUST update that other table at the same time, in an ACID transation, to be sure you don't get your relationships all kerflummoxed. SHOULD you do this? NOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO!!! The ID key field of a record should not have any explicit meaning. If you try to re-number your records so it does, it will just cause you grief and give you nothing useful in return. If you actualy have a table where there *IS* an ordering of the records which is meaningful, and it really SHOULD be 1 to N, then add another field "rank" (or somesuch) and manipulate that independent of the ID field, which you should never care about its actual number. To explain WHY this is, would be way bigger than an email that's already off-topic for PHP, but they should have told you on the MySQL list. Or not, as it's probably in their FAQ which you should have read. If nothing else, the hassle of keeping all those IDs sorted out is a TON of extra code that serves little purpose, and a single tiny mistake can make a pig's breakfast of your entire database. Bad Idea. -- Please vote for this great band: http://acl.mp3.com/feature/soundandjury/?band=COMPANY-OF-THIEVES Requires email confirmation. One vote per day per email limit. Obvious ballot-stuffing will be revoked. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
