查找最低可用功能

Question

I'm trying to create function which will return for my script lowest available id of current user. For example, let's say, there's user with id 25, so he will be able to enter 10addresses, numbered from 250 to 259. So i created this function:

protected function checkAvailIDforAddress($id)
{

    $min = $id*10;
    $max = $id*10 + 9;

    $checkAvailID = $this->_dbconnection->prepare
            ('SELECT MAX(user_id) FROM user_address WHERE user_id BETWEEN :min AND :max');

    $checkAvailID->bindParam(':min', $min);
    $checkAvailID->bindParam(':max', $max);

    $checkAvailID->execute();

    $row = $checkAvailID->fetch();

    if ($row[0] == 0) throw new Exception('Error');

    $AvailID = $row[0] + 1;

    return $AvailID;


}

I thought, that this function will be good, but then i tested it and i found out, that if user put into db 3addresses, and then deleted 2nd, the function still return max. value, so entry will be added as 4th and 2nd will be unset.

Then I altered the question with foreach loop, and in every loop I made query to db to check, whether is certain id unset, but i think that this query is really uneffective. So my question is: is there some query to db which will return first available id between two values?

Answer 1

Hate to say this, but your question makes absolutely no sense.

I will point out that if you are looking for the LOWEST possible user_id, using SELECT MAX(user_id) is the opposite of what you want...

If you want a range, use SELECT MIN(user_id) as min, MAX(user_id) as max ....

'SELECT MIN(user_id) as min, MAX(user_id) as max FROM user_address WHERE user_id BETWEEN :min AND :max'

If your results, you will now have a 'min' and 'max' value that you can use to determine a range of available ID's?