homepage Welcome to WebmasterWorld Guest from 54.196.195.38
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Telephone number search string
Rynaard




msg:4493765
 1:40 pm on Sep 11, 2012 (gmt 0)

I have a site that allows users to search a database, one of the search criteria can be a telephone number now the problem is the previous developer did not format the telephone when it was inserted into the database and the users also search for the numbers in different formats (for example XXX-XXX-XXXX and XXX XXX XXXX and XXXXXXXXXX), how can I get the number from the database without worrying about the format of the number to prevent empty search results

 

eelixduppy




msg:4493777
 1:50 pm on Sep 11, 2012 (gmt 0)

How may rows are we talking about here? I would personally find a way to normalize the data as best you can.

phranque




msg:4493781
 1:52 pm on Sep 11, 2012 (gmt 0)

welcome to WebmasterWorld, Runaard!

ideally you would have canonicalized the format of all phone numbers as part of cleansing and validating all user input values before inserting into your database.

you should read about SQL injection and test your forms to see if your application accepts malicious input.


in your situation i would use a regular expression in the where clause of the sql query.

lostdreamer




msg:4493785
 1:53 pm on Sep 11, 2012 (gmt 0)

I agree with eelix,

You could either use replace / regexp functions to only search for the numbers in the string, but this would run the replace or regexp function on all rows, not just the ones you want.
This could become pretty slow pretty fast.

If you could get all the phonenumbers as number only, it would be a lot better.

Rynaard




msg:4494084
 5:34 am on Sep 12, 2012 (gmt 0)

thanks for your replies, I ran the replace() sql function to normalize the database, but now i am struggling to format the user input to standardize it in the form XXXXXXXXXX

phranque




msg:4494102
 6:47 am on Sep 12, 2012 (gmt 0)

to be a valid number it's got to have 10 digits or a 1 followed by 10 digits.
remove all non-numeric characters, remove the leading 1 if any, if it's not 10 digits, reject the input.
you might go further and validate the area code.

g1smd




msg:4494123
 7:26 am on Sep 12, 2012 (gmt 0)

to be a valid number it's got to have 10 digits or a 1 followed by 10 digits.

Outside of the US and Canada and a small number of other countries that solution fails.

Check out [aa-asterisk.org.uk...] for UK phone number format details for example.

The solution to the original problem can probably be found in libphonenumber at: [code.google.com...]

phranque




msg:4494155
 8:55 am on Sep 12, 2012 (gmt 0)

(for example XXX-XXX-XXXX and XXX XXX XXXX and XXXXXXXXXX)

of course you are correct but i was basing my assumption on the example provided in the OP.

g1smd




msg:4494159
 9:08 am on Sep 12, 2012 (gmt 0)

I was taking the wider view that this thread will eventually be read by people from all over the world. :)

phranque




msg:4494173
 9:33 am on Sep 12, 2012 (gmt 0)

the libphonenumber option is interesting for international applications if you are using a supported language but i didn't see a port to PHP, for example. =8)

Rynaard




msg:4494598
 5:28 am on Sep 13, 2012 (gmt 0)

the numbers are in South African format, most people here write the number in the form XXX XXX XXXX, but XXXXXXXXXX would be better for a database, there is some external program that rolls back my 'replace()' changes to the database :( back to square one

swa66




msg:4494625
 8:27 am on Sep 13, 2012 (gmt 0)

What you could do is to just store the digits (carefully not to lose any leading 0 -internationally that's possible-) and just remove all other puntuation from it. store it so that you also have the country code in it (deduce it from the country and try to validate it. Let the users input it as they like, even assist them with some javascript maybe to get it to them as familiar as possible) just remove all but the digits.

Now all you need to do upon output is format it in a way the user seeing it is expecting the phone number to be formatted (might be locale or use specific (local numbers/ international exit code / ...)

The big advantage is that you have a specific and generic format in your database.
e.g.:

input -> stored
1-800-flowers -> 18003569377
+32/485.11.11.11 -> 32485111111

Now as you learn locations, you learn mistakes people make on input, correct those.
E.g. some people assume because their internationel exit code is 00, that that is the case everywhere -> correct it.Or if you know that there is a discrepancy between (foolish) local traditions and actual numbers one can dial, you can correct it too:

e.g.:
0032475.11.11.11 -> 32471111111
+32(0)495.11.11.11 -> 32491111111

You can also add back missing info by e.g. adding back in the country code from an address:
Belgium 011/11.11.11 -> 3211111111
USA (212)555-5555 -> 12125555555


Be careful with checking lengths: e.g. in Germany (just like in Belgium) the length of a phone number is not fixed. And some countries have such a small population that their phone numbers look suspiciously short.

On output: if you know what/where it is going to be used, you can either output locally written numbers or a full international one as appropriate.

I've run multiple times into an application that is too US centric to be used properly and demanding a phone number in a US format - they'll usually end up with a fake one is they insist on not accepting my real one.

g1smd




msg:4494925
 10:25 pm on Sep 13, 2012 (gmt 0)

And some countries have such a small population that their phone numbers look suspiciously short.

The shortest number on the planet that I'm aware of is something like +290 3555 - and that includes the country code.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved