> 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!This is the way it should be, and it's like that for a reason - if you have other tables with relations to the ID in the original table, you may cause some headaches when the holes are filled up by new rows and they become related to those other tables. > 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. You may find the function mysql_insert_id() useful. If you'd like to count the number of rows in a table you can SELECT COUNT(*) FROM table; > 1) Can the next auto_increment value be 'set' by a SQL query???? That's done automagically, and it's probably best to leave it that way. > 2) Can I get a SQL query to INSERT INTO the first 'hole' it finds in the ID > column?? It's possible but not recommended, you can just search through the rows and see where there's a hole. -Jome -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
