MySQL How to return only part of the result of the FULL-TEXT query

Navigation
2

Let's say I have this text stored in a table in my database:

  

Lorem ipsum dolor sit amet, consectetur adipisicing elit, eiusmod sed   tempor inciudunt ut labore et dolore magna aliqua. Ut enim ad minim come,   wanted nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo   consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse   cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non   proident, sunt in culpa qui officia deserunt mollit anim id est laborum.

If I query the word reprehenderit , I'd like something similar to this in my application in PHP / HTML

:

>
  

... aute irure dolor in reprehenderit in voluptate velit esse ...

I've been reading about the FULL-TEXT query, but I need to return the words around the term of the query instead of the whole text. It would be three or four words before and after the query term.

How do I do it?

    
asked by anonymous 04.05.2017 / 11:15

1 answer

1

From what I've found, you can make a combination of LOCATE and SUBSTRING to make the cut of the text based on the position of the word.

  

LOCATE

     

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if substr or str is NULL

  

SUBSTRING

     

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return to substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for any of the forms of this function

You can do something in this logic and change the number of characters left and right from the word position. I used + 10 as an example. 

set @p = (SELECT LOCATE('dolor', 'Lorem ipsum dolor sit amet'));
SELECT SUBSTRING( 'Lorem ipsum dolor sit amet' , @p - 10 , @p + 10 );

output : rem ipsum dolor sit ame

I do not remember if you have any function that prevents cutting of words, as with SUBSTRING that cuts lorem : rem , in any case you can increase the limit and make the cut in PHP. p>     

04.05.2017 / 20:30
mysql and php - subtract the last two records from the same column and divide by the last record from another column How to retrieve the body and header of a POST transaction in PHP?