Generate a random number & check DB to see it is unique

[playaz]

Hi guys,

I have a registration form that once submitted inserts the values into a mysql db. Its a registration form for a trade account (eCommerce site) - basically once a company applies for a trade account, i'd like an Account Number to be generated automatically however obviously this needs to be unique.

So ideally i'd would like it to randomly generate an 8 digit number and then check the database to ensure it isn't already in use.

Can anyone assist me on this thanks :)

[michaellunsford]

you could loop random number generation checked against the database.

[code]
$loop=true;
while($loop) {
  $acct_num = rand(10000000,99999999);
  if(!mysql_query("SELECT * FROM `table` WHERE `account_number` = '".$acct_num."' LIMIT 1")) $loop=false;
} // when found, loop ends, and $acct_num retains a value you can use later.
[/code]

Some mysql expert should have a better mysql solution -- which would be much faster -- but the above code still works.

[kenrbnsn]

I would get all of the current id numbers in the database, store them to a temporary array, then generate the random numbers until you find one not in the temporary array.

Something like:
[code]<?php
$q = "select acct_num from your_table";
$rs = mysql_query($q);
$tmp = array();
while ($rw = mysql_fetch_assoc($rs))
      $tmp[] = $rw['acct_num'];
$new_acct_num = rand(10000000,99999999);
while (in_array($new_acct_num,$tmp)) $new_acct_num = rand(10000000,99999999);
echo $new_acct_num;
?>[/code]

There is a slim possibility that this code will produce a duplicate number if more than one new account is processed at the same time (before the database is updated with the new number).

Is there any reason why you can't use an autoincrement field?

Ken

[YAOMK]

[quote]There is a slim possibility that this code will produce a duplicate number if more than one new account is processed at the same time (before the database is updated with the new number).
[/quote]

This can be solved using inodb tables along with transactions.

[corbin]

I would do something like:

[code=php:0]
$rand = rand(1000000000,9999999999);
while(mysql_num_rows(mysql_query("SELECT number FROM somewhere WHERE number = '{$rand}'")) > 0) {
$rand = rand(1000000000,9999999999);
}
[/code]