Welcome to WebmasterWorld Guest from 54.224.253.82

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Telephone number search string

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

New User

joined:Sept 11, 2012
posts:3
votes: 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
1:50 pm on Sept 11, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member eelixduppy is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 12, 2005
posts:5966
votes: 0


How may rows are we talking about here? I would personally find a way to normalize the data as best you can.
1:52 pm on Sept 11, 2012 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10551
votes: 10


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.
1:53 pm on Sept 11, 2012 (gmt 0)

Junior Member

5+ Year Member

joined:July 8, 2010
posts:114
votes: 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.
5:34 am on Sept 12, 2012 (gmt 0)

New User

joined:Sept 11, 2012
posts:3
votes: 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
6:47 am on Sept 12, 2012 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10551
votes: 10


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.
7:26 am on Sept 12, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 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...]
8:55 am on Sept 12, 2012 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10551
votes: 10


(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.
9:08 am on Sept 12, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 0


I was taking the wider view that this thread will eventually be read by people from all over the world. :)
9:33 am on Sept 12, 2012 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10551
votes: 10


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)
5:28 am on Sept 13, 2012 (gmt 0)

New User

joined:Sept 11, 2012
posts:3
votes: 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
8:27 am on Sept 13, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 7, 2003
posts:4783
votes: 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.
10:25 pm on Sept 13, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member g1smd is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2002
posts:18903
votes: 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.