Page 1 of 1

needle in haystack search

Posted: Fri Sep 09, 2011 5:13 pm
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.

Re: needle in haystack search

Posted: Sat Sep 10, 2011 12:48 am
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.

Re: needle in haystack search

Posted: Sat Sep 10, 2011 4:31 am
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?