Page 1 of 1

Auto increment question

Posted: Sun Apr 08, 2007 11:12 am
by user___
Hi guys,
I have a table which is dynamically updated. It has an id column which is primary_key and auto_increment. When a row is deleted and a new is created the new id is as it should have been if the previous had not been deleted. I do not want this to be in this way but when I delete for example number eight and a new is created(After I have deleted number eight) to have my new one numbered eight not nine.

I can do this without id being auto_increment but I have to do it in the way as described above.

Posted: Sun Apr 08, 2007 11:28 am
by John Cartwright
Why does the id have to be without gaps? This is not the intended purpose of auto_increment. If your wanting to number the rows, do that in the outputting.

Code: Select all

$i = 0;
while ($row = mysql_fetch_assoc($result)) 
{
   $i++;

   echo $i. ' ' $row['foobar'];
}

Reply

Posted: Sun Apr 08, 2007 12:40 pm
by user___
I do know this but I need it in the way I said above. I have just given an example with a table. I have about one hundred table and most of them are related to each other by this id but if some of them change(update, delete or insert) others do not(I know this is not as most systems work but the system I am creating is a little strange.). So when I have some of my ids deleted and some not I get incorrect results.

BTW:Thank you for your help.

Posted: Sun Apr 08, 2007 12:54 pm
by jayshields
There's absolutely no need for a column like this...

But if you have to, remove the auto increment from the field and just put a 1 in the primary key of the first row in the table, then whenever you use INSERT, use a SELECT query to fetch the highest primary key and then add 1 to it and use it as the new primary key in the INSERT query.

You could probably do this with a subquery.

Re: Reply

Posted: Sun Apr 08, 2007 2:16 pm
by timvw
user___ wrote:I do know this but I need it in the way I said above. I have just given an example with a table. I have about one hundred table and most of them are related to each other by this id but if some of them change(update, delete or insert) others do not(I know this is not as most systems work but the system I am creating is a little strange.). So when I have some of my ids deleted and some not I get incorrect results.
It sounds to me that you want to look into referrential constraints, more specifically: foreign key constraints... Since you mentionned auto_increment i presume that you're using mysql: http://dev.mysql.com/doc/refman/5.0/en/ ... aints.html

Reply

Posted: Sun Apr 08, 2007 3:25 pm
by user___
I use MyISAM so I can not use foreign keys.(As far as I know).

However, thank you for your help.