2

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

hiprakhar
  • 1,007
  • 3
  • 12
  • 15
  • This is usually something you would do on programming language/application level instead. Does it *have* to be inside mySQL? – Pekka Feb 12 '11 at 13:42
  • plz note stackoverflow has chewed away the tags I had placed in the post. Please read accordingly. – hiprakhar Feb 12 '11 at 13:42
  • hi pekka, if it can be inside mysql, that would be great. In php I will have to explode query string in an array, then match each tuple's every field will all the words in the array. so thats 3 nested foreach loops (I guess..) – hiprakhar Feb 12 '11 at 13:45
  • Infact I feel this feature should be added to mysql, because that would help a lot of people. – hiprakhar Feb 12 '11 at 13:47
  • @hiprakhar - one of the benefits of MySQL being open source is that you can actually add this feature yourself if it's really that important to you... and if you think that it will be useful to lots of other people, fork MySQL and distribute your own version with that enhancement – Mark Baker Feb 12 '11 at 17:54
  • @mark-baker +1 :) Sure, I'll see if I can document this feature and report it to MySQL. Lot of us need results to be Google like,with highlighted search text. If they can do highlighting of text with 1 flag, that can actually save hassels. Moreover, highlighting can be heuristic in nature, so that search for michal will also highlight michael. – hiprakhar Feb 13 '11 at 05:48
  • @hipraka I have to say I think this is a bad idea - The job of a database it to return data. IT can be for a console app, a web app, a desktop app, etc - It doesn't need to know. Making the database responssible for FORMATTING the results means the DB needs to know what display mechanism is being used - Or to put it another way, you wouldn't be able to use the same search functionality in a web site as you would in a desktop app. Very bad idea. – Basic Feb 21 '11 at 12:08

3 Answers3

3

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

Basic
  • 26,321
  • 24
  • 115
  • 201
1

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.

Kris Bravo
  • 141
  • 1
  • 6
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
}
hiprakhar
  • 1,007
  • 3
  • 12
  • 15