Id to userfriendly characters

Question

I want to create a userfriendly system to make users enter a certain id:

id 1-26: A-Z
27: AA
28: AB
    ...
    ...
    ZZZ

I tried several ways to achieve this but I get random characters...

$counter = 7;
do {
    $temp = floor($id / (26^$counter)) % 26; 
    $counter--;
    echo($temp.' ');
} while($counter > 0);

Where does `$id` come from? Is it a number that the user enters? — aefxx, Sep 17 '12 at 08:03
Yes, 27 = AA, 28 = AB... The id isn't user input but comes from the db. — Ferdau, Sep 17 '12 at 08:04
You're giving yourself the same problem as Excel column names, in that it's not as simple as converting to base 26. Have a look at http://stackoverflow.com/questions/297213/translate-an-index-into-an-excel-column-name — cmbuckley, Sep 17 '12 at 08:24
Just ... `AA` is "user-friendly", where `27` isn't?!? Where is the difference? — KingCrunch, Sep 17 '12 at 08:26
For a PHP solution: http://stackoverflow.com/a/5554413/263057 — Guillaume Poussel, Sep 17 '12 at 08:36
@KingCrunch yes, imo YRX is more userfriendly than 391023 but indeed for smaller numbers this isn't the case... — Ferdau, Sep 17 '12 at 08:59
@Ferdau "more userfriendly" is quite relative when you taking into account, that a user can't read both ;) (`HJDFSH` <=> `98432`?) — KingCrunch, Sep 17 '12 at 09:07
Well wouldn't HJDFSH be like 983712349108? It will be shorter and believe but thanks for the input! — Ferdau, Sep 17 '12 at 09:50

score 1 · Accepted Answer · answered Sep 17 '12 at 08:31

Mapping Excel column name to numeric order is kind of a crappy thing, because there's no 0 in A-Z...

Anyway, I did come up with two functions to convert them back and forth:

function calcCol($col) //character to number
{
    if(is_numeric($col)) return intval($col);
    $col=array_reverse(str_split(strtoupper(preg_replace("/[^a-z]/i","",$col))));
    $num=0;
    foreach($col as $i=>$ch)
    {
        $num+=(ord($ch)-ord('A')+1)*pow(27,$i);
    }
    $num-=ceil($num/27)-1;
    return $num;
}
function getCol($col) //number to character
{
    if(preg_match("/^[a-z]+$/i",$col)) return strtoupper($col);
    $col=abs(intval($col));
    $col+=ceil($col/26)-1;
    $str="";
    while($col>0)
    {
        $tmp=$col%27;
        $str=chr($tmp-1+ord('A')).$str;
        $col=floor($col/27);
    }
    return $str;
}

Explanation:

Consider A-Z as a 27-based numeric system with a missing/hidden 0;

And after converting from character to number, removes those hidden 0s by counting how many 27 is "counted" (ceil($num/27));

And before converting from number to character, add those hidden 0s back by counting how many 26 is "counted" (ceil($col/26)).

Marcus · Answer 2 · 2012-09-17T09:09:18.867

This is a base-converting problem, but there's a little trick.

First we consider 'A' as 1, so 'AA' or 'AAA' could make sense, and 'B' as 2, 'C' as 3.., but what about 'Z'? No, 'Z' is not 26, the trick here is to consider the 'Z' as 'A0', and every time we find a 0 when in converting, we write it down as 'Z', and throw a 'A' away(by subtracting 1).

so we have the following code:

function num2user( $num )
{
    $s = "ZABCDEFGHIJKLMNOPQRSTUVWXYZ";
    $ret = "";
    while( $num > 0 )
    {
        $m = $num % 26;
        $ret = $s[$m].$ret;
        $num = floor($num / 26);
        if( $m == 0 ) $num = $num - 1;
    }
    return $ret;
}

for( $i = 1 ; $i < 128 ; $i++ )
{
    echo "$i=".num2user($i)."\n";
}

Id to userfriendly characters

2 Answers2