CODEWITHSUNDEEP
phpmysqlinnodbfull-text-search
Nyxynyx
Nyxynyx

Reputation: 63619

Converting a Multi-word search string for use in MySQL FULLTEXT Boolean mode

I am using Innodb's FTS feature for searching my database and returning the results sorted by relevancy. When using Boolean mode, the search string must be connverted to a form where the individual terms must be seperated by some operators like +, ~, -, *.

If I have a search string with multiple word, how can I (using PHP perhaps) to convert the search string so I can use it with the MATCH()... .AGAINST() clause?

Search term:

stack over flow

SQL Query:

SELECT * FROM mytable WHERE 
    MATCH ( name, description) 
    AGAINST ('+*stack* +*over* +*flow*' IN BOOLEAN MODE);"

Matched results

stack over flow
haystack hover flowers

Upvotes: 1

Views: 1705

Answers (2)

user557846
user557846

Reputation:

for fuzzy search i do:

$search_words = explode(' ', $search);
foreach ($search_words as $foo) {
    $sql_search .= '*' . $foo . '* ' ;
}

or regular:

$search_words = explode(' ', $search);
$sql_search = '+' . implode(" +", $search_words);

Upvotes: 1

Dejan Marjanović
Dejan Marjanović

Reputation: 19380

$string = 'stack over flow';

$string = preg_replace('#\b(\S+)\b#', '+*\\1*', $string); # \b can be ommited

echo $string; # +*stack* +*over* +*flow*

One of the solutions, if that is what you are looking for.

Upvotes: 0

Related Questions