Full-text MySQL search - return snippets

Question

I have a MySQL table that contains chapters of books.

Table: book_chapter
--------------------------
| id | book_id | content |
--------------------------

I am currently able to search the content using full-text search like this:

SELECT * FROM book_chapter WHERE book_chapter.book_id="2" AND
MATCH (book_chapter.content) AGAINST ("unicorn hair" IN BOOLEAN MODE)

However, I would like to know if it's possible to search the content and have the results returned in 30 character snippets, just so the user can feel the gist. So for example, if I search for "unicorn hair", I would have a result like this:

-------------------------------------------------
| id | book_id | content                        |
-------------------------------------------------
| 15 |    2    | it isn't unicorn hair. You kno |
 -------------------------------------------------
| 15 |    2    | chup and unicorn hair in soup  |
 -------------------------------------------------
| 27 |    2    | , should unicorn hair be used  |
 -------------------------------------------------
| 31 |    2    | eware of unicorn hair for bal  |

Notice that there are two results from the same record. Is that possible as well?

Answer 1

An improvement to the query by Mike Bryant

If the match is at the beginning of the field, then the SUBSTRING will start from the end.

I just added an IF statement to fix it

SELECT 
    id, 
    book_id, 
    SUBSTRING(
        content, 
        IF(LOCATE("unicorn hair", content) > 10, LOCATE("unicorn hair", content) - 10, 1),
        10 + LENGTH("unicorn hair") + 10
    ) 
FROM 
    book_chapter 
WHERE book_chapter.book_id="2" 
AND MATCH (book_chapter.content) AGAINST ("unicorn hair" IN BOOLEAN MODE)

Answer 2

Try something like this for creating a snippet of the first match of the search phrase plus 10 characters before it and 10 characters after it (this is not 30 characters in length, but may be a better solution depending on the length of the search phrase, i.e. what if your search phrase > 30 characters). This doesn't address your wish to possibly show multiple results for the same record in the result set. For something like that I would almost think you would be best server creating a stored procedure to do the work you want for you.

SELECT id, book_id, SUBSTRING(content, LOCATE("unicorn hair", content) - 10, 10 + LENGTH("unicorn hair") + 10) FROM book_chapter WHERE book_chapter.book_id="2" AND
MATCH (book_chapter.content) AGAINST ("unicorn hair" IN BOOLEAN MODE)

Obviously you would replace "unicorn hair" with whatever your search phrase is in all it's locations.