How to find/insert data in missing sequence(auto increment) ID?

Question

I have a table that contains

ID(P)(auto)  NAME
----------------------- 
1            Art
4            Bankie
6            Cara

The data sequence has 3 missing ID values: 2, 3 and 5.

This is my query:

$fname = $_POST['fname'];
mysql_query("INSERT INTO applicants ($fname) VALUES ('$fname')");

How can data be INSERTed in place of a missing ID value, in order to use all ID's.

From a SQL point, you need to do three things. Stop the AutoInc, make the INSERTs, enable the AutoInc again. But why did you use the php tag? — Lucio, Sep 24 '15 at 05:20
You can also manually INSERT the data, or even adjust the auto_increment value. — Brandon White, Sep 24 '15 at 05:21
@BrandonWhite. why you would stop and insert manually? bad idea. — kim de castro, Sep 24 '15 at 05:23
What are you trying to do? It can't pull the data from nowhere... If you are talking about holes due to InnoDB "INSERT IGNORE" that is a different story. Could you elaborate on your question, so we might actually propose a decent solution. Furthermore, what have you tried to solve your problem? — Brandon White, Sep 24 '15 at 05:26
check my update. I'm inserting a data then display it and have a delete button. — kim de castro, Sep 24 '15 at 05:28
Just so I understand, you HAD data in the missing values that you deleted, and now you want to place new data in their place? — Brandon White, Sep 24 '15 at 05:31
yup that's the point. so all the ID will be useful no missing sequence. — kim de castro, Sep 24 '15 at 05:33

score 1 · Answer 1 · edited May 23 '17 at 12:06

What you're asking is not necessary for performance purposes, however to my knowledge there isn't a way to do this automatically. You would have to check for the missing ID ranges and assign them manually.

I would also recommend looking at the following post about SQL injection protection as it's a necessary step to maintaining a secure database connection. SQL Injection Prevention

Secondly, please do not use php's mysql_query() functions. These are deprecated and have been for some time now. MySQLi or PDO is recommended instead.

score 0 · Answer 2 · answered Sep 24 '15 at 06:01

It's could be done using variable in sql query.

Let's say $name = $_POST['fname'];

Then you need insert query like this :

insert into yourtable(id,name)
select id-diff as new_id,'$name' from(
    select id,id - (@x := @x + 1) as diff
    from yourtable
    join (select @x := 0) as t1
    order by id asc
) as t2
where diff <> 0
limit 1

How to find/insert data in missing sequence(auto increment) ID?

2 Answers2