I'm trying to write a script which is searching in database using every single word of query provided by user.
For example query could be: "Nike Black Hat"
In my script that string is split by space by PHP and then passed to SQL query in that form:
foreach($query as $word)
{
$searchQuery[] = " title ILIKE '%".$word."%' ESCAPE '|' ";
}
The problem is that those query is able to find words like semi*Black* or *Hat*ed. I don't want that so what I did was adding spaces:
WHERE title ILIKE '% ".$word." %' ESCAPE '|'
Now the problem is that if user query is "Nike red shorts", script will find nothing because "Nike" don't have a space before in database. I don't know how to resolve it - find only by whole words including words which don't begin or end with space.
You can append a space before an after the search term as well as to the pattern:
WHERE ' '||title||' ' ILIKE '% ".$word." %' ESCAPE '|'
Use PostgreSQL's full text search support for this kind of job.
See full text search. There's pretty good Rails support for it apparently, but I don't really speak Rails and can't find any good links for it.
In SQL, it'd be something like:
to_tsvector(title) @@ to_tsvector('Nike Black Hat')