Can MySQL full-text search return index (position) instead of evaluation?

Question

I would like to use the position / index found in Match ... Against the full-text search in mysql to return the text before and after the match in the field. Is it possible? In all the examples I've seen, Match ... Against returns the score in select instead of the location or position in the text field in which the search is being performed.

SELECT random_field, MATCH ($search_fields) AGAINST ('".mysql_real_escape_string(trim($keywords))."' IN BOOLEAN MODE) AS score FROM indexed_sites WHERE MATCH ($search_fields) AGAINST ('".mysql_real_escape_string($keywords)."' IN BOOLEAN MODE) ORDER BY score DESC; 

This will give me a field and an estimate ... but I would like the index / position to be evaluated instead (or along).

Answer 1

Full-text search is a scoring function. this is not a search for the appearance function. In other words, the highest scoring result may not have a starting position for the match. Since this may be a combination of weighted results of different matches in the text. if you enable the query extension, the search for the word / s may not even appear as a result! http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html

Hope this makes sense.

In any case, it is best to take the results and then use some text search function to find the first occurrence of the first matching word. I suggest that is best suited for a word processing language such as perl, or a more general language such as php, or what you use to run a query.

DC