CODEWITHSUNDEEP
phpsqlpostgresqlsearch-engine
webrama.pl
webrama.pl

Reputation: 1890

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.

Upvotes: 0

Views: 207

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324531

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

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

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

Upvotes: 1

Related Questions