Let's assume that 'a' corresponds to 0 and 'z' to 25, and you're using ASCII otherwise.
So in 'ab012', 'a' is in the 10000th place, 'b' in 1000th place, and the rest are as normal.
This means that given a suitable number, we can transform it to the required code by removing the last 3 digits, then obtaining the alphabetic portion (in numeric form) by subtracting the last 3 digits and dividing by 1000.
Then we either use a direct mapping of number to letter pair, or we do a guess and check search.
The required code would be the last 3 digits prepended with the letter pair found.
Given the 'ab' portion,
10a+b=c, where c is the numeric version of the alphabetic code.
Hence (c-b)/10=a, and (c-b)=0(mod 10)
Possible code:
//convert from 1 to aa001
//$num in range [0,275999]
function calcAlNumCode($num) {
//numeric portion
//last 3 digits
$n = substr($num, -3);
//alphabetic portion
//subtract last 3 digits, divide by 1000
$c = ($num - $n) / 1000;
//map 0=>a to 25=>z
$letters = range('a', 'z');
//search for required letter pair
$p = 'aa'; //initialise required letter pair
for ($b = 0; $b < 26; $b++) {
$a = $c - $b; //(c-b) in 10a+b=c
//(c-b)=0(mod 10)
if ($a % 10 == 0) {
//a=(c-b)/10
$a = (int)($a / 10);
//check that $a is within range
if (array_key_exists($a, $letters)) {
$p = $letters[$a] . chr($b + 97);
break;
} //else continue
}
}
//last 3 digits prepended with the letter pair found
return $p . $n;
}
//convert from aa001 to 1
//$code in range [aa000, zz999]
function calcNumCode($code) {
return 10000 * (ord($code{0}) - 97)
+ 1000 * (ord($code{1}) - 97)
+ substr($code, -3);
}
In this way, one can then use a normal AUTO_INCREMENT for the id, and convert when necessary.
I do think that unless you change the searching in calcAlNumCode() to a direct mapping (i.e. pre-calculation), you're going to have a significant overhead when dealing with a significant number of records at a time.
