# Separate areacode from phone number string with MySQL Database

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."; } ?> • note that even your trick with ordering codes from the longest to shortest may give a false positive, as the actual phone number may start from the remainder of the longer code, i.e. there are two codes, 03254 and 032. Given the phone number is (032)5442211, it will detect the wrong area code. Oct 12, 2017 at 11:29 • And, given such a task, I would refuse to solve it directly. There is not enough info to do it well Oct 12, 2017 at 11:30 • Did I get it right that you want to map your phone number to the area matching the most initial digits? Having 032, 0325, 03254 and 032544 you would expected the location of 032544 for 03522522492? Oct 12, 2017 at 11:52 • @YourCommonSense: Hi Thanks for your response and your note. If a areacode 03254 exists the call number of 032 can't start with 5 in Germany. But you are right, maybe the problem can appear in other countrys Oct 12, 2017 at 13:06 • @mheinzerling: Yes. I have a string for example "03522522492" and spilt the string into areacode and call number. In germany we have 5000 different areacodes with digits from 3 to 6. Oct 12, 2017 at 13:08 ## 1 Answer 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'
• @Did I get it right that you want to map your phone number to the area matching the most initial digits? Yes that's right Oct 12, 2017 at 13:10
• Ok, so my solution should work. As I said, you just need to generate a suitable IN-clause. Oct 12, 2017 at 13:13
• Having "032, 035, 0352, 03522, 035225, 03254, 032544" in MySQL i would expected the location of 035225 for the phone number string 03522522492? Oct 12, 2017 at 13:14
• I don't understand your line "WHERE Areacode IN ('032','0325','03254','032544') " how do you get to the digits? The script starts with the entry of the phone number, for example 03522522492. Oct 12, 2017 at 13:24
• I added the php part Oct 12, 2017 at 13:34