Matching Telephone area codes

[lloydie2]

how would I match a telephone number to it's nearest area code. For example I would like to match 001 876 432 987 to it's nearest country code which is Jamaica and not the shortest match which is US and have only one result.

area_code | country
001| USA
001876 | Jamaica

WhaDaYaTink?

 

[tshot]

it has do with the semantics.

Is there fix lenght of phone nos e.g. totl 10 ten didigt or 12 digit xlcuding the '00' of int'l code.

othere ways u can hav a table of country and their codes. in that case u segergare ur phone nos as country code and no







----------------
ur feedback is a very welcome desire

 

[lloydie2]

The full code and number is being presented as one number. The telephone numbers are of varible length as are the area codes. For instance:

020 87575000 London area code and number
01865 875123 Oxford Area code and number.

I think it would be difficult to easily segregate the codes and numbers as the area codes can have a length anywhere between 2 and 6 digits (at least in the UK), as well as dealing with country codes.

Looking at the "like" operator, I am not sure it is suitable.

 

[tshot]

how would u query using like ?

taking ur above example of Jamica. a person in US can have no (001) 876 432 987 where as some body in Jamica could have no (001 876) 432 987.

when the entire no is stroed in one coulmn then like would result in both the records.

if the data is not very lfatge say a million odd record u can either store te code and no in seprate fields or alternatively add a (2 digit may work ) country id in a separte table keep country code with country id and code. so this 2nd table could be a look up table




----------------
ur feedback is a very welcome desire

 

[lloydie2]

Tshot, I have worked out how to do it.
I use strlen to find the lentgth of the telephone number and give that value to a var.
I then do a for loop which substr the length of the telephone number based on the strlen var and runs a query on the result of the substr. If there is no result, the telephone number length is reduced by one until there is a match.

 

[tshot]

as u said earlier that phone no and code is one single no. then how do u find the code of that country ?

could u please explain it with the above exapmle of USA and Jamica




----------------
ur feedback is a very welcome desire

 

[lloydie2]

I should of mentioned that you will need some kind of script(pdp/perl) or programme(C++/VB/Delphi) the example below is for php, although I will be porting it to Delphi. Look at my previous post for explaination.

<?php
foreach ($_POST as $key => $value) {$$key = $value;}
foreach ($_GET as $key => $value) {$$key = $value;}

include('pageconnect.php');
$connection = @mysql_connect($host, $user, $pass) or die (&quot;Unable to connect to database&quot;
mysql_select_db($db) or die (&quot;Unable to select database: $db &quot;

for ($i = strlen($callno); $i != 0; $i--) {
$areacode = substr($callno, 0,$i);
$query = &quot;SELECT *
FROM stdcodes
WHERE areacode = '$areacode' &quot;;
$queryResult = mysql_query($query);
$numrows =mysql_num_rows($queryResult);

if($numrows >0) {
break;
}
}
$query = &quot;SELECT *
FROM stdcodes
WHERE areacode = '$areacode' &quot;;
$queryResult = mysql_query($query);

if ($queryResult) {
while($row =mysql_Fetch_array($queryResult)) {

echo &quot;the country for $row[areacode] is $row[country]&quot; ;
}
} else {
echo &quot;Query failed. SQL=$selectresult error=&quot;.mysql_error();
}
?>

 

[tshot]

whta i understand from yr code is that u are inpuuting the no and then checking digit by didgit till the match continues. am i on the right track ?




----------------
ur feedback is a very welcome desire

 

[lloydie2]

Yes, It will check digit by digit backwards until there is a match.

 

[tshot]

could u post the structure of ur stdcodes table




----------------
ur feedback is a very welcome desire

 

[lloydie2]

That was in my very first post.

areacodes | country
---------------------
001 | USA
001876 | Jamaica

 

[hos2]

so if you have all the areacodes in a table then you sort it by length, the longest first

select length(areacode) as arealen from areatable order by arealen desc

then you search where there is a match for the first record (with 6 digits). the record you find you fill in the appropiate area or areacode. with the search on the next areacode you exlude the ones you have already updated.