Matching telephone numbers against Area Codes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Matching telephone numbers against Area Codes

Post by lloydie-t »

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?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Well, just from the top of my head, you could perhaps;

Starting with the query:

Code: Select all

select country from table where area_code like '$search%'
...combined with:

Code: Select all

$search = str_replace(' ', '', '001 876 432 987');

    echo strlen($search).' chars long<br>';
    echo $search.' is the original without spaces<p>';

    for ($i = strlen($search); $i != 0; $i--) {
        echo substr($search, 0,$i)."<br>\n";
        // good place for query here along with more code, a break, verification etc.
    }
...might give you some more ideas. Basicly, I thought of reading the areacode from right-to-left trying to match it each time with the database. If you get a mysql_num_rows() = 1, you found something worth using....
Just ideas, and untested.
User avatar
m3mn0n
PHP Evangelist
Posts: 3548
Joined: Tue Aug 13, 2002 3:35 pm
Location: Calgary, Canada

Post by m3mn0n »

Google search: [google]country area code listing[/google]
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Thanks Jam for your reply. Reading the string I think could be a problem where some part of it has the same pattern as the left side ie:

001 876 456 001 would be seen as USA as the is a match 001 when in fact it is jamaica. I am slowly coming to the conclusion that I will have to get a fixed length of say 6 digits and compare these to a table where all the codes are six digits by adding extra digits to the

Usa 001
001202 | USA
001203 | USA
001204 | USA
001205 | USA
001etc | USA
001876 | Jamaica

After saying that I have just had a look at the code tables provided by an PTO and some of the codes go upto 10 digits ie: Germany Mobile = 00491721212
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Yah, I thought I solved that with the right-to-left idea. My result (with the code in prev. post):

Code: Select all

12 chars long
001876432987 is the original without spaces

001876432987
00187643298
0018764329
001876432
00187643
0018764
001876 <-- Jamaica
00187
0018
001 <-- USA
00
0
Have in mind that I would do a quary against each of them (ugly, yah so...) so it would likely get a hit at the Jamaica'n one before the USA one. Or have I totaly lost it? ;)
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Jam I just got what you are trying to do. I did not read your code well enough.
I assume that you are comparing the number to the database and if there are no entries then you reduce the string length by one until there is a match.

If that is the case then it would be OK
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Yah well, I havn't worked with area codes anytime before, so I was unsure on how they work/look. Hope it helped or at least gave you some ideas.
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

Thanks Jam, The following code seems to work perfect:

Code: Select all

for ($i = strlen($callno); $i != 0; $i--) { 
        $areacode = substr($callno, 0,$i); 
        $query = "SELECT COUNT(distinct areacode) as numrows  
FROM stdcodes 
WHERE areacode = '$areacode' ";
$queryResult = mysql_query($query);

if( !($row = mysql_Fetch_array($queryResult)) ) { }
    else {
        break;
    }
}

$query = "SELECT *  
FROM stdcodes 
WHERE areacode = '$areacode' ";
$queryResult = mysql_query($query);

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

echo "the country for $row["areacode"] is $row["country"];
       }
    } else {
        echo "Query failed.  SQL=$selectresult  error=".mysql_error();
    }
lloydie-t
Forum Commoner
Posts: 88
Joined: Thu Jun 27, 2002 3:41 am
Location: UK

Post by lloydie-t »

fixed

Code: Select all

<?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 ("Unable to connect to database");
		mysql_select_db($db) or die ("Unable to select database: $db ");
		
for ($i = strlen($callno); $i != 0; $i--) { 
        $areacode = substr($callno, 0,$i); 
        $query = "SELECT *  
FROM stdcodes 
WHERE areacode = '$areacode' ";
$queryResult = mysql_query($query);
$numrows =mysql_num_rows($queryResult);

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

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

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