Page 1 of 1

ack! The LIKE argument

Posted: Tue Mar 07, 2006 1:44 pm
by hurdy gurdy
Hello all

I'm implementing a "simple" search mechanism to help users find store locations near them. However, I'm having problems with my SQL syntax and I'm hoping that someone here can point me in the right direction.

The problem with the below query is that LIKE is too literal, it only returns rows that match the criteria exactly. I would like some room to fudge around a bit to account for misspellings or similar row results but not exactly the same.

Below is the query I'm using, the $criteria variable is supplied by the user from a form field.

Code: Select all

$selLoc = "SELECT * FROM TableRestaurants WHERE strZip LIKE '%".$criteria."%' ORDER BY strZip ASC";
I realize that the query is specifically looking for $criteria in the strZIP column. I need this query to be more lenient. For example if I entered 75214 as the criteria I would not get a result of 75215 or 51274.

Is what I'm asking for impossible?

Thanks in advance
HG

Posted: Tue Mar 07, 2006 1:55 pm
by feyd
REGEXP or SOUNDEX() ?

Posted: Tue Mar 07, 2006 2:57 pm
by hurdy gurdy
Thanks!

Here's a stupid question for ya... Whats the proper way to insert a variable into a regular expression?

For Example (this may work in my case) :

Code: Select all

SELECT * FROM `TableRestaurants` WHERE strZip REGEXP '^VARIABLEGOESHERE.*$'
The above SELECT statement may work for me in the short term while I do more research on a more robust solution. However I'm not familiar with regular expressions and I want to make sure my MySQL query is "proper". Whats the right syntax to insert a variable in the reguilar expression above?

Thanks again