How to make mysql automatically bold matching words in fulltext search

Question

I have tried to precisely explain my problem in the title itself. Can I surround the matching words in query by in mysql query itself? MySQL query is:

select id, FirstName,LastName,addcomments WHERE MATCH (FirstName,LastName,addcomments) AGAINST ('some sample text' WITH QUERY EXPANSION)

The results from mysql should be like:

id | FirstName       | LastName             | addcomments 

1  |<b>some</b> name | lastname <b>text</b> | comments have <b>some sample text</b> inside

Any help would be deeply appreciated

Answer 1

Thanks kris for the reply. I agree mysql will return generic results that can be consumed by any application. But can I write a mysql function for the same effect?

Also, I tried writing php function for that, but its not working:

$searchitems=explode(" ", $trimmed);
$totalSearchItems = count($searchitems);

while ($row= mysql_fetch_array($result))
{
      for($i=0;$i<$totalSearchItems;$i++)
      {
            str_replace($searchitems[$i], '<b>'.$searchitems[$i].'</b>', $row);
      }
      //Display $row
}

Answer 2

This is a non-question as MySQL doesn't format results at all, it passes a data set to an application. it's then up to the application to do what it wants with that data.

If you're in PHP, you could use str_replace on the result to replace the keyword with a bold version of the keyword

There's also a security concern here - if your database return HTML, you'll have to output the content with encoding. This means that if anyone manages to inject malicious record into your DB, your site could be serving XSS attacks or other nasty code

Answer 3

You will probably find that this will have mixed results for you depending on where the output is displayed. For example, properly escaping the text so it appears bold or colored on the command line is done differently from printing in bold in html, or rtf, or pdf, etc.

If you solve the issue with whatever you are using to display the output you will get more predictable and maintanable results.