fulltext search and highlighting by PHP and MySQL?

Question

MySQL can take care of fulltext search quite well,

but it doesn't highlight the keywords that are searched,

how can I do this most efficiently?

Answer 1

Get the result set from mysql. Do a search and replace for each search word, replacing each word with whatever you're doing for highlighting, e.g., <span class='highlight'>word</span>

Answer 2

Do your sql query and then do a preg_replace on the result, replacing each keyword with KeyWord

$hilitedText = preg_replace  ( '/keyword/'  , '/<span class="hilite">keyword<\/span>/'  , $row['columName']);

And define the hilite class in your css to format however you want the hilighted keywords to appear. If you have multiple keywords put them in an array and their replacements in a second array in the same order and pass those arrays as the firt two arguments of the function.

Answer 3

The solutions posed above require retrieval of the entire document in order to search, replace, and highlight text. If the document is large, and many are, this seems like a really bad idea. Better would be for MySQL FTS to return the text offsets directly like SQLITE does, then use an indexed substring operator - that would be significantly more efficient.