How to find the next available integer in MySQL table using PHP

Question

I know auto_increment is the way to go but I can not use auto_increment feature since the column in my table might repeat, its not unique. When I insert a new row to a table I need a way to find the next available spot to insert it.

For example table structure:

 Primary Key = (ID, UserID)
 ID   UserID 
 3    6
 3    1
 1    3

Now when i do insert query i want to isert it at ID = 2 and not 4. With auto_increment it gives me 4

Is there a solution without using the loop in PHP? So far what i have is I fetch all rows into array and then find the next available digit in ID. Is it possible to do this without fetching all rows in PHP and just doing it on MySQL query ?

Answer 1

SELECT t1.id+1 AS MISSING_ID
FROM the_table AS t1
LEFT JOIN the_table AS t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL
ORDER BY t1.id LIMIT 1;

I made a fiddle: http://sqlfiddle.com/#!2/4d14d/2

Answer 2

No, it is not possible without processing the data. The preferred method to correct this issue is to adjust your table structure to support a unique, auto-incrementable field. Failing that, you will have to process the data (either in PHP or via an SQL statement) to find an open slot.