PHP String Evaluation Method

Question

Hoping one of the great minds can help me here.

I have a situation where I will receive an international phone number from a provider and I have to do a database lookup and figure out the country, region, and phone type that the phone number is calling.

For example, say I will receive a phone number +393234567891. I have to look up in a table the country that phone number belongs to. so I know that '39' is Italy, and I have that as a country code in the database but I have to determine whether the number is a landline or a cell phone. For that I need more information out of the phone number so '39' is a landline and '393' is a cell phone. I need to see that the phone number contains '393' and therefore I know that it is a cell phone.

My question is what is the best way to evaluate this? Would it be to loop through each segment of the phone number like first compare the first two phone numbers against the database, then the first three, then the first four until I come back with one single result? For example, if I continue with this example and compare '39' for Italy against the db I will come back with a bunch of results because there are '39' and '393' and '3939' and so on. So what is the best way to use the entire phone number to get the exact match for the phone number prefix?

I would have thought to just loop through the phone number and add a digit of the phone number to the loop until I come back with only one result, I just want to make sure that this is the most efficient way to accomplish this.

Any recommendations? Thanks!

Answer 1

I assume you have a table like:

prefix (id, number)

with data like:

1, '39'
2, '393'
3, '33'
4, '331'

You can get the longest match with a reverse LIKE:

SELECT id
FROM prefix
WHERE "393234567891" LIKE CONCAT(number, "%")
ORDER BY LENGTH(number)
LIMIT 1;

I have not tested it, but assuming your shortest prefix is 2 characters, you may get some improvement with (this will only check prefixes starting with 39, that is 1% of all the prefixes you have):

SELECT id
FROM prefix
WHERE "393234567891" LIKE CONCAT(number, "%")
    AND number LIKE "39%"
ORDER BY LENGTH(number)
LIMIT 1;

Then you can have a different table with the informaton attached to that prefix like:

prefixinfo (id, prefix_id, type, data)

with data like:

1, 1, 'country', 'Italy'
2, 2, 'country', 'Italy'
3, 2, 'type',    'Landline'
4, 3, 'country', 'France'
5, 4, 'country', 'France'
6, 4, 'city',    'Paris'

Answer 2

if an actual phone number is fixed size you can remove it. with sub_strreplace; e.g. numbers are 8 digits long:

$code = substr_replace($number,'',-1,8);

$code now will contain only code part. so you can easily count digits and find out what you need.

Answer 3

The last 3 that signifies a mobile in 393, is that the same for every country?

The ideal situation would be to have a table for countries and then another table with related prefixes

Countries table                        Subsearch Table

countryMatch: 39                       substrMatch: 3 // for 393              
countryName: "Italy"                   substrCountry: 39
                                       substrMeaning: "cell"
                                       ...................
                                       substrMatch: 5 // 395
                                       substrCountry: 39
                                       substrMeaning: "something else"

That way once you have determined the country you can limit the rest of your searches for further limiting e.g. 393, 3939.

I reckon your proposed method is sound, looping through bit by bit till you find a match using SQL queries. So find the country code by popping off the first two digits (39), and if found query the subsearch table for results. With those results loop through appending them to the country code and see if you get a match

$subsearchArr = array("3" => "cell","5" => "something else") # from the database 
$match = false;
$country = 39;

foreach($subsearchArr as $key => $value)
{
  # append $key to $country e.g. 393, 395
  # if this is a match to the string
  # set match to true and do your logic
}

if($match == false) # no match so landline
{
  # logic here if landline
}

I reckon that would work, but I guess i'd have to see the exact data structure to be sure. But yeah two tables is definitely desirable

Answer 4

It may be better to do the comparisons in PHP with simple array loops, even if you have the data in Mysql. Build from the database (and cache) a PHP array of expected country codes and known unique prefixes within each country to differentiate between mobile, landline, areas etc.

For each country code you have, see if the input phone number starts with that code. Once you find the country, remove the country code from the phone number and test the remaining number against a list of known mobile number prefixes in that country. If found, it's mobile. If not found, it's landline.

For instance, in Greece the country code is 30 and all mobiles start with 69 after that. If, however, you're realing with countries where mobile number prefixes are indistinguishable from area codes (such as USA and Canada) you are out of luck.

function checkMSISDN($msisdn) {

    $countries = array(
        'gr' => array(
            'countryPrefix' => '30',
            'mobilePrefix' => '3069',
            'length' => 12,
        ),
        'it' => array(
            'countryPrefix' => '39',
            'mobilePrefix' => '393',
            'length' => 12,
        ),
    ) ;

    foreach ($countries as $countryName => $countryRules)  {

        $msisdnCurrent = $msisdn ;

        $countryPrefix = $countryRules['countryPrefix'] ;
        $fullPrefix = $countryRules['mobilePrefix'] ;

        //remove leading + if any
        if (substr($msisdnCurrent, 0, 1) == '+') {
            $msisdnCurrent = substr($msisdnCurrent, 1) ;
        }

        //remove leading 00 if any
        if (substr($msisdnCurrent, 0, 2) == '00') {
            $msisdnCurrent = substr($msisdnCurrent, 2) ;
        }

        $msisdnLength = strlen($msisdnCurrent) ;
        if ($msisdnLength != $countryRules['length']) {
            //sanity check, not this country
            continue ;
        }

        if (substr($msisdnCurrent, 0, strlen($countryPrefix)) != $countryPrefix) {
            //not this country
            continue ;
        }

        if (substr($msisdnCurrent, 0, strlen($fullPrefix)) != $fullPrefix) {
            //not mobile
            return "isLandline" ;
        }
        else {
            //mobile
            return "isMobile" ;
        }
    }
    return false ;
}