needle in haystack search

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
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

needle in haystack search

Post by timoteo »

Hi, I'm trying to implement a search on a database where the column needs to match part of a string (ie needle in haystack) however all the examples I can find have the haystack as the column in database and the needle as the string resolved by

Code: Select all

WHERE `column` LIKE '%{$needle}%'
or something like that.
I need it in reverse - ie the column as needle and the string as haystack. I have tried
column IN (string)
but get problems.
for example I have a list of cities in database and someone types city and country - someone types
new york usa
which I need to match to a column with just new york. I have tried breaking it up which works with simpler examples
'london', 'england'
but that gets more complicated when you create
'new', 'york', 'usa'
then I can't match new york.
If anyone can give me any ideas to match 'new york usa' or 'long city name the federal republic of wherever' etc etc that would be great. like strpos in php.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: needle in haystack search

Post by Christopher »

You need some way to get context. If you split on spaces then you will get "WHERE city LIKE '%new%' OR '%york%' OR '%usa%'". If you can get a comma like "new york, usa" then you can get to "WHERE city LIKE '%new york%' OR '%usa%'. But ultimately you need to know something about the expected search. For example, if it is locations then you might first look at the last word to see if it is a list of expected countries. Then search on the rest.
(#10850)
timoteo
Forum Contributor
Posts: 125
Joined: Sat Jan 08, 2011 6:46 am

Re: needle in haystack search

Post by timoteo »

thanks for the ideas. Previously I had 4 fields for people to fill out - street, town or city, county or state, country - easy but clumsy. Now I want to make it simpler with just one field for people to put whatever they wish - 'central park' or 'central park new york' or 'usa' - etc etc - give them a free reign. Works fine with single words - 'london', ' england' etc.. matching countries might get some of the way, but there are a lot of 2 word streets in 2 word towns and I can't recognise them all. The other option I imagine would be full text search but I would have to change database as using innodb - not sure how it would work. Any other ideas?
Post Reply