help in the "WHERE" syntax

[bind727]

here is what I am trying to do which I am having no luck with finding the syntax on mysql.com

 
 $where .= " AND SUBSTRING(c.home_phone,0,4) LIKE '%".$_POST['areacode']."%' ";

the idea is to search a phone number for a user supplied areacode.

Any suggestions are welcome and I know you guys have some creative ones.

Joel

[r937]

this will probably be easy to solve but we need more information first

what are some sample values in the home_phone field?

456-456-4567
4564564567
3456781234
(456) 456-4567
(123) 456-4567
345-3456
234.234.2345
2345671234

unless there is some format uniformity, you will have a hard time getting accurate results

for instance, if you were searching for '%456%' you would return false positives from the above data, even if you did restrict yourself to looking at the first four characters

by the way, that syntax you have looks okay

[KDesigns]

It may be easiest if your database was setup with the area code and phone number in separate rows. Then you could do a

$areacodequery = "SELECT * FROM table WHERE areacode = '456'";

$query = mysql_query($areacodequery);

[bind727]

sorry for not giving more information there is a home_phone field. it doesn't have a unified value. I needed to get the substring but it didn't work. What I did was WHERE LEFT(home_phone, 4) LIKE '555%' which now that I look at it wont give me very nice results iether

I will have to do some sql field formating and strip the extra whitespace and chars.......