Using the like statement

[Oxford]

Hi, sorry if this is a newbie question, but I'm trying to figure out how to do the following with a like statement. Maybe a like statement isn't what I need.

Using mysql db I have the following sql statement:
"SELECT * FROM databasename WHERE city like '%new york city%'"

Now, the city of new york is called just that, "New York" not "new york city". Some people are entering new york city in a form and no results are being returned. How can I harvest the database to return "new york" when either new york or new york city is entered?

Thanks for your help!

[Frank_Rizzo]

You could filter out the string 'city' before searching.

$search = trim(str_replace(' city', '', $search));
$query = "SELECT * FROM databasename WHERE city like '%$search%'"

[Oxford]

Thanks for your help Frank! That would work for this particular instance.

[rocknbil]

Right, for that particular instance, but not all. :-)

There are a variety of approaches, you could do a cross-references search lookup table storing common mistyped commands, etc.; exploration of the mysql soundex() [dev.mysql.com] ("sounds like") function might help, but all of these are still going to be unreliable and prone to user error.

I'd probably do one of two things, the second being my preference. First is to just "handle it" - split up the submitted term on spaces, then nest the words in an or

where (field like '%word1%' or field like '%word2%' or field like '%word3%')

Which will eliminate "city" from the equation but gives you other problems - new jersey, for example . . . You could complicate this with nested 'ands'

((field like '%word1%' and field like '%word2%') or . . . .
but now we're back where we started in some cases. So this will likely be extremely unreliable.

The best scenario from a user standpoint would be to do a drilldown using select lists: select country->select state->select city. This is a bit of a pain too, you have to populate database tables with is a huge enough job itself, and adds steps for the user. But it's the only way to eliminate errors from user input.

Soundex() might provide some help, don't know.

A side note, I've seen some cases where like is not responding to case insensitivity. It might be better to use regexp:

"SELECT * FROM databasename WHERE city regexp 'new york%'"