freelanceprogrammers.org Forum Index » PHP
Mysql Finding the country name from country prefix
Joined: 18 Mar 2005
Posts: 21
Mysql Finding the country name from country prefix
Hi Friends,
I have a ticky mysql problem.
I need to find the country name from the country prefix so eg. i have a mysql
table with prefix and name as the coumns and with datas like :
Prefix, Name
1 USA
11 XYZ
44 UK
91 India
92 Pakistan
123 ZXF
and i have a number like 911122334455 and i need t match that to india.
I cannt do that directly by this statement select name from country_table where
prefix=`91`; for the simple reason as i do not have the length or the no. of
digits the prefix is beforehand.
Pl. help me out.
Quick help will be appreciated.
--
Regards
Abhishek J
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
[Non-text portions of this message have been removed]
Joined: 06 Sep 2005
Posts: 16
Mysql Finding the country name from country prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have
> a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1Â Â Â Â Â Â Â Â Â USA
> 11Â Â Â Â Â Â Â XYZ
> 44Â Â Â Â Â Â Â UK
> 91Â Â Â Â Â Â Â India
> 92Â Â Â Â Â Â Â Pakistan
> 123Â Â Â Â Â ZXF
> and i have a number like 911122334455 and i need t match that to
> india.
> I cannt do that directly by this statement select name from
> country_table where prefix=`91`; for the simple reason as i do not
> have the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
> Â Â Â Â Â Â Â Â Â Â
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.Â
>
> [Non-text portions of this message have been removed]
>
>
>
> PHP Data object relational mapping generator -
> http://www.meta-language.net/
>
>
> YAHOO! GROUPS LINKS
>
> â–ª Â Visit your group "php-objects" on the web.
> Â
> â–ª Â To unsubscribe from this group, send an email to:
> Â php-objects-unsubscribe@yahoogroups.com
> Â
> â–ª Â Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
Joined: 20 Nov 2005
Posts: 4
Mysql Finding the country name from country prefix
For more detailed information on international dialing sequences see
http://en.wikipedia.org/wiki/E.164
----- Original Message -----
From: Michael Slavin
To: php-objects@yahoogroups.com
Sent: Saturday, November 19, 2005 9:29 AM
Subject: Re: [php-objects] Mysql Finding the country name from country prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have
> a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1 USA
> 11 XYZ
> 44 UK
> 91 India
> 92 Pakistan
> 123 ZXF
> and i have a number like 911122334455 and i need t match that to
> india.
> I cannt do that directly by this statement select name from
> country_table where prefix=`91`; for the simple reason as i do not
> have the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
>
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.
>
> [Non-text portions of this message have been removed]
>
>
>
> PHP Data object relational mapping generator -
> http://www.meta-language.net/
>
>
> YAHOO! GROUPS LINKS
>
> â–ª Visit your group "php-objects" on the web.
>
> â–ª To unsubscribe from this group, send an email to:
> php-objects-unsubscribe@yahoogroups.com
>
> â–ª Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
PHP Data object relational mapping generator - http://www.meta-language.net/
------------------------------------------------------------------------------
YAHOO! GROUPS LINKS
a.. Visit your group "php-objects" on the web.
b.. To unsubscribe from this group, send an email to:
php-objects-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------------------------------------------------------------
[Non-text portions of this message have been removed]
Joined: 27 Apr 2005
Posts: 7
Mysql Finding the country name from country prefix
Rather than taking the long number as it is, why not clean it up before
putting it into the MYSQL table, --or add another few columns with the same
data to test the number against? This isn`t so much a MYSQL problem as a
data integrity problem.
I had to do this with phone numbers or various length. Because it was going
to be used in a court proceeding, I had to keep the original data pristine,
yet be able to run tests against it by adding more DB columns.
Also, I notice that the maximum number of prefix digits is 3, so you can
script loop/tests based upon the length of the prefix as well.
If (strlen(prefix) ==3) { test MYSQL data against the first three digits }
elseif(strlen(prefix) ==2){ test MYSQL data against the first two digits }
else { test MYSQL data against the first digit }
Essentially, determine whether you can test the data outside of a SQL query,
amend your DB to clean up the data or whether you HAVE to do it at the DB
level. Seems like you have a few options here.
-----Original Message-----
From: php-objects@yahoogroups.com [mailto:php-objects@yahoogroups.com] On
Behalf Of Michael Slavin
Sent: Saturday, November 19, 2005 9:30 AM
To: php-objects@yahoogroups.com
Subject: Re: [php-objects] Mysql Finding the country name from country
prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have
> a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1 USA
> 11 XYZ
> 44 UK
> 91 India
> 92 Pakistan
> 123 ZXF
> and i have a number like 911122334455 and i need t match that to
> india.
> I cannt do that directly by this statement select name from
> country_table where prefix=`91`; for the simple reason as i do not
> have the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
>
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.
>
> [Non-text portions of this message have been removed]
>
>
>
> PHP Data object relational mapping generator -
> http://www.meta-language.net/
>
>
> YAHOO! GROUPS LINKS
>
> ? Visit your group "php-objects" on the web.
>
> ? To unsubscribe from this group, send an email to:
> php-objects-unsubscribe@yahoogroups.com
>
> ? Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
Joined: 18 Mar 2005
Posts: 21
Mysql Finding the country name from country prefix
Hi TechLDS,
>>If (strlen(prefix) ==3) { test MYSQL data against the >>first three digits }
>>elseif(strlen(prefix) ==2){ test MYSQL data against the >>first two digits }
>>else { test MYSQL data against the first digit }
But how to find the prefix or you mean to say that i need to substr to 3 , 2, 1
strings and then check that will the record exisits for these strings. If yes
then i can get the country name.
Don`t you think the method is quiet inefficient.
However in a seperate mailing list i found a solution as
SELECT name FROM country_table
WHERE `911122334455` REGEXP CONCAT(`^`, prefix);
This perhpas solves mine problem.
Thanks for the help.
--
Regards
Abhishek jain
TechLDS <tech@...> wrote: Rather than taking the long
number as it is, why not clean it up before
putting it into the MYSQL table, --or add another few columns with the same
data to test the number against? This isn`t so much a MYSQL problem as a
data integrity problem.
I had to do this with phone numbers or various length. Because it was going
to be used in a court proceeding, I had to keep the original data pristine,
yet be able to run tests against it by adding more DB columns.
Also, I notice that the maximum number of prefix digits is 3, so you can
script loop/tests based upon the length of the prefix as well.
If (strlen(prefix) ==3) { test MYSQL data against the first three digits }
elseif(strlen(prefix) ==2){ test MYSQL data against the first two digits }
else { test MYSQL data against the first digit }
Essentially, determine whether you can test the data outside of a SQL query,
amend your DB to clean up the data or whether you HAVE to do it at the DB
level. Seems like you have a few options here.
-----Original Message-----
From: php-objects@yahoogroups.com [mailto:php-objects@yahoogroups.com] On
Behalf Of Michael Slavin
Sent: Saturday, November 19, 2005 9:30 AM
To: php-objects@yahoogroups.com
Subject: Re: [php-objects] Mysql Finding the country name from country
prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have
> a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1 USA
> 11 XYZ
> 44 UK
> 91 India
> 92 Pakistan
> 123 ZXF
> and i have a number like 911122334455 and i need t match that to
> india.
> I cannt do that directly by this statement select name from
> country_table where prefix=`91`; for the simple reason as i do not
> have the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
>
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.
>
> [Non-text portions of this message have been removed]
>
>
>
> PHP Data object relational mapping generator -
> http://www.meta-language.net/
>
>
> YAHOO! GROUPS LINKS
>
> ? Visit your group "php-objects" on the web.
>
> ? To unsubscribe from this group, send an email to:
> php-objects-unsubscribe@yahoogroups.com
>
> ? Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
PHP Data object relational mapping generator - http://www.meta-language.net/
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "php-objects" on the web.
To unsubscribe from this group, send an email to:
php-objects-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
[Non-text portions of this message have been removed]
Joined: 20 Nov 2005
Posts: 4
Mysql Finding the country name from country prefix
It`s always looked to me like:
if 1-digit prefix
do NANP
else if 2-digit prefix exists in 2-digit-country-codes
do 2-digit
else if 3-digit prefix exists in 3-digit-country-codes
do 3-digit
----- Original Message -----
From: abhishek jain
To: php-objects@yahoogroups.com
Sent: Sunday, November 20, 2005 12:59 PM
Subject: RE: [php-objects] Mysql Finding the country name from country prefix
Hi TechLDS,
>>If (strlen(prefix) ==3) { test MYSQL data against the >>first three digits }
>>elseif(strlen(prefix) ==2){ test MYSQL data against the >>first two digits }
>>else { test MYSQL data against the first digit }
But how to find the prefix or you mean to say that i need to substr to 3 , 2,
1 strings and then check that will the record exisits for these strings. If yes
then i can get the country name.
Don`t you think the method is quiet inefficient.
However in a seperate mailing list i found a solution as
SELECT name FROM country_table
WHERE `911122334455` REGEXP CONCAT(`^`, prefix);
This perhpas solves mine problem.
Thanks for the help.
--
Regards
Abhishek jain
TechLDS <tech@...> wrote: Rather than taking the
long number as it is, why not clean it up before
putting it into the MYSQL table, --or add another few columns with the same
data to test the number against? This isn`t so much a MYSQL problem as a
data integrity problem.
I had to do this with phone numbers or various length. Because it was going
to be used in a court proceeding, I had to keep the original data pristine,
yet be able to run tests against it by adding more DB columns.
Also, I notice that the maximum number of prefix digits is 3, so you can
script loop/tests based upon the length of the prefix as well.
If (strlen(prefix) ==3) { test MYSQL data against the first three digits }
elseif(strlen(prefix) ==2){ test MYSQL data against the first two digits }
else { test MYSQL data against the first digit }
Essentially, determine whether you can test the data outside of a SQL query,
amend your DB to clean up the data or whether you HAVE to do it at the DB
level. Seems like you have a few options here.
-----Original Message-----
From: php-objects@yahoogroups.com [mailto:php-objects@yahoogroups.com] On
Behalf Of Michael Slavin
Sent: Saturday, November 19, 2005 9:30 AM
To: php-objects@yahoogroups.com
Subject: Re: [php-objects] Mysql Finding the country name from country
prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have
> a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1 USA
> 11 XYZ
> 44 UK
> 91 India
> 92 Pakistan
> 123 ZXF
> and i have a number like 911122334455 and i need t match that to
> india.
> I cannt do that directly by this statement select name from
> country_table where prefix=`91`; for the simple reason as i do not
> have the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
>
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.
>
> [Non-text portions of this message have been removed]
>
>
>
> PHP Data object relational mapping generator -
> http://www.meta-language.net/
>
>
> YAHOO! GROUPS LINKS
>
> ? Visit your group "php-objects" on the web.
>
> ? To unsubscribe from this group, send an email to:
> php-objects-unsubscribe@yahoogroups.com
>
> ? Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
PHP Data object relational mapping generator - http://www.meta-language.net/
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "php-objects" on the web.
To unsubscribe from this group, send an email to:
php-objects-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
[Non-text portions of this message have been removed]
PHP Data object relational mapping generator - http://www.meta-language.net/
SPONSORED LINKS Php developer Basic programming language Computer
programming languages
Programming languages Object oriented programming Object oriented
programming tutorial
------------------------------------------------------------------------------
YAHOO! GROUPS LINKS
a.. Visit your group "php-objects" on the web.
b.. To unsubscribe from this group, send an email to:
php-objects-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
------------------------------------------------------------------------------
[Non-text portions of this message have been removed]
Joined: 27 Apr 2005
Posts: 7
Mysql Finding the country name from country prefix
Yes, it`s inefficient, and not at all recommended.
That`s why my main suggestion was to take another look at controlling your
data: garbage in, garbage out.
:o))
-----Original Message-----
From: php-objects@yahoogroups.com [mailto:php-objects@yahoogroups.com] On
Behalf Of abhishek jain
Sent: Sunday, November 20, 2005 12:59 PM
To: php-objects@yahoogroups.com
Subject: RE: [php-objects] Mysql Finding the country name from country
prefix
Hi TechLDS,
>>If (strlen(prefix) ==3) { test MYSQL data against the >>first three
digits }
>>elseif(strlen(prefix) ==2){ test MYSQL data against the >>first two
digits }
>>else { test MYSQL data against the first digit }
But how to find the prefix or you mean to say that i need to substr to 3 ,
2, 1 strings and then check that will the record exisits for these strings.
If yes then i can get the country name.
Don`t you think the method is quiet inefficient.
However in a seperate mailing list i found a solution as
SELECT name FROM country_table
WHERE `911122334455` REGEXP CONCAT(`^`, prefix);
This perhpas solves mine problem.
Thanks for the help.
--
Regards
Abhishek jain
TechLDS <tech@...> wrote: Rather than taking the
long number as it is, why not clean it up before
putting it into the MYSQL table, --or add another few columns with the same
data to test the number against? This isn`t so much a MYSQL problem as a
data integrity problem.
I had to do this with phone numbers or various length. Because it was
going to be used in a court proceeding, I had to keep the original data
pristine, yet be able to run tests against it by adding more DB columns.
Also, I notice that the maximum number of prefix digits is 3, so you can
script loop/tests based upon the length of the prefix as well.
If (strlen(prefix) ==3) { test MYSQL data against the first three digits }
elseif(strlen(prefix) ==2){ test MYSQL data against the first two digits }
else { test MYSQL data against the first digit }
Essentially, determine whether you can test the data outside of a SQL
query, amend your DB to clean up the data or whether you HAVE to do it at
the DB level. Seems like you have a few options here.
-----Original Message-----
From: php-objects@yahoogroups.com [mailto:php-objects@yahoogroups.com] On
Behalf Of Michael Slavin
Sent: Saturday, November 19, 2005 9:30 AM
To: php-objects@yahoogroups.com
Subject: Re: [php-objects] Mysql Finding the country name from country
prefix
You can remove the last ten digits and look up the result in the mysql
table.
If that end is also variable length, you`re .. um ... sunk.
-m
On Nov 19, 2005, at 6:54 AM, abhishek jain wrote:
> Hi Friends,
> I have a ticky mysql problem.
> I need to find the country name from the country prefix so eg. i have >
a mysql table with prefix and name as the coumns and with datas like :
> Prefix, Name
> 1 USA
> 11 XYZ
> 44 UK
> 91 India
> 92 Pakistan
> 123 ZXF
> and i have a number like 911122334455 and i need t match that to >
india.
> I cannt do that directly by this statement select name from >
country_table where prefix=`91`; for the simple reason as i do not > have
the length or the no. of digits the prefix is beforehand.
> Pl. help me out.
> Quick help will be appreciated.
> --
> Regards
> Abhishek J
>
>
>
> ---------------------------------
> Yahoo! FareChase - Search multiple travel sites in one click.
>
> [Non-text portions of this message have been removed] > > > > PHP
Data object relational mapping generator - > http://www.meta-language.net/
> > > YAHOO! GROUPS LINKS >
> ? Visit your group "php-objects" on the web.
>
> ? To unsubscribe from this group, send an email to:
> php-objects-unsubscribe@yahoogroups.com
>
> ? Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service.
>
>
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator -
http://www.meta-language.net/ Yahoo! Groups Links
PHP Data object relational mapping generator -
http://www.meta-language.net/
---------------------------------
YAHOO! GROUPS LINKS
Visit your group "php-objects" on the web.
To unsubscribe from this group, send an email to:
php-objects-unsubscribe@yahoogroups.com
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
---------------------------------
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
[Non-text portions of this message have been removed]
------------------------ Yahoo! Groups Sponsor --------------------~--> Get
fast access to your favorite Yahoo! Groups. Make Yahoo! your home page
http://us.click.yahoo.com/dpRU5A/wUILAA/yQLSAA/saFolB/TM
--------------------------------------------------------------------~->
PHP Data object relational mapping generator - http://www.meta-language.net/
Yahoo! Groups Links
Joined: 06 Sep 2005
Posts: 16
Mysql Finding the country name from country prefix
True enough. The problem at hand is certainly simple enough:
SELECT country FROM country_prefixes WHERE prefix LIKE $prefix
The trick is simply to define your prefix.
Perhaps a space between, then you can explode your phone number using a
space separator. Or a fixed number of places after that you can simply
chop off. The idea of searching the beginning several digits for a
match is less inefficient than ludicrous, You`d usually end up, I
imagine, with needing to choose the best out of three matches, and if
you have any data that would help make that choice you`d do better to
be looking up with that other data in the first place, seems to me.
-m
On Nov 20, 2005, at 2:06 PM, TechLDS wrote:
> That`s why my main suggestion was to take another look at controlling
> your
> data: garbage in, garbage out.
[Non-text portions of this message have been removed]
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
China Wholesale - Electronics Products
Character Studio - Tutorials and Help