2
\$\begingroup\$

I have a database with 5000 entries of local area codes. The code uses those records to compare with the area code from a specific 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 the 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

2 Answers 2

2
\$\begingroup\$

Avoid possible SQL injection

Maybe the values in the variables used in the WHERE conditions won't ever come from user supplied values but it is still a good habit to guard against the possibility of SQL Injection - e.g. with Prepared statements and bound parameters with functions like mysqli_stmt_bind_param().

Loop to fetch all rows from query can be simplified

Instead of using mysqli_fetch_assoc() to get each row and push the row into an array, mysqli_fetch_all() can be used to get the entire rows into an array in one step.

$array = array();
while($row = mysqli_fetch_assoc($query)){
  $array[] = $row;
}

Can be simplified to this, using MYSQLI_ASSOC as the mode parameter:

$array = mysqli_fetch_all($query, MYSQLI_ASSOC);

Naming can be improved to better describe what a variable contains

The foreach iterates over the array as follows:

foreach ($array as $areacode) {

yet each element in the array is an array containing values from a database row. A more appropriate name might be $row. Then when accessing the Areacode field it would be referenced via $row["Areacode"] instead of $areacode["Areacode"]. The latter could be confusing to someone reading the code (including your future self).

\$\endgroup\$
1
\$\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 and acknowledge you have read our privacy policy.

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