HDFilmMaker2112 Posted May 28, 2011 Share Posted May 28, 2011 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? Quote Link to comment Share on other sites More sharing options...
hemo-ali Posted May 28, 2011 Share Posted May 28, 2011 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 Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted May 28, 2011 Share Posted May 28, 2011 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? Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 28, 2011 Author Share Posted May 28, 2011 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"; Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted May 28, 2011 Share Posted May 28, 2011 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 Quote Link to comment Share on other sites More sharing options...
matthew9090 Posted May 28, 2011 Share Posted May 28, 2011 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 } ?> Quote Link to comment Share on other sites More sharing options...
mikesta707 Posted May 28, 2011 Share Posted May 28, 2011 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 Quote Link to comment Share on other sites More sharing options...
spiderwell Posted May 28, 2011 Share Posted May 28, 2011 i would use a combination of a session id and a timestamp with non numeric characters removed. simple, and wont ever duplicate Quote Link to comment Share on other sites More sharing options...
xyph Posted May 28, 2011 Share Posted May 28, 2011 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. Quote Link to comment Share on other sites More sharing options...
ShaolinF Posted May 28, 2011 Share Posted May 28, 2011 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 ?> Quote Link to comment Share on other sites More sharing options...
xyph Posted May 28, 2011 Share Posted May 28, 2011 Trimming hashes increases the chance of collisions. And a 256bit RMA number is, well, huge. Quote Link to comment Share on other sites More sharing options...
cunoodle2 Posted May 28, 2011 Share Posted May 28, 2011 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. Quote Link to comment Share on other sites More sharing options...
Alex1646 Posted May 28, 2011 Share Posted May 28, 2011 Try using uniqid(), then checking the database for any duplicates using a mysql WHERE statement. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 28, 2011 Author Share Posted May 28, 2011 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. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 29, 2011 Author Share Posted May 29, 2011 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. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 29, 2011 Author Share Posted May 29, 2011 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. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 29, 2011 Author Share Posted May 29, 2011 <?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; ?> Quote Link to comment Share on other sites More sharing options...
xyph Posted May 29, 2011 Share Posted May 29, 2011 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. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 30, 2011 Author Share Posted May 30, 2011 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. Quote Link to comment Share on other sites More sharing options...
HDFilmMaker2112 Posted May 30, 2011 Author Share Posted May 30, 2011 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.