MySQL Full Text Search Excerpt

Question

I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for.

I initially thought that I could do this with locate() to get the index of the string minus ~10 characters and then substring around 200 characters after but this only works if there is a document that includes these words consecutively. For example:

SELECT  id,created_at, modified_at,
        SUBSTRING(content, (locate('pto request', content))) as excerpt
FROM    search_index
WHERE   MATCH (name,content) AGAINST ('pto request' IN BOOLEAN MODE)

The match clause finds documents that contains the words pto and request that may not be consecutive but the the locate function requires the exact string "pto request" to be somewhere in the document, if it isn't, an excerpt is not generated.

My first thought was to check for 0, if 0 locate didn't match anything so remove a word and try again and keep doing so until a locate finds a match, but this could get really messy. Is there a better way?

Answer 1

If you want exactly the 2 words, do

... AGAINST('"pto request"' IN BOOLEAN MODE)

If you want some context:

SELECT ...
    SUBSTRING(content,
              GREATEST(1, LOCATE('pto request', content) - 20),
              50)
FROM ...