Generating Random Number and inserting into database - with no duplicates

[HDFilmMaker2112]

I need a way to generate a random number and insert into a database, and I need the database to not contain any duplicates of that number. I basically need to generate a RMA # (Return Merchandise Authorization Number), so the numbers absolutely CAN NOT be duplicate. I have no ideas how to go about this.

 

Should I generate a random number, search the database and see if there's a duplicate, and if there is re-run the script?

[hemo-ali]

As I understand you want to insert random numbers and don't want to duplicate  this number if you want that

you can check that number before insertng

[mikesta707]

Theres a few things wrong with that idea. Firstly, depending on how you run that script, it could take a very long time if you have a lot of users.

 

Secondly, look into the unique key construct that mysql supports.

 

A little more information would be good. How long are the numbers gonna be? can they be any length? Why do they have to be random? Would an auto incremented primary key suffice?

[HDFilmMaker2112]

I need them to be random, because I don't want people to anticipate a number being issued. I'm hoping to generate something between 8-12 characters. All issued numbers would be the same length. 

 

Would the below work?

 

while(execute_sql("INSERT INTO ... RAND()") != SUCCESS){
}
echo "You have now successfully added a random number";

[mikesta707]

Well That code doesn't really show us much. We don't know how your database is structered, so giving a definite answer is somewhat impossible, but theoretically you could go about solving your problem using something similar to what you posted

[matthew9090]

this might work which i quickly wrote:

 

