[RESOLVED] Avoiding database duplicates using randomization..

hotwire

Hello,

I am trying to implement the best way to check a Member ID field and make sure the new randomly generated Member ID is not duplicated in the database.

I am using mysql 4.2. I understand how to check the database for a match, but I am not sure if I am using the best method to regenerate a new Member ID if a match is found..

I want to know the best way to regenerate a new member ID if a match is found and recheck the database again..

This is the script I've been using:

while($row = mysql_fetch_array($result))
  {

$ckmemid =($row['MemID']);

if ($tmemid==$ckmemid){

while ($tmemid==$ckmemid){
$tmemid=rand(0000,9999);
}
}

It randomizes a 4 digit code. But it appears that after the new code is randomized after finding a match, it will not fully recheck the entire database again with the new randomized ID.

If a match is found in the database, should I just reload the page to have the query start from the top of the database again?

Thanks for any help on this issue..

laserlight

Declare the MemID column as unique. Generate the ID, then attempt to insert it. If there is a unique constraint violation, generate a new ID anf then insert again.

However, note that a 4 digit code is incredibly small. After about 100 members are registered, it becomes likely (more than 50% chance) that a randomly generated member ID will conflict with an existing member ID, and this process of regenerating and inserting IDs could eventually be rather slow.

To understand why this is to, read up on the birthday attack. This is why, if you do not actually need random IDs, you should just use auto increment.

hotwire

Hi laserlight,

Thanks for the response and info.. The 4 digit warning really makes good sense :eek: Didn't think about that..

Yeah I guess I'm primarily trying to ask what is the best way to re-query the database member ID field if a match is found.. As of now, i'm actually going to attempt to just reload the randomizing page with the query if a match is found.

It is the only way I can see at my amature stage to actually requery the member ID field from top to bottom each time a match is found.. I'll keep checking for a better optimized way, If Possible, to requery the entire Member ID field when a match is found.

Thanks for the input..

laserlight

Do not query the database. Suppose user A registers. At the same time, user B registers. Now, when the database is queried for user A's random ID, it returns no results. At the same time, when the database is queried for user B's random ID, it returns no results. Then the system inserts user A's random ID. If by chance the ID generated for user A and user B are the same, user B's ID would become a duplicate.

The solution to avoid this race condition, as I noted, is to ensure that the column is unique. A primary key column is unique (by right, but this is not always the case for different database vendors). Then instead of querying, you simply insert, then check for a unique constraint violation, and keep generating and inserting until there is no unique constraint violation.

NogDog

Hint: for MySQL, the duplicate key error number is 1062.

$result = mysql_query($your_insert_query);
if($result == false)
{
   if(mysql_errno() = 1062)
   {
      // ID already in use
   }
   else
   {
      // some other error, time to log it and let the user know something's broken
   }
}
else
{
   // all is good, insert succeeded
}

hotwire

Ok, I see what you're explaining now..

I never have actually used the unique function in my databases, but I see now is the time. I'll have to educate myself on implementing the unique function for the Member ID field.

I assign my number counts as primary.

I also see that based on what you are stating to me, that I would have to have some knowledge of handling a unique error which I have no knowledge of at the time. Would this procedure be more incumbersome than just reloading the page that performs the randomizing and querying?

It appears to be a more optimized approach the way you are telling me, but I would have to see about learning to handle the unique error part of it.

You're a big help thanks for your info..

hotwire

I just posted before I looked at NogDog info..Wow! That is interesting.. Now I see a bit more on using the unique function.. Will surely be more optimized than reloading the process page.. Thanks for this Info NogDog..