I'm using PHP and MySQL and
I have a table with 3 fields ((ID, Username, PID)).
I want the PID field to contain strings of 8 unique characters.
My solution is to generate the random string in PHP and check if it exists. If it exists then it will generate another string.
Is there any better solution that will save processing time, like a MySQL trigger or something like that?
This will give you a random 8 character 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 could also use:
substr(md5('username value'), 0, 8);
Though it's extremely unlikely, particularly for the md5, neither case guarantees a unique string, so I would 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 would probably only yield 1 'check' query, maybe 2 in unique cases, and would ensure a unique string.
You can create 8 chars unique string in Mysql in such a way
CAST(MD5(RAND()) as CHAR(8))
Do the characters need to be random? Or just unique? If they only need to be unique, you could use a timestamp. Basing the value on time will ensure a uniqueness.
If you go another route, you'll have to check your generated value against the database until you end up with a unique value.
Why not do this the correct way and use UUIDs (aka GUIDs), which are always unique, no need to check if they are or not. It may be 36 chars, but you get the benefit of storing them as HEX which saves disk space and increase speed over standard CHAR data.
You can read the comments on the PHP doc for functions that do this.
My solution is to generate the random string in PHP and check if it exists. If it exists then it will generate another string.
This is the wrong way to do it. The web server will run multiple instances of your code concurrently, 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 column UNIQUE, and don't bother with any pre-checks. Just run the INSERT query, and check the result.
If the result is a 1062 (ER_DUP_ENTRY) error, generate a new PID and try again.
Any other database error should be dealt with like you normally would.
Perhaps 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 */
}
If you're set on 8 characters for the PID value then you'll need something to generate the string and check that it doesn't already exist.
$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 doesn't need to be random, then use the ID value, which is probably an auto-increment integer and start the count for that at 10000000.
Then just do a simple A=1, B=2, C=3 etc replacement on the digits in that number to generate your string.
Your mileage may vary.
--Mark