<?php
$chars = "123456789"; //numbers 1-9 and copy as many times as you want for dublicate numbers
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars = str_shuffle($chars);
$chars = substr($chars, 0, ;
//connect here
mysql_connect("host", "user", "pass");
mysql_select_db("db");
$sql = mysql_query("SELECT * FROM table WHERE number='$chars'"); //change the table name to fit your db
if (mysql_num_rows($sql)>0)
{
   header("Location: this_page.php");
}
else
{
   //insert
}
?>

[mikesta707]

this might work which i quickly wrote:

 

<?php
$chars = "123456789"; //numbers 1-9 and copy as many times as you want for dublicate numbers
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars .= "123456789"; 
$chars = str_shuffle($chars);
$chars = substr($chars, 0, ;
//connect here
mysql_connect("host", "user", "pass");
mysql_select_db("db");
$sql = mysql_query("SELECT * FROM table WHERE number='$chars'"); //change the table name to fit your db
if (mysql_num_rows($sql)>0)
{
   header("Location: this_page.php");
}
else
{
   //insert
}
?>

 

The only problem with this script, is it could lead to a crash of your server depending on how many users he has. For example, if OP has 1000 users, someone may try to join, in the worse case scenario, the user could be very unlucky, and need to refresh the page 1000 times before he gets a unique number. Yes, this is very unlikely, but if it can happen, it will happen, and you need to always consider the worse case scenario. Consider what will happen if multiple users try to sign up in a short time frame.

 

Basically, the larger the audience that this script has, the longer on average it will take to run, and the more chance for a server crash, or an overload of bandwidth. If OP is not planning on having a large amount of users for his script, it may be OK, but if he plans on having a larger amount of users, this may not be the optimal solution.

 

Now, another way you could go about doing this is to generate a table with all the possible unique values and a column that tells you whether that number has been used or not. For you, you could have a table with the numbers 10000000-999999999999 (8 - 12 digit numbers). Assuming you have this table, you could then do something like

//get 1 unused unique key
$sql = "SELECT number FROM uniqueIDS WHERE used=false ORDER BY rand() LIMIT 1";
$res = mysql_query($sql);
$row = mysql_fetch_array($res);
$id = $row['number'];

//now we can use $id however we want, and its a unique randomized number from 8-12 digits long

 

Hope this helps

[spiderwell]

i would use a combination of a session id and a timestamp with non numeric characters removed.

simple, and wont ever duplicate

[xyph]

Use an algorithm using the current unix timestamp, the username of the person dropping the rma, and a pseudo-random number generator.

 

Here's a basic one using just a timestamp.

 

<?php

$user = 'xyph';
// Reverse to make it harder to predict
$stamp = (int) strrev(time());
// character set up change base
$charset = '123456789ABCDEFGHJKLMNPQRSTUVWXYZ';
// convert timestamp to base whatever to shorten and obfuscate
$rma_str = base10_to_x( $stamp, $charset );

echo 'RMA Number: '.$user.'_'.$rma_str;

//echo 'Unique RMA Number: '.$user.'_'.$rma_str;

function base10_to_x( $num,$charset ) {
$len = strlen($charset);
for( $r=''; $num>=0; $num=intval(bcdiv($num,$len))-1 )
	$r=$charset[bcmod($num,$len)].$r;
return $r;	
}

?>

 

Just add a couple random digits to the start/end of the timestamp, and mix around the character set to make it nearly impossible to reverse your methods and predict what a future RMA number will be, though I don't see how this can be an issue if you're storing all the RMA numbers your script makes.

[ShaolinF]

The solution is simple, just use a cryptographic hash like SHA2, you will have no collisions. You can use the customer name, RMA primary key id, and a timestamp, hash them and there you have an RMA. If its too long then you can trim it. One major benefit (in this case) is hash calculations are very light on the CPU, you can performs 1000s per second and collisions are extremely extremely unlikely.

 

<?php
$rma_primarykey_id = 12831;
$customer_name = 'namehere';
$microtime = microtime();
$rma_number = hash('sha256', ($rma_primarykey_id.$customer_name.$time); // 256bits of pure randomness

?>

[xyph]

Trimming hashes increases the chance of collisions.

 

And a 256bit RMA number is, well, huge.

[cunoodle2]

Here is some code that I had used in the past.  I had 3 secure fields in the DB and I needed to verify that no users had the exact same SETS of all 3 security fields.  Here was my code..

 

<?php
//set up a query to check for security codes
$stmt = $read->prepare("SELECT `Key1` FROM `User` where Key1 = ?, Key2 = ? AND Key3 = ?;");

do{
	//get three random numbers to be used for security tracking in database
	$key1 = RandPass();
	$key2 = RandPass();
	$key3 = RandPass();

	//execute query and extract information
	$stmt->execute(array($key1, $key2, $key3));
	$result = $stmt->fetch(PDO::FETCH_ASSOC);
} while ($key1 == $result["Key1"]);
?>

 

Basically it would keep generating random keys until the key1 did NOT match.  You should be able to use something like this.

 

My other thought is to just build a table of RMA's with 2 colums (ID and rand#).  Have this table already built (like possibly in the middle of the night to prevent load during busy times) and then when the user needs an RMA they just get the next one in said table that has yet to be used.  So again, build the entire table of like 3,000 RMAs and then just pull values from there.  Once you get over 2000 RMAS then build another table.  You will be totally fine.

[Alex1646]

Try using uniqid(), then checking the database for any duplicates using a mysql  WHERE statement.

[HDFilmMaker2112]

The issue with some of these Ideas is I don't have user accounts. I'm not handling any of the payment processing, or cart processing... I'm using PayPal.

 

I came up with this idea:

 

<?php
do {
$test=base_convert(mt_rand(0x19A100, 0x39AA3FF), 10, 36);
$test.=mt_rand(0000001, 9999999);
} 
while (mysql_query("INSERT INTO ... ") != SUCCESS);
echo $test;
?>

And using a Unique row in the table for the RMA numbers.

 

Don't know how viable that is though. I've never used Do-While before, so I could be using it completely wrong. The idea of running a second table with all the RMAs already created could be another option.

[HDFilmMaker2112]

What about using time() and appending a two digit random number to the beginning of it? Seems like it would be a 10 digit number, then I can add two additional characters to it.

[HDFilmMaker2112]

I hate to keep posting (wish I could a edit for a little while longer), but I came up with this:

 

<?php
<?php
$length = 2;
$characters = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$string ="";
$string.= time();
$string.= substr($string, 2);
for ($p = 0; $p < $length; $p++) {
$string .= $characters[mt_rand(0, strlen($characters))];
}
echo $string;
?>

 

 

The issue is it seems the substr is not removing the first to characters (the "13" from the unix time stamp), it's making the whole string longer. I want to remove the "13" and add an additional 2 random characters on front.

[HDFilmMaker2112]

<?php
$length = 2;
$characters = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$string ="";
$string.= microtime(true);
for ($p = 0; $p < $length; $p++) {
$string .= $characters[mt_rand(0, strlen($characters))];
}
$string= substr_replace($string, '', 0, 4);
for ($p = 0; $p < $length; $p++) {
$string2 .= $characters[mt_rand(0, strlen($characters))];
}
$string="".$string2."".$string;
$string=explode(".",$string);
$string=$string[0].$string[1];
echo $string;
?>

 

[xyph]

You don't have to use my example EXACTLY, it could easily be done without username.

 

You'd need some sort of unique seed to it though, perhaps by using an auto_increment value that was tied to the original purchase?

 

Your final code there is completely random, so there's nothing to stop duplicate entries from existing.

[HDFilmMaker2112]

You don't have to use my example EXACTLY, it could easily be done without username.

 

You'd need some sort of unique seed to it though, perhaps by using an auto_increment value that was tied to the original purchase?

 

Your final code there is completely random, so there's nothing to stop duplicate entries from existing.

 

won't microtime(true) always generate a incremented number based on when the rma issued? I don't see how there would be duplicates if the microtime is always incrementing and will never repeat in the foreseeable future.

[HDFilmMaker2112]

You'd need some sort of unique seed to it though, perhaps by using an auto_increment value that was tied to the original purchase?

 

I have no data what so ever of the purchases in my database or on my server... Everything's on Paypal. I could use Paypal IPN if it explained more clearly on their website, but I don't understand almost any of the way it works.