MySQL Query / Locating "next available number"

[Mr_Mako]

I've been searching for almost an hour on the forums looking for the answer to this, and evidently I'm not wording my searches correctly because I haven't found a solution, even though I know I've come across this here before.

I have a database with a table for users. In this users table is a "user_code" field. The table essentially looks thus:

user_id		user_code	user_name	user_email
-------------------------------------------------------------
0001		1001		Bob		[e-mail]
0002		1002		Rick		[e-mail]
0003		1003		Tom		[e-mail]
0004		1004		Alan		[e-mail]

Now currently what I have when a new user is added is an INSERT statement that includes a variable result from a previous query using the MAX() function to get the next available user_code, like this:

$cd = "SELECT MAX(user_code) FROM users";
$cd_result = @mysql_query($cd, $connect) or die(mysql_error());

while ($cd_row = mysql_fetch_array($cd_result)) {
	$cd_code = $cd_row['MAX(user_code)'];

$cdcode = ($cd_code + 1);
}

$nu = "INSERT INTO users (user_code, user_name, user_email) VALUES ('$cdcode', '$_POST[username]', '$_POST[email]')";
$nu_result = @mysql_query($nu, $connect) or die(mysql_error());

Naturally, after the first time a previously entered user was deleted, this approach presented a problem.

So what I need to do is re-phrase the SELECT statement to be able to loop through the user_code fields and grab the next available unused user_code. For example, if I deleted the user "Tom" above, and then later added a new user to the database, my SELECT statement will show the next available user_code as 1005, when it should be 1003 ("Tom's" old number).

I'm fairly certain the solution here is to utilize an array of some sort... but I'm not too familar with arrays (other than what's shown above), and as such am not sure how to begin. I'd really appreciate any ideas.

[rincewind456]

Why do you want to fill in the gaps?

Unless there is a very important reason (and I can't think of one), don't mess with numbers in mysql especially if you have used those numbers in other areas of the db.

[Mr_Mako]

I want to fill in the gaps because I don't want large jumps between user_codes. The user_code is basically an identifier in the game / program the users play. There is no correlation anywhere else in the database of the user_code. The user_id is what is used for all correlation and relations... and that of course is assigned by mysql and never changes.

[Kiramin]

This thread is old, but in case anyone cares, here is a possible solution (no guarantee it is a good one).

---

SELECT
(MIN(user_code) +1) AS first_available_box

FROM (

SELECT DISTINCT
t0.user_code,
t1.user_code AS number_plus_one
FROM
user_table AS t0
LEFT JOIN
user_table AS t1
ON
t0.user_code +1 = t1.user_code

) AS temp1

WHERE number_plus_one IS NULL

The query basically LEFT JOINs the table against itself, seeing if each user_code has a row where the user_code is one higher. If so, the resulting number_plus_one field has a value, and if not, the value is NULL.

The second query uses that subquery and finds the lowest user_code that has NULL for the number_plus_one field, ands one, and wallah! you have the first unused number.

It has some potential problems, that would have to be fixed by improving the WHERE logic. For example, it wont fill in numbers below your current min (i.e. if the first user_code is 1003 it will never give you 1002, 1, or any number in between as an available number).

[chasiv]

or just

$strSQL = "SELECT user_code FROM users ORDER BY user_code ASC";
$result = mysql_query($strSQL);
unset($avail_num);
unset($last);
$counter = 0;
while($row = mysql_fetch_assoc($result)) {
     If ($counter>0) {
          If($row['user_code'] != $last+1) {
                $avail_num = $last+1;
                break;
          }
    }
    $last = $row['user_code'];
    $counter++;
}
If(!$avail_num) { $avail_num = $last+1; }  // in case there are no gaps !!

Which basically cycles through your user_codes (in order) until the user_code is not 1 more then the previous one.

[bradgrafelman]

I'll reiterate again what rincewind said:

rincewind456 wrote:

Unless there is a very important reason (and I can't think of one), don't mess with numbers in mysql especially if you have used those numbers in other areas of the db.

He couldn't be more correct. First of all, I don't see the need for user_code when there's already a user_id field.

Despite that, you should never mess with sequences like this just to "fill in the gaps". Not only does it use more resources to try to find the next number, but it opens up a whole world of problems such as race conditions, broken relations, etc.