2
\$\begingroup\$

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.";
}
?>
\$\endgroup\$
6
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ Oct 12, 2017 at 11:29
  • \$\begingroup\$ And, given such a task, I would refuse to solve it directly. There is not enough info to do it well \$\endgroup\$ Oct 12, 2017 at 11:30
  • \$\begingroup\$ 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? \$\endgroup\$ Oct 12, 2017 at 11:52
  • 1
    \$\begingroup\$ @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 \$\endgroup\$
    – soo29
    Oct 12, 2017 at 13:06
  • \$\begingroup\$ @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. \$\endgroup\$
    – soo29
    Oct 12, 2017 at 13:08

1 Answer 1

0
\$\begingroup\$

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'
\$\endgroup\$
5
  • \$\begingroup\$ @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 \$\endgroup\$
    – soo29
    Oct 12, 2017 at 13:10
  • \$\begingroup\$ Ok, so my solution should work. As I said, you just need to generate a suitable IN-clause. \$\endgroup\$ Oct 12, 2017 at 13:13
  • \$\begingroup\$ Having "032, 035, 0352, 03522, 035225, 03254, 032544" in MySQL i would expected the location of 035225 for the phone number string 03522522492? \$\endgroup\$
    – soo29
    Oct 12, 2017 at 13:14
  • \$\begingroup\$ 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. \$\endgroup\$
    – soo29
    Oct 12, 2017 at 13:24
  • \$\begingroup\$ I added the php part \$\endgroup\$ Oct 12, 2017 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.