PHP mysql search queries

Question

I'm trying to create a search engine for an inventory based site. The issue is that I have information inside bbtags (like in [b]test[/b] sentence, the test should be valued at 3, whereas sentence should be valued at 1).

Here is an example of an index:
My test sentence, my my (has a SKU of TST-DFS)
The Database:

|Product|  word  |relevancy|
|   1   |   my   |    3    |
|   1   |  test  |    1    |
|   1   |sentence|    1    |
|   1   | TST-DFS|    10   |

But how would I match TST-DFS if the user typed in TST DFS? I would like that SKU to have a relevancy of say 8, instead of the full 10..

I have heard that the FULL TEXT search feature in MySQL would help, but I can't seem to find a good way to do it. I would like to avoid things like UNIONS, and to keep the query as optimized as possible.

Any help with coming up with a good system for this would be great.

Thanks, Max

Answer 1

But how would I match TST-DFS if the user typed in TST DFS?
I would like that SKU to have a relevancy of say 8, instead of the full 10..

If I got the question right, the answer is actually easy.
Well, if you forge your query a little before sending it to mysql.

Ok, let's say we have $query and it contains TST-DFS.

Are we gonna focus on word spans? I suppose we should, as most search engines do, so:

$ok=preg_match_all('#\w+#',$query,$m);

Now if that pattern matched... $m[0] contains the list of words in $query.
This can be fine-tuned to your SKU, but matching against full words in a AND fashion is pretty much what the user presumes is happening. (as it happens over google and yahoo)

Then we need to cook a $expr expression that will be injected into our final query.

if(!$ok) { // the search string is non-alphanumeric
  $expr="false";
} else {   // the search contains words that are no in $m[0]
  $expr='';
  foreach($m[0] as $word) {
    if($expr)
      $expr.=" AND ";  // put an AND inbetween "LIKE" subexpressions
    $s_word=addslashes($word); // I put a s_ to remind me the variable
                                 // is safe to include in a SQL statement, that's me 
    $expr.="word LIKE '%$s_word%'"; 
  }
}

Now $expr should look like "words LIKE '%TST%' AND words LIKE '%DFS%'"

With that value, we can build the final query:

$s_expr="($expr)";
$s_query=addslashes($query);

$s_fullquery=
"SELECT (Product,word,if((word LIKE '$s_query'),relevancy,relevancy-2) as relevancy) ".
"FROM some_index ".
"WHERE word LIKE '$s_query' OR $s_expr";

Which shall read, for "TST-DFS":

SELECT (Product,word,if((word LIKE 'TST-DFS'),relevancy,relevancy-2) as relevancy)
FROM some_index
WHERE word LIKE 'TST-DFS' OR (word LIKE '%TST%' AND word LIKE '%DFS%')

As you can see, in the first SELECT line, if the match is partial, mysql will return relevancy-2

In the third one, the WHERE clause, if the full match fails, $s_expr, the partial match query we cooked in advance, is tried instead.