ack! The LIKE argument

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
hurdy gurdy
Forum Commoner
Posts: 40
Joined: Mon Jun 09, 2003 8:19 pm

ack! The LIKE argument

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

REGEXP or SOUNDEX() ?
hurdy gurdy
Forum Commoner
Posts: 40
Joined: Mon Jun 09, 2003 8:19 pm

Post 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
Post Reply