How to insert english alphabets as incremental order

Question

Hi i have the table structure as follows

id   --auto increment id
alphabet
user_id
name

and while adding i needs to insert the english alphabets to users as incremental order like this A,B,C,....Z,AA,AB,AC,...AZ,BA,BB,BC,.....BZ.

I need to get like this

    id    alphabet  user_id   name  
------------------------------------------
    1      A          1       name-1
    2      B          1       name-2
    .      .          .         .
    .      .          .         .
    26     Z          1       name-n
    27     A          2       name-1
    28     B          2       name-2
    .      .          .         .
    .      .          .         .
    52     Z          2       name-52
    53     AA         2       name-53 
    .      .          .         .
    .      .          .         .
    .      .          .         .
    .      AZ         2         .

Thanks for your time.

insert in alphabetical order? If you said that you want the result to be sorted that way I could understand your problem — Anshu, Oct 15 '12 at 05:25
@TheDOCTORfromTARDIS The Doctor is not from the TARDIS! Ha ha :) — Lews Therin, Oct 15 '12 at 05:26
@LewsTherin SO wasn't allowing the length with Gallifrey. Had to make-do. :( — hjpotter92, Oct 15 '12 at 05:29
@LearneR You can use `LAST_INSERT_ID()` and use a `(char) ((int)'A' + $last_id)`. — hjpotter92, Oct 15 '12 at 05:30
This may help you: http://stackoverflow.com/questions/3302857/algorithm-to-get-the-excel-like-column-name-of-a-number — Vijay Joseph, Oct 15 '12 at 05:47

score 1 · Answer 1 · edited May 23 '17 at 12:27

Check this similar article:

Algorithm to get the excel-like column name of a number

Here's a nice simple recursive function (Based on zero indexed numbers, meaning 0 == A, 1 == B, etc)...

function getNameFromNumber($num) {
    $numeric = $num % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval($num / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2 - 1) . $letter;
    } else {
        return $letter;
    }
}

And if you want it one indexed (1 == A, etc):

function getNameFromNumber($num) {
    $numeric = ($num - 1) % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval(($num - 1) / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2) . $letter;
    } else {
        return $letter;
    }
}

Tested with numbers from 0 to 10000...

score 1 · Accepted Answer · answered Oct 15 '12 at 06:23

1

It's quite easy that you use php to implement this, please check codes below

$i = 'A';
echo $i;
echo PHP_EOL;
while($i != 'ZZ')
{
 echo ++$i;
 echo PHP_EOL;
}

or see the results in http://codepad.org/QB4kyV7U

answered Oct 15 '12 at 06:23

Lake

813
4
6

Its worked for me after some modification of this code, thanks for your time. – iLaYa ツ Oct 16 '12 at 03:46

score 0 · Answer 3 · edited May 23 '17 at 12:18

0

Sounds a lot like this SO article

MYSQL: how to "reorder" a table

Is there an absolute necessity to do what you are wanting when you can use sorting to output your data however you want?

edited May 23 '17 at 12:18

Community

1
1

answered Oct 15 '12 at 05:24

rws907

787
4
14
25

How to insert english alphabets as incremental order

3 Answers3