Search code examples
mysqlregexfull-text-searchmatchagainst

How to get the exact matched keyword in MySQL MATCH() AGAINST() search?

I am performing a search in mysql using MATCH() and AGAINST() function with multiple keyword. How to know which keyword matched for the search.

SELECT ... , MATCH(n.s_body_from_rss, n.s_full_body) AGAINST("LIST OF KEYWORDS' IN BOOLEAN MODE") AS relevance

This relevance show a numeric value rather than the matched keyword.

Any suggestions most welcome.

Solution

  • Get the result array and then using loop (for|foreach) in php match the string using the same keyword list in preg_match_all() i.e. [assuming KEYWORD_LIST is in ',' separated value]

     preg_match_all('/\b'.str_replace(',','\b|\b', **KEYWORD_LIST**).'\b/iu',$string,$match);
 $matched_keyword = array_unique($match[0]);

    the $matched_keyword array will provide the desire matched keyword list.

    This is a probable solution in php instead of MySQL query itself