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

Asked

Viewed 312 times

2

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

Lorem ipsum dolor sit Amet, consectetur adipisicing Elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad Minim veniam, wanted nostrud exercitation ullamco laboris nisi ut aliquip ex e 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 read the word reprehenderit, I want in my application, in PHP/HTML display something similar to this:

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

I’ve been reading about the consultation FULL-TEXT, but I need you 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 consulted term.

How do I do?

php html mysql
  • 1

    This type of functionality is often at the limit of what supports FULL-TEXT on relational databases can do it efficiently. Even if you find a smart solution to filter the text, in general I recommend you start thinking about using your own indexing and search tool like Elasticsearch or Apache Solr.

  • Only one SQL query serves? Do you need to be "Words" before and after? Cannot simply be a fixed piece (can cut the first/last)?

  • @Ismael can cut yes, man. I delete the first and the last already in the application. No problem. If you want to formalize an answer with your solution, thank you, but I believe it’s the same idea as Pope Charlie.

1 answer

1


From what I found, you can make a combination of LOCATE and SUBSTRING to cut 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 a 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 pos in any of the Forms of this Function

You can do something in this logic and change the amount 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 don’t remember if it has any function that avoids cutting words, as with the SUBSTRING that cuts lorem : rem, in any case, you can increase the limit and cut in PHP.

  • 1

    I’m sure that’ll do the trick. I can increase the number of characters, divide the string into an array based on the spaces and undo the first and last index, since it would be few words. Helped a lot! ;)

  • 1

    I’ll find a replacement for SUBSTRING and avoid cutting the word in half, if find do a reply update.

Browser other questions tagged php html mysql

You are not signed in. Login or sign up in order to post.