CODEWITHSUNDEEP
phpmysql
GGio
GGio

Reputation: 7653

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

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 ?

Upvotes: 4

Views: 1667

Answers (4)

Alex Khimich
Alex Khimich

Reputation: 828

My workaround for not loaded project:

Suppose, you have questionset with question_id 's which belong to certain topic_id.

Suppose, user navigates and clicks "<Prev" "Next>" buttons to navigate questions.

You have only current id. Catching the direction of navigation, topic_id, question_id you can do a loop

  do {

  // query base, doing question_id++ or question_id-- depending on needed    direction until you find next id within topic_id 

  } while( id!=null ) `

using incrementation or decrementation depending on direction of your move

Upvotes: 0

George Cummins
George Cummins

Reputation: 28936

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.

Upvotes: 3

Drew
Drew

Reputation: 1777

This should do the trick:

SELECT
  min_table.ID+1 AS start,
  MIN(max_table.ID) - 1 AS end
FROM
  your_table AS min_table,
  your_table AS max_table
WHERE
  min_table.ID < max_table.ID
GROUP BY
  min_table.ID
HAVING
  start < MIN(max_table.ID)

The left hand column will return the first available spot in the sequence gap, and the second is the highest number in that particular gap.

Source: http://www.codediesel.com/mysql/sequence-gaps-in-mysql/

Upvotes: 0

Wesley Schleumer de G&#243;es
Wesley Schleumer de G&#243;es

Reputation: 6527

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

Upvotes: 6

Related Questions