Re: Auto Increment Problems....

On Mon, 29 Jul 2002 20:44:54 -0700 Georgie Casey "Georgie Casey" <webmaster@filmfind.tv> wrote: > rite, > > my primary key column ("id") is set to auto_increment as usual > which is very > handy. But when I delete a row, the auto_increment just keeps > incrementing > and there's this 'hole' left where I deleted the row! > > 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. > > So I've 2 questions > 1) Can the next auto_increment value be 'set' by a SQL query???? > 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds > in the ID > column?? > > TIA Why do you want to create your own ID when one is created for you automatically? If you want to know how many rows are in a table, you can use "SELECT COUNT(*) FROM table" (at least using MySQL). If you need the ID of the last row inserted to be able to stick that ID into another table, using PHP, try "mysql_insert_id()". -- Raquel ============================================================ Happiness is not the absence of conflict; it is the ability to cope with it. --Unknown