MySQL full text index, natural lanuage search on the Rich Text field

searain

I used the MySQL full text index, natural language search. It works pretty well.

But now I have this problem.

If the field I am going to use the natural language search on is a "rich text field" which means I saved the html tags within the field too.

Such as "PHP builder". So when I use the natural language search on "PHP builder", due to the html tags, I cannot get the right search results, right?

how to deal with it?

Does MySQL have a built in function like php's strip_tags() to strip off all the html tags? Even MySQL has the strip_tags() function, how can I apply it with the MySQL natural lanague search?

Thanks!

bunner_bob

Boy - that's a toughie. I suppose the fallback position is to store a "raw text" copy of your rich text in another db column. Index that field, and if a search hits, have the link from the results pull up the rich text content instead.

Basically, every access you make to the content would work with the rich content (editing, updating, retrieving, etc.). Only when you do an insert or update would you have PHP duplicate the field and strip tags. The process would be transparent to you and site users - nobody would ever see the raw text.

It's a brute-force solution but I think it would work. The only downside I can see is more data in your tables. How much content are you dealing with?

searain

Yes, I think you are right. Use a field to save the raw text with the html tags stripped off.

http://www.codeproject.com/useritems/FulltextFirebird.asp

this link also pointed that out.

OK, so if I use a second field to hold the raw text for the search, how can I highlight the query words in the result which is html text?

Such as "PHP builder". So when I use the full text search for the "PHP builder" on the duplicated raw text field and grab this record, and return the results in html text. But how can I hightlight the query "PHP Builder" in the result of "PHP builder"

bunner_bob

Perhaps you can use the raw text here again - you'd have to dispense with the rich text formatting, but just for your Search Results view. And anyway, if you were to highlight on top of the rich text you'd have highlight on top of bold (etc.), which could impede readability.

But if you want to highlight within the display of rich text....hmmm....

OK - question is, does fulltext searching search for phrases or just the presence of the individual words? If the latter, you should be able to highlight the words regardless of formatting. "PHP" would be highlighted whether or not it was within tags. But if you're matching phrases and have PHP builder, I don't think you'd be able to highlight it.

Unless you split up the search terms before highlighting and do each word individually. Maybe that's not so bad - you're still doing your SEARCH fulltext, so the results will be relevant; they might just not be highlighted ideally - you might catch a few stray "PHP"s or "builder"s. Maybe a rule that says they have to be within a certain number of characters of each other?

Do fulltext searches actually search on phrases, or just search for the individual words? I should probably know that...

searain

Do fulltext searches actually search on phrases, or just search for the individual words? I should probably know that...

Search the phrase, such as '"PHP Builder"', the query words would be in double quote.