CODEWITHSUNDEEP
phpmysqlregexphone-number
soo29
soo29

Reputation: 17

PHP: Separate Area Code from Phone number with MySQL Database

I want to separate the area code from a phone number string by using a area code mysql database.

For example the string is 0349152023.
The endresult should be 03491 52023.

To get the endresult, i want to split the string and search every digit in database.
For example 0 and then 3 and then 4 and then take the last found result.

The code i have at the moment is only to prepare the phone number string for futher actions:

$phone1 = preg_replace('/[oO]/', '0', $phone-string);

$phone2 = preg_replace("/[^0-9]/", "", $phone1);

Then i use str_split to cut the string in pieces:

$searchArray = str_split($phone2);

Thanks for your help.

Upvotes: 0

Views: 2198

Answers (5)

Leonardo Gugliotti
Leonardo Gugliotti

Reputation: 56

I think you'd better split your database into a tree, making a table for each digit.
So the third digit could refer to the second, the fourth to the third, and so on until you reach the maximum lenght of the prefix. The last table should include the name of the area.
Following your example, supposing that the maximum lenght of the area code was five digits, the fifth_digit_table should have at least four fields like these:
ID
IDref
Number
Name

10 records may have the same IDref, corresponding to the number "2" at the fourth position, linked to the previous "021" through the fourth_digit_table, the three_digit_table and so on; only one among these records, that with the Number field filled with "9", should have the Name "Haan"; the others, if there aren't any, should have the Name "Solingen".
I hope you will manage to speed up your script.

Upvotes: 0

Rick James
Rick James

Reputation: 142298

A single probe (assuming INDEX(area_code)):

 SELECT ...
     FROM AreaCodes
     WHERE area_code < ?
     ORDER BY area_code DESC
     LIMIT 1;

(Where you bind the $phone_number as a string into the ?)

Upvotes: 0

kscherrer
kscherrer

Reputation: 5766

step 1: select all area codes from db and put them into an array $areaCodes

step 2: iterate over $areaCodes as $code and check if the phonenumber starts with $code. if it does, create a string that has a whitespace between the code and the rest of the number

$phonenumber = '0349152023';

$preparedPhonenumber = '';
foreach($areaCodes as $code){
    if(str_pos($phonenumber, $code) === 0){
        // phonenumber starts with areacode

        $phoneWithoutCode = substr($phonenumber, strlen($code));
        $preparedPhonenumber = $code.' '.$phoneWithoutCode;

        break;
    }
}
// if one of the areaCodes was 0349,
// the variable $preparedPhonenumber is now '0349 152023'

edit: you can shorten the amount of returned area codes from db by selecting only those that start with a certain string.

Let's assume the shortest area code in germany is 3 digits long (which i think is correct).

$threeDigits = substr($phonenumber,0,3);
$query = "SELECT * from areacodes
         WHERE code like '".$threeDigits."%'
         ORDER BY CHAR_LENGTH(code) DESC";

this will drastically shrink down the probable area codes array, therefore making the script faster.

edit 2: added order by clause in query so the above code will check for longer areacodes first. (the break; in the foreach loop is now obligatory!)

Upvotes: 0

soo29
soo29

Reputation: 17

Hi Leonardo Gugliotti and Cashbee

i sort the areaCodes to get a better match. The php scripts works fine, but takes to long time to handle 5000 MySQL entries. Is it possible to make the foreach search directly in mySQL?

<?php
$sample_area_codes = array( '0350', '034', '034915', '03491', '0348', '0349', '03491', '034916', '034917',);
sort($sample_area_codes);

$phone_string = '0349152023';

foreach ($sample_area_codes as $code) {
    $subString = substr($phone_string, 0, strlen($code));
    if ($subString == $code) {
        $phone = $subString." ".substr($phone_string, strlen($code));
    }
}

if (!empty($phone)) {
    echo $phone;
}

else {
    echo "No AreaCode found.";
}
?>

Output: 034915 2023, which is correct

Upvotes: 0

Leonardo Gugliotti
Leonardo Gugliotti

Reputation: 56

You may build an array containing all the area codes. Then you may write something like this:

foreach ($area_codes as $code) {
  if (substr($phone, 0, strlen($code)) == $code) {
    $phone_string = substr($phone, 0, strlen($code))." ".substr($phone, strlen($code));
  }
}

You can obviously add a controller in order to verify if the area code was found or not.

Upvotes: 1

Related Questions