php unique random number

[kproc]

below is code that I'm trying to use to create a unique random number for my mebers> how do I make sure that the random number created in not in my data base already. I know how check for the value in the data base but I don't know how to tell it to create the loop to create a new number when if a a match is found.

<?php
$min = 8000000
$max3 = 8999999
referral_number = rand($min,$max);

$check_referral_num = mysql_query("SELECT referral_number FROM user WHERE user_id = '$user_id'");

$row_check = mysql_fetch_assoc($check_referral_num);

if($row_check <1) {

mysql_query("UPDATE users SET referral_number = '$referral_number' WHERE user_id = '$user_id'");

}

?>

[NogDog]

I'd recommend creating (or altering) the table so that referral_number column is an auto-increment integer, and in the table options set the starting number to 8000000:

CREATE TABLE table_name (
referral_number MEDIUMINT UNSIGNED,
...<other column definitions>...
)
AUTO_INCREMENT = 8000000;

Then just let the database assign the next available number, rather than possibly having to generate several numbers and make several DB queries until you happen to select an unused number.

[kproc]

can I set this query some how to create to update the table with the auto number that you mentioned. not everyone is going to need this number


mysql_query("UPDATE users SET referral_number = '$referral_number' WHERE user_id = '$user_id'");

[bokeh]

unique ... randomThose are two adjectives that can't be used to describe the same object. If a number is to be truely randomly selected it can't be unique and to be truely unique it can't be randomly selected. As NogDog states your best option would be to use a numbering system that elects its choice through logic rather than guesswork and to let the database handle it.

[NightShift58]

Within the context of a database application, it makes sense to have a random number that is unique - it just depends on what "random" and "unique" is. A random number that is unique within a table? It's done all the time.

I can only assume that you're willing to go through the trouble of doing it this way to prevent potentially malfeasant users from exploiting the referral system that you're building. If that's the case, it makes perfect sense to go for the unique and random solution. If not, heed NogDog's advice...

Further assuming that you need the unique, random number feature, the solution is - from a logical perspective - fairly simple. The real problem is concurrency.

How many such inserts are likely to take place at the same time? We don't know, of course. What I mean is that once you've determined that the number is not present in the database, there will be no guarantee that, in the split second it took to make that determination, that number could have been inserted/updated in the table by another instance of the script running elsewhere.

As such, you'll need to merge the checking and updating into one step - and if the number is not yet taken, "grab" it on the spot. This could be done by executing an "UPDATE IGNORE" query and then checking with mysql_affected_rows() if the update really took place. If not, have that part of your script keep trying (looping) until it succeeds.

Once you've got the basic routine down, you can then tweak it to keep the guessing down to a minimum.