MySQL Full Text Search - Fragment Return

Question

I have a MySQL table containing book chapters.

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

Currently, I can search for content in full-text search, for example:

 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 is possible to search for content and return results in 30 pieces of characters so that the user can feel the gist. So, for example, if I search for “unicorn hair”, I will have this result:

 ------------------------------------------------- | 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 | 

Please note that there are two results from the same record. Is it also possible?

Answer 1

Improved query by Mike Bryant

If the match is at the beginning of the field, then 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 to create a snippet of the first match of the search phrase plus 10 characters in front of it and 10 characters after it (it's not 30 characters long, but may be the best solution depending on the length of the search phrase, that is, if your search phrase > 30 characters). This does not mean that you want to show multiple results for the same record in the result set. For something like this, I would almost think that it is best to create a server-side stored procedure to do the work you need.

 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 your search phrase in all places.