[RESOLVED] Getting the next number available for INSERT query

Mr_Mako2

Hi...

Hopefully I can explain this well enough.

I'm working with a website that INSERTs new users with two identifiers... a userID (that's unique and permanent), and a userCODE (which can be changed). Currently when a new user signs up, the code assigns the next available userCODE to the new user from the last user who signed up. For example, if Joe Bloe signs up and is assigned userCODE 1123, when Fred Joe signs up a day later, he'll get userCODE 1124. The code I'm using to do this is listed below :

// Create Next User Code Script //
$user = "SELECT MAX(user_code) FROM user";
$user_result = @mysql_query($user, $connect) or die(mysql_error());

while ($user_row = mysql_fetch_array($user_result)) {
	$ucode = $pcd_row['MAX(user_code)'];

$user_code = ($ucode + 1);
}

The problem is that, when users are deleted from inactivity, it frees up userCODEs for use again. For example, if Joe Bloe is deleted, then userCODE 1123 would be available for use... but with the code above, since Fred Joe is active and his userCODE is 1124, the next userCODE that will be assigned will be 1125, not 1123.

So what I need to do is figure out how to run either a loop or some such that allows the script above to loop through the userCODEs in the user table and assign the next available number... rather than the MAX number. I'm not really sure how to do that... I haven't played with loops a lot.

Can anyone give me any ideas?

Thanks in advance...

laserlight

Why dont you use an autoincremented field?

Mr_Mako2

I have the userID field autoincremented. The site uses the userID as the identifier for all user related activity. However, I don't necessarily want the userCODE autoincremented, because that number can change for a number of reasons. And autoincrementing the userCODE would result with the same problem I am experiencing now with the current script.

laserlight

hmm... I see what you mean.

One way might be to store the deleted userCODEs in a table, then assign from that table if it has any entries.

Mr_Mako2

Would that be easier than simply scripting a loop to run through the userCODEs until it reaches an available one? For this project, my thinking is that the loop would be the better scenario... simply because it would eliminate the need for the extra table (and resultant code) you mention.

laserlight

Would that be easier than simply scripting a loop to run through the userCODEs until it reaches an available one?

Both are rather easy. There might be some kind of race condition since you are using more than one SQL statement to do a single task, but I am not an expert DBA so I cannot provide such analysis for you.

For this project, my thinking is that the loop would be the better scenario... simply because it would eliminate the need for the extra table (and resultant code) you mention.

However, the loop would involve retrieving the userCODEs from the entire table. One just needs to retrieve one row from the table of deleted userCODEs, and if there's an empty result set, just insert as usual.

Mr_Mako2

I went ahead and set up a reference table where all the old userCODEs are dumped and wrote a quick script that checks that table first. Worked just like I needed it too.

Thanks for the tip.