Select first unused id using mysql query

Question

I have list of ID's in array format as follows

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

My table record as follows

id  email
1   [email protected]
2   [email protected]
10  [email protected]
13  [email protected]
15  [email protected]
20  [email protected]

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

For the above example, I expect the result 16. I need elegant way/query 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 table name is emails and your model is Email: use Eloquent to get the ids, then get the difference between your $ids array and the ids from the db, finally pick the first one:

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