CODEWITHSUNDEEP
phpmysql
Dipendra Gurung
Dipendra Gurung

Reputation: 5870

Search phone number in database table

I have a phone record tables as

phones(id, number);

It may have values as:-
1, 9801234567
2, 980 1234568
3, 9779801234569
4, 9801234570
5, 977 980 1234 571

If someone search for 980 1234567 (with spaces), I can remove the spaces before running the queries to get the result.

But my problem is when someone search for 9779801234571 with a condition that there is no regular format of number, it must return the last record i.e. 977 980 1234 571. Any idea how to do it efficiently?

Upvotes: 0

Views: 534

Answers (2)

TimoStaudinger
TimoStaudinger

Reputation: 42460

It is probably best to clean the phone numbers from whitespace before you write them into the database. You can easily to this using this function:

$string = preg_replace('/\s+/', '', $string);

Maybe you also have to strip out other characters like - or /.

Then you can use a simple WHERE condition without bells and whistles. This will also significantly improve the performance of your SELECT statement since you don't have to do conversions of your data in order to find the right row.

This is assuming that you fill the database yourself of course. If that's not the case, ignore this advise.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Here is a way to do this:

 where replace(phonenumber, ' ', '') = replace($phonenumber, ' ', '')

Doing this efficiently is another matter. For that, you would have to format your phone numbers in the database in a canonical format -- say by removing all the spaces in an update statement. Then put the number you are searching for in the same format. The query can then use an index on the column.

Upvotes: 2

Related Questions