complex mysql query help

juschillinnow

I have a database that has phone number stored in it.

the phone number can be stored like either of the following options:

(123)-247-3695
123-247-3695
1232473695

I now want to be able to search for those a phone number by inputing 1232473695 into a search field and clicking enter.

(I know how to do this....thank you for all you smart a**es)

but I wonder if there is a way to do this with a mysql query like:

$query = "select * from my_table where (ereg_replace any ()- in the column phone) = '$phone_number'";

suntra

I can't answer to your question, but I would suggest you to put in your database an unique telephone number format. For example, 4186596600. When you print it in a page, you could "tell" PHP to display it like 418-659-6600.

To do that, use this code :

preg_replace("([^0-9])", "", $str);

It will convert every "not-number"-characters contained in $str to nothing and will return the result.

Like that, it would be much easier ! Don't you think ?

Tilemachos

You can also try the LIKE commands:

"SELECT something FROM table WHERE (phone LIKE '". 
         substr($search_phone, 0, 3) ."%' AND phone LIKE '%". 
         substr($search_phone, 3, 3) ."%' AND phone LIKE '%". 
         substr($search_phone, -4, 4) ."')"

This should give you a query like this:

SELECT something FROM table WHERE (phone LIKE '123%' 
       AND phone LIKE '%247%' AND phone LIKE '%3695')

This means :p that it will select all phones starting with '123' ending with '3695' and anywhere inside the phone is the '247' digits