MySQL full-text search

If you want to find fragments in multiple table fields use the MySQL full-text search

SELECT * FROM comments WHERE MATCH (`subject` , `comment`)
AGAINST ('+my_word +and_this_word +like_this_word* -not_this_word' IN BOOLEAN MODE);
  • Add full-text index to the table field ALTER TABLE `comments` ADD FULLTEXT (`subject`)
  • Table has to be MyISAM
  • Don't search for stop words (and, or, if...)

This will give you the three most relevant matches to the three words given (skicka, mail, parkera), and sort the result with the best match first.

SELECT *, MATCH(`subject`) AGAINST ('+skicka +mail +parkera') AS relevance FROM comments  WHERE MATCH(`subject`) AGAINST ('+skicka +mail +parkera') > 0 ORDER BY relevance DESC LIMIT 3

Add full text search on title in Drupal. Add the FULLTEXT index to the table field in node table. Then you can use this function to search in titles and get the best match.

<?php
function _get_best_match($title, $type = "client"){
   
        //Remove hyphenation
    $title = str_replace("-", " ", $title);
    $title_parts = explode(" ", $title);
   
    foreach ($title_parts as $key => $part) {
        $fragment .= " +".$part;
    }
    return db_fetch_object(db_query("SELECT *, MATCH(`title`) AGAINST ('%s') AS relevance FROM node WHERE MATCH(`title`) AGAINST ('%s') > 0 AND type LIKE '%s' ORDER BY relevance DESC LIMIT 1", $fragment, $fragment, $type));
   
}

//Example
$title = "Computer Backup Online";
$match = _get_best_match($title, $type = "client");

//[title]     => Zimondo Online Backup
//[relevance] => 15.1350831985474

?>
<pre>
<?php
  print_r($match);
?>
</pre>
<?php

    stdClass Object
(
    [nid] => 848
    [vid] => 848
    [type] => client
    [language] => en
    [title] => Zimondo Online Backup
    [uid] => 5
    [status] => 1
    [created] => 1320322832
    [changed] => 1328203027
    [comment] => 2
    [promote] => 0
    [moderate] => 0
    [sticky] => 0
    [tnid] => 0
    [translate] => 0
    [relevance] => 15.1350831985474
)
?>
Knowledge keywords: 
SQL
MySQL