编写sql搜索引擎查询

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')