Generate Alphanumeric ID from Numeric ID in MySQL/PHP

Question

Hi,

I have a basic MySQL database where each row is assigned a unique incrementing ID number, however I would also like each row to have a unique 6 character, alpha numeric ID which also increments. Example:

Row 1: ID = 1, Name = AAAAAA
Row 2: ID = 2, Name = AAAAAB
Row 3: ID = 3, Name = AAAAAC

The database is added to via PHP so this can be done through PHP as well as through pure SQL.

So far I have been trying to build a PHP function which could turn the Numeric ID into the AlphaNumeric ID to no success.

Anybody have any suggestions? Thank you.

Answer 1

Something like this should work and produce unique ID's (produces just letters):

function alphaID($n)
{
    for($r = ""; $n >= 0; $n = intval($n / 26) - 1)
        $r = chr($n%26 + 0x41) . $r;
    return str_pad($r, 6, 'A', STR_PAD_LEFT);
}

echo alphaID(0); // returns "AAAAAA"
echo alphaID(1); // returns "AAAAAB"
...
echo alphaID(8652); // returns "AAALTU"

Answer 2

you have to do it the hard way and first making a char column primary and then doing a ton insert rigger that looks up a global variable for the current maximal alphanumeric value and increments that and afterwards assigns the incremented value to the new inserted line. The problem is i don't if there is already a special string incrementation function or if you have to make some casts from string to integers then increment the integers and afterwards do a back cast to string.

Answer 3

The numeric id is unique. I wouldn't store the alphanumeric value in the table (it violates a few rules of DB normalization) and besides the numeric id and the alphanumeric id are actually the same value, no sense double storing a value! If possible (and it should be), only ever query using the numeric value (which I assume is your primary key anyways).

I would use a PHP function to convert numeric to alphanumeric (such as posted by Tatu Ulmanen) and call it in your pages when you have a reason to display the alphanumeric. In MSSQL you could write a UDF to display the value whenever it was needed if you didn't want to rely on the PHP. Though I never really got into stored functions etc with myssql so not sure if its possible to do that with it.

You could also use PHP's base_convert() function

base_convert(35, 10, 36); //= convert '35' from base '10' to base '36' and you would get 'Z'