MySQL & auto_increment

[Vulcan]

I have a table with an auto_increment on the id (like most tables).

And, for example, when I delete id 5, and create another entry in the table it will use id 6, instead of id 5 (which was deleted).

How can I solve this so when I make a new entry, it will use the last id+1. The only way I can think is to read the table and get the highest id number, then add 1 to it, but there must be a quicker/easier way.

Here's the snippet used to create the table, if it helps:

$query="CREATE TABLE $table (id int(3) NOT NULL auto_increment,title varchar(25) NOT NULL,info varchar(200) NOT NULL,PRIMARY KEY (id))";

 

[Beansprout]

Think carefully about what you need. It sounds like you don't need autoincrement

 

[Vulcan]

Well, without auto_increment it won't work when I add new items to the database.

 

[Beansprout]

It will. You just have to loop through and find the first un-used number first.

Auto increment is designed so that each inserted row is unique and no IDs are ever re-used. So you can pinpoint any row at any time. So it shouldn't matter if they get deleted, because the missing row simply means there was something there, but it's gone now. The ID doesn't mean anything beyond that...you can't use the last ID as the number records, for example.

 

[Vulcan]

Ah I see.

I removed the auto_increment, and used..

$query="SELECT id FROM $table WHERE id = (SELECT MAX(id) FROM $table)";

.. before inserting data to find the id with the greatest value. Then just added +1.

Thanks

 

[Beansprout]