Search code examples
phpsqlpostgresqlsearch-engine

Writing sql search engine query

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.

Solution

  • You can append a space before an after the search term as well as to the pattern:

    WHERE ' '||title||' ' ILIKE '% ".$word." %' ESCAPE '|'