RE: [PHP] Auto Increment Problems....

-----Original Message----- >From: Georgie Casey [mailto:webmaster@filmfind.tv] >Apart from this looking ugly, it poses another problem. In my PHP script >where I can add new rows, I query the table, checking how many rows in the >table altogether and set the new id as the next number, but this doesnt work >if theres 'holes' in the id field, as the new record tries to overwrite >another id. The entire purpose of auto_increment is that you do NOT have to 'set' id when you add new records to the field. Don't reference your 'id' field in the insert statement at all. >So I've 2 questions >1) Can the next auto_increment value be 'set' by a SQL query???? No. The next auto_increment value is the same as max(id) + 1. However, just because a column is auto_increment doesn't mean you can't specify an id when you add a new record, if you like. >2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID >column?? Not with SQL by itself. You'd need to SELECT id FROM table, then use PHP to figure out how to fill in the hole. However, none of this should be a problem; simply allow those holes to exist and find some other way of numbering your records.