CODEWITHSUNDEEP
php
julio
julio

Reputation: 6728

get ordinal value for letter PHP

I've been given a datafile where the original creator used alphabetical rather than numeric values to show order.

For example, if there's ten items, they'd be named:

12342313A
12342313B
12342313C
12342313D
12342313E
...

I need to import these values into a mySQL table that has order as a required int column, and I need to convert the letter to a number.

Is there a function in PHP to get a numeric value for a letter? Or will I need to do a substr to grab the trailing letter, and create an indexed array of letters and just do a lookup against that array?

I'm hesitant to do the simple way above, since I don't know how many objects could potentially exist, and I could need to write an array from A-AAAA or something.

Upvotes: 0

Views: 655

Answers (5)

Chris Baker
Chris Baker

Reputation: 50612

Assuming this is a one-time problem that you've got to correct and won't encounter moving forward, I suggest you use sort to... erm, sort out the problem. Let's say you have all those alpha-numeric order fields in an array, like so:

$vals = array (
    '12342313A',
    '12342313D',
    '12342313E',
    '12342313B',
    '12342313C'
);

Those are all mixed up, not in order. But, you can call the function sort (docs) on that array and PHP does a decent job of making sense out of it: 

    print '<pre>Unsorted: ';
    print_r($vals);
    print '</pre>';

    sort($vals);

    print '<pre>Sorted: ';
    print_r($vals);
    print '</pre>';

/*
Unsorted: Array
(
    [0] => 12342313A
    [1] => 12342313D
    [2] => 12342313E
    [3] => 12342313B
    [4] => 12342313C
)

Sorted: Array
(
    [0] => 12342313A
    [1] => 12342313B
    [2] => 12342313C
    [3] => 12342313D
    [4] => 12342313E
)
*/

So far, so good. Now, you've got them ordered, and as a bonus you can use the index of the array as your new field in the database. Alter the table and add a field to hold the new value; we'll call this field numeric_order, and in my sample I've called the field that currently holds the alpha-numeric sort data string_order. Loop your sorted array and update the database (for example):

foreach ($vals as $x=>$v) {
    $sql = 'UPDATE myTable SET numeric_order = '.($x+1).' WHERE string_order = "'.$v.'"';
}

I add 1 to x in the loop based on the assumption that you don't want anything to have 0 for the order - if that isn't a concern, then you can just use x. This is also predicated on the assumption that no two rows have the same alpha-numeric sort value.

If they do, then all is not lost! Start with your array looking like this:

$vals = array (
    3=>'12342313A',
    15=>'12342313D',
    66=>'12342313E',
    101=>'12342313B',
    200=>'12342313C'
);

... the numeric keys would represent the unique/primary key of the corresponding row. Instead of sort, which does not preserve keys, use asort (which does preserve keys - docs), and then your loop looks like this:

$ord = 1
foreach ($vals as $x=>$v) {
    $sql = 'UPDATE myTable SET numeric_order = '.$ord.' WHERE id = "'.$x.'"';
    $ord++;
}

If my base assumption is wrong, and you'll continue to deal with this method of ordering rows, then in my humble view you ought to re-consider your data design.

Upvotes: 1

Jemaclus
Jemaclus

Reputation: 2376

PHP has a simple way to create that array, so you could write a function to figure all that out for you and do something like:

    function str_to_num($letters, $max = 'ZZZZZZ') {
        $count = 0;
        for ($i = 'A'; $i < $max; $i++) {
            $count++;
            if ($letters == $i) 
                return $count;
        }
    }

Then you could do the substr, find the letters at the end, and then pass it into the function:

    str_to_num('A');  // returns 1
    str_to_num('AB'); // returns 28
    str_to_num('AC'); // returns 29
    str_to_num('ABC'); // returns 731

Something like that, anyway.

Good luck.

Upvotes: 1

shesek
shesek

Reputation: 4682

Try converting it from base 36 to base 10 using base_convert(), I.e. base_convert($str, 36, 10). You might need to strtolower it first, and it'll only work if its not case sensitive.

Upvotes: 2

Galen
Galen

Reputation: 30170

use ord() with substr and subtract 64. This will set A to 1, B to 2, etc...

Upvotes: 0

maraspin
maraspin

Reputation: 2393

From what you have above, it seems like your values (last digit, at least) can be thought as being hex numbers. You can then transform them into decimal numbers through the hexdec function.

http://php.net/manual/en/function.hexdec.php

Upvotes: 0

Related Questions