Insert unique strings of 8 random characters

Question

I use PHP and MySQL and I have a table with three fields (( ID , Username , PID )).

I want the PID field to contain strings of 8 unique characters.

My solution is to create a random string in PHP and check if it exists. If it exists, it will generate another string.

Is there a better solution that will save processing time, for example, a MySQL trigger or something like that?

Answer 1

This will give you a random 8 digit string:

 substr(str_pad(dechex(mt_rand()), 8, '0', STR_PAD_LEFT), -8); 

Found here: http://www.richardlord.net/blog/php-password-security

Or, if the username field is unique, you can also use:

 substr(md5('username value'), 0, 8); 

Although this is highly unlikely, especially for md5, not a single case guarantees a unique string, so I will probably do something like this:

 // Handle user registration or whatever... function generatePID($sUsername) { return substr(md5($sUsername), 0, 8); } $bUnique = false; $iAttempts = 0; while (!$bUnique && $iAttempts < 10) { $aCheck = $oDB->findByPID(generatePID("username value")); // Query the database for a PID matching whats generated if (!$aCheck) { // If nothing is found, exit the loop $bUnique = true; } else { $iAttempts++; } } // Save PID and such... 

... which is likely to give only 1 "check" request, possibly 2 in unique cases and provide a unique string.

Answer 2

Should characters be random? Or just unique? If they must be unique, you can use a timestamp. The initial value in time will provide uniqueness.

If you go on a different route, you will need to check the generated value in the database until you get a unique value.

Answer 3

Why don't you do it right and use UUIDs (also like GUIDs), which are always unique, no need to check if they are or not. It can be 36 characters, but you get the opportunity to store them as HEX, which saves disk space and increases speed compared to standard CHAR data.

You can read the comments on the PHP doc for functions that do this .

Answer 4

You can create a unique string of 8 characters in mysql this way

 CAST(MD5(RAND()) as CHAR(8)) 

Answer 5

My solution is to create a random string in PHP and check if it exists. If it exists, it will generate another string.

This is the wrong way to do this. The web server will run multiple instances of your code at the same time, and sooner or later two instances will store the same PID in your database.

The correct way to solve this problem is to make the PID UNIQUE column and not worry about any preliminary checks. Just run the INSERT query and check the result.

If the result is error 1062 (ER_DUP_ENTRY) , generate a new PID and try again.

Any other database error should be treated as usual.

Maybe something like this (untested):

 <?php /* $link = MySQLi connection */ if (!($stmt = mysqli_prepare ('INSERT `t` (`ID`, `Username`, `PID`) VALUES (?, ?, ?)'))) { /* Prepare error */ } if (!mysqli_bind_param ('iss', $id, $user, $pid) { /* Bind error */ } $e = 0; for ($i = 0; $i < 10; $i++) { $pid = /* generate random string */; if (mysqli_stmt_execute ($stmt)) break; /* success */ $e = mysqli_stmt_errno ($stmt); if ($e !== 1062) break; /* other error */ } mysqli_stmt_close ($stmt); if ($e) { if ($e === 1062) { /* Failed to generate unique PID */ } else { /* Other database error */ } } else { /* success */ } 

Answer 6

If the PID value is set to 8 characters, you need something to generate a string and verify that it does not exist yet.

 $alphabet = range('A','Z'); // get all the PIDs from the database $sql = "select PID from mytable"; // save those all to an array $pid_array = results of query saved to array shuffle($alphabet); $pid_offer = array_slice($alphabet,0,8); while(in_array($pid_offer, $pid_array)){ shuffle($alphabet); $pid_offer = array_slice($alphabet,0,8); } // found uniuqe $pid_offer... 

race conditions still exist.

If the string should not be random, use the ID value, which is probably an integer with auto-increment and will start the counter for 10000000.

Then just do a simple replacement A = 1, B = 2, C = 3, etc. on the digits of this number to generate your string.

Your mileage may vary.

- Mark