Separate areacode from phone number string with MySQL Database

Question

I have a database with 5000 entries of local area codes to separate the area code from a phone number string. I'm not sure if the code I use is the fastest way to solve these job.

I shrink the number of db entries with the first three digits from phone number string. After this I put the entries found in db in an array and finally search these array for a match.

What do you think guys? Would you do it the same?

<?php
$phonenumber = '03522522492';
$country = 'DE';
$link = mysqli_connect('Host', 'DB', 'Pass','User') or die(mysqli_error());
$db_selected = mysqli_select_db( $link, 'DB');
$fristthree = substr($phonenumber,0,3); 
$array = array();
$query = mysqli_query($link, "SELECT * FROM `Areacodes` WHERE `Country` LIKE '".$country."%' AND `Areacode` like '".$fristthree."%' ORDER BY CHAR_LENGTH(Areacode) ASC");
while($row = mysqli_fetch_assoc($query)){
  $array[] = $row;
}
foreach ($array as $areacode) {
    $subString = substr($phonenumber, 0, strlen($areacode["Areacode"]));
    if ($subString == $areacode["Areacode"]) {
        $phone = $subString." ".substr($phonenumber, strlen($areacode["Areacode"]));
        $location = $areacode["Location"];
    }
}
if (!empty($phone)) {
    echo $phone;
    echo '<br><br>';
    echo $subString;
    echo '<br><br>';
    echo $location;
}

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

Answer 1

Assuming my understanding of the question and the data is correct:

Did I get it right that you want to map your phone number to the area matching the most initial digits? Having 035, 0352, 03522 and 035225 you would expected the location of 035225 for 03522522492?

I would try a SQL-only approach:

SELECT * 
FROM Areacodes 
WHERE Areacode IN ('032','0352','03522','035225') 
ORDER BY CHAR_LENGTH(deliveryAddress_zip) DESC 
LIMIT 0,1

So in PHP you now only need to build the IN-clause. Split the phone number in substrings of suitable length (I guess 3-6 digits for Germany).

Without any validation the snipped could look like:

$phone = "03522522492";

$parts = [];
for ($len = 3; $len <= 6; $len++) $parts[] = substr($phone, 0, $len);
$in = "'" . implode("','", $parts) . "'";

echo $in;
//'035','0352','03522','035225'