Select first unused id using mysql query

Question

I have a list of identifiers in array format as follows

$ids = [10, 13, 16, 20, 25, 28, 34, 40, 45]; 

My table is written as follows

 id email 1 [e-mail] 2 [e-mail] 10 [e-mail] 13 [e-mail] 15 [e-mail] 20 [e-mail] 

I want to compare this table with the $ ids array and get the first unused identifier.

In the above example, I expect the result 16 . I need an elegant way / request to find the same.

Thanks in advance!

Answer 1

 <?php foreach($ids as $id){ $result = DB::table($table)->where('id', '=', $id)->get(); if($result && count($result){ continue; } echo 'First unused id :'. $id; break; } 

? >

Answer 2

Say your emails table name and your Email model: use Eloquent to get identifiers, then get the difference between your $ids array and identifiers from db, finally select the first one:

 $dbIds = Email::lists('id'); $diff = array_diff($ids, $dbIds); $first = array_shift($diff); 

Answer 3

 create table t1 (id int, email text); insert into t1 values (1, ' [e-mail] '), (2, ' [e-mail] '), (10, ' [e-mail] '), (13, ' [e-mail] '), (15, ' [e-mail] '), (20, ' [e-mail] '); create table t2 (id int); insert into t2 values (10), (13), (16), (20), (25), (28), (34), (40), (45); select t2.id from t2 where t2.id not in (select id from t1) 

result

 16, 25, 28, 34, 40, 45 

Demo

UPDATE

if you say

 select min(t2.id) from t2 where t2.id not in (select id from t1) 

you get only 16 :)

Answer 4

  $sql = 'SELECT field WHERE field IN ' . implode( ',', $ids) . ' );'; $result = $db->query($sql); $found = array(); while ($row = $result->fetch_assoc()) { $found[] = $row['field']; } 

then you can compare with your fields

 $not_found = array_diff($ids, $found); 

Answer 5

Not sure if elegant, but you can use a temporary table to store your array using ID in MySQL. Then you can query this temporary table with a limit of 1 and NOT IN in the list of identifiers you want to leave:

 CREATE TEMPORARY TABLE IF NOT EXISTS tmp_all_ids (`id` INT(11)); INSERT INTO `tmp_all_ids` (`id`) VALUES (2), (5), (3); SELECT `id` FROM `tmp_used_ids` WHERE `id` NOT IN (SELECT `id` FROM `table_with_used_ids`) LIMIT 1; 

So, create this query in your code, replacing the values ​​in INSERT INTO with a list of all IDs in $ ids using implode () or something like that.

Answer 6

First you can get an array of IDs from your database using

 SELECT GROUP_CONCAT(id) FROM tbl_name 

let's say you put the content returned in $ result, your ID array would be

 $db_ids = explode(',', $result); 

then you can just

 $diff = array_diff($ids, $db_ids); $first_unused_id = min($diff);