Highlight matched words MySQL FULLTEXT index

1.8k views Asked by At

MySQL FULLTEXT index trouble. There are:

  1. Table with words like:
ID WORDS
1 jfjdeur,rjjghfje,rioogr
2 fkjtifdfe,lerkr
3 kfrkriti
4 frlerkti,tykitriero,frorodfl,rfjkrjr
...
N fjfjtiu,frkrker,fkdkri
  1. MySQL FULLTEXT index on WORDS column.

  2. Query like:

SELECT *
FROM `table`
WHERE match(`words`) against ('gjfjdeur,rioogr,tykitriero')

As the result returns:

ID WORDS
1 gjfjdeur,rjjghfje,rioogr (because contains gjfjdeur and rioogr)
4 frlerkti,tykitriero,frorodfl,rfjkrjr (because contains tykitriero)

Is it possible to rewrite query to create additional column which will be contain found/matched word? Something like:

ID WORDS FOUND
1 gjfjdeur,rjjghfje,rioogr gjfjdeur,rioogr
4 frlerkti,tykitriero,frorodfl,rfjkrjr tykitriero

Maybe something like:

SELECT *, some_function_to_select_matched_range(`word`) as found
FROM `table`
WHERE match(`words`) against ('gjfjdeur,rioogr,tykitriero')

In realily, table.words consists of hundreds words and preg_match_all in php isn't a good solution to select the matched words from each found.

0

There are 0 answers