mysql insert random unique 8 chars

Question

hello i'm using PHP , Mysql i have a table with 3 fields (( ID , Username , PID )).

i want 'PID field' to contain string of 8 unique chars.

my solution is to generate the random string in PHP and check if it's exists if it's exists then it will generate another string .

is there any better solution that will save processing time like mysql trigger or sth like that?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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