Generate Alphanumeric ID from Numeric ID in MySQL/PHP

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 = AAAAA开发者_运维百科A
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.

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"

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'

an easy way would b to represent the number in a base-32 or hexadecimal format... an easy and a quick fix...

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.