CODEWITHSUNDEEP
phpmysql
Tom Smykowski
Tom Smykowski

Reputation: 26129

How to get part of text, string containing a phrase/word?

I have a database in MySQL that i process with PHP. In this database i have a column with long text. People search for some phrases with the search tool on the website. It displays items matching this search.

Now, my question is how to get a part of the text that contains the phrase they search for so that they can see if it's what they looking for?

For example:

Text: "this is some long text (...) about problems with jQuery and other JavaScript frameworks (...) so check it out"

And now i would like to get for phrase jQuery this:

about problems with jQuery and other JavaScript frameworks

?

Upvotes: 1

Views: 503

Answers (3)

Mike Mudimba
Mike Mudimba

Reputation: 1

You can use strpos() to find the first occurrence of the phrase you a looking for. Then do a subtraction backwards to get a number less than yo first occurrence. Then call mb_strimwidth(). Here is an example code

we will search for the word 'website'. 

//Your string
$string = "Stackoverflow is the best *website* there ever is and ever will be. I find so much information here. And its fun and interactive too";

// first occurence of the word '*website*' - how far backwards you want to print
$position=intval(strpos($string, '*website*'))-50;
$display_text=mb_strimwidth($string, $position, 300, '...<a href="link_here">more</a>'); 
//we will print 300 characters only for display.

echo $display_text;

Like a boss.

Upvotes: 0

wandering-geek
wandering-geek

Reputation: 1378

You can use the PHP function strpos().

Upvotes: 0

bfavaretto
bfavaretto

Reputation: 71939

In MySQL, you can use a combination of LOCATE (or INSTR), and SUBSTRING:

SELECT SUBSTRING(col, LOCATE('jQuery', col) - 20, 40) AS snippet
FROM yourtable

This will select a 40 character snippet from your text, starting 20 characters before the first occurrence of 'jQuery'.

However, it tends to be slow. Alternatives worth looking into:

  • Using a similar method in PHP
  • Using full-text search features from MySQL (not sure if any of them will help), or maybe a whole separate full-text search engine like solr.

Upvotes: 1

Related Questions