[SOLVED] insert at lowest id possible

[GetYourArse]

I have a table with 2 columns. (id | title) id is not auto-increment but is unquie. Example i have these rows

 

[1] ["test"]

[3] ["test again"]

[5] ["another test"]

 

I want to insert a new row, with the id set to the lowest number possible and not 0 and not taken. So in this example the id should be 2. If anyone played gunbound you should know the room system, I'm trying to do that. Hope anyone understand me.

[Wildbug]

I can think of several ways of accomplishing this.

 

#1.  If your ids will be within a reasonable limit, you can define a table with a single column defined to the same type as your "id" column.  For example, if your id column is TINYINT UNSIGNED, create a table with a single TINYINT UNSIGNED column and fill it with a series of integers (1,2,3,4,5,...,255).  Then you can LEFT JOIN this table to any of your tables containing an id column where you want to find the first unused integer and select the first NULL.

SELECT * FROM numbers LEFT JOIN yourtable ON n=id WHERE id IS NULL LIMIT 1;

 

#2.  Use a session variable.  This is a little more complex.  It seems like I've been posting alot of these types of solutions lately, too.

<?php

mysql_query('SET @c=0');
$result = mysql_query('SELECT free FROM ((SELECT id,IF(id=@c+1,0,@c+1) AS free,@c:=id AS set_c FROM yourtable ORDER BY id) UNION (SELECT 0,COUNT(*)+1,0 FROM yourtable)) AS t1 WHERE free > 0 LIMIT 1');

if ($result && mysql_num_rows($result)) $first_avail_id = mysql_result($result,0);

?>

 

#3.  LEFT JOIN the table to itself.  This is probably the best solution!

SELECT t1.id+1 FROM yourtable AS t1 LEFT JOIN yourtable AS t2 ON t2.id=t1.id+1 WHERE t2.id IS NULL LIMIT 1;

[bubblegum.anarchy]

Number three is nearly brilliant, Wildbug - expect when the missing id is 1.

[Wildbug]

Agh!  The follies of not comprehensively testing code!

 

Well, you can get around that with a UNION subquery.

 

SELECT a.id+1 FROM
(SELECT 0 AS id UNION SELECT id FROM t2 ORDER BY id) AS a
LEFT JOIN t2 AS b ON b.id=a.id+1
WHERE b.id IS NULL
LIMIT 1;

 

(This assumes your ids start at 1.  If they're zero-based, then replace the "SELECT 0..." with "SELECT -1....")