Generating Random Unique Number

bostonmacosx

I've read some of the answers to this Question is on here already but it seems I have a more simple problem which hasn't been explained. Any help would be appreciated.

I already have a database where I have an asset tag # field with vaules between 90000-99999(not all used). When someone submits a new record to the database I need it to generate a unique number in this range to be added into the database. It has to be unique though. How do I check the random generated number against the database and then loop it till it hits a # number not being already used.

Thanks
Robert

fitzbean

$sql = "SELECT thefieldname FROM table";
$result = mysql($connection, $sql);
$rows = mysql_num_rows($result);

$unique = false;
while ($unique = false)
{
$uniqueid = rand(90000,99999);
$i = 0;
$unique = True;
while (($i < $rows) and ($unique = true))
{
$dbnumber = mysql_result($result, $i, thefieldname);
if ($dbnumber == $uniqueid)
{ $unique = false; }
$i++;
}

{

echo "$uniqueid is not in the DB.";

fitzbean

That last { should be }

🙂

Fitz

[deleted]

You just said what you'd have to do, make a number and run query to see if it's free.

Unfortunately it's not quite that simple.
PHP/webserver is multi-user, several people can enter new records into the database, so it is possible that two people come up with the same number, andboth run a query to see if it exists, both will see it is free, and both will enter a record. Boom: duplicate number. (that will happen)

Does the number have to be random?
if you only need a unique number, just use a sequence, or as MySQL calls it: auto_increment.

That brute force "create a number and see if it works" can really load your database as the amount of free numbers get's smaller.

bostonmacosx

Think I came up with the solution.

$hhh=1;
while ($hhh != 0) {

$rand_num=rand(90000,99999);
$result = mysql_query("select asset from inventory where asset='$rand_num'") or die("Invalid Query");
$hhh = mysql_num_rows($result);}

If this is in the submit phase and submitted immediately after lookup the chances of duplication are very small.

I can't think of a reason that this wouldn't since only one actual submit is allowed at a time. The persons involved would have to be within the same micron to have this duplicate.

[deleted]

"The persons involved would have to be within the same micron to have this duplicate."

a few milliseconds is enough, it's more likely than you think.
But if you say that is an acceptable risk, go ahead. Don't say I didn't warn you! :-)

Anon

Furthermore, if it's that likely that two users will create new records within a few milliseconds of each other, it is also likely that more than 10,000 records (the total possible number of IDs) will be created in a fairly short time.

If the average time between two records being created is one second, then 10,000 IDs will be used up in less than three hours.

bostonmacosx

These are all good valid points. The main part of the project will not require this routine to run so the chances are like a milion to 1 that it would happen. If it does I can always massage the data later.

So here is the question though. How can you do this correctly?

Thanks
Robert

[deleted]

"Furthermore, if it's that likely that two users will create new records within a few milliseconds of each other it is also likely that more than 10,000 records (the total possible number of IDs) will be created in a fairly short time"

Possible, not likely.
It is possible that two users create a record within the same 50 milliseconds and it is possible that they choose the same number. Not likely, but possible.

You need one heck of a database server to be able to deal with one inserts per 25ms, that's 40 inserts/second, 2400/minute,144000 per hour. :-)

[deleted]

Correctly:

Make your database design so that the column that holds the unique number is defined as 'UNIQUE'.

-Generate a random number.
-Run the insert query (with the random numebr)
-Check to see if the query was executed ok.
-If the number was a duplicate, the UNIQUE constraint on the table will refuse to insert the record and come back with an error message. If the query failed, generate a new number and repeat the process.
-If the number was unique, the record is inserted without error.

Anon

I wasn't talking about simultaneous updates (where everyone's trying to get the same number), only considering the fact that a server busy enough that two hits within 50ms becomes something to worry about is also a server which is so busy over the longer term (in this case, a few hours) that the ID pool would become exhausted in that time.