auto_increment is not in the SQL standard, and not everyone uses MySQL. To be cross-compatible between databases use a Sequence: http://pear.php.net/manual/en/package.database.db.intro-sequences.php provides a brief introduction, the PEAR::DB class allows you to use sequences easily.
Neither auto-increment nor sequences allow you to do what you're asking for though, which is like database garbage collection. If you really need something like this you should implement it in a cron job that runs during non-peak hours as it will be quite an expensive operation on a large table (the best way I can think of would be to iterate over all table rows and look for "gaps" in the table, and for each gap take the record with the highest ID and change the ID to fill the gap, then update all replys to match the new ID, after everything is finished reset the sequence to the highest id in the modified table and you're done). Note from the MySQL manual for you auto_increment fans out there: Posted by Jim Martin on Tuesday October 1 2002, @11:57am Just in case there's any question, the AUTO_INCREMENT field /DOES NOT WRAP/. Once you hit the limit for the field size, INSERTs generate an error. (As per Jeremy Cole) And the comment a few beyond that regarding being able to drop the index and renumber doesn't help here, as there is an exterior resource (the reply table) that will be broken as soon as the ids change. If anyone has a better suggestion on how to do this I'd love to hear it :) -Javier > -----Original Message----- > From: Marek Kilimajer [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 11, 2003 3:30 PM > To: Dimitri Marshall > Cc: [EMAIL PROTECTED] > Subject: Re: [PHP] Unique ID > > > Dimitri Marshall wrote: > > Hi there, > > Here's my situation: > > > > I'm making a message board and I've decided the best way to > go about > > the structure is to have 3 tables, two of them will be "Posts" and > > "Replys". Now, in order for this ti work, each post has to have a > > UniqueID - same with the replys. Looking at another > program, I can see > > that one way to do this is to do it by rows (ie. count how > many rows, > > add 1, then that is the ID). It would be unique because no two rows > > would be 1 for example. > > > > The problem I can see is that the database would become incredibly > > huge (size wise I mean). I want to delete the posts after > 30 days, and > > if I delete the row, then that would mess up the row system. > > > > Any suggestions? > > Have you heard about auto_increment? Read on: > http://www.mysql.com/doc/en/example-> AUTO_INCREMENT.html > > -- > > PHP General Mailing List > (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
