MySQL search results

20-30 characters before and after the search word

[tengri]

How to make in MySQL search results displaying 20-30 characters before and after the search word so that users can see what they found (like Google or Altavista search, you know). It could be something like SUBSTRING(-30, ‘%$search%’, 30), but it does not work of course. I spent the whole day trying to make it and no results! Can you please help me with that? Any idea will be greatly appreciated!

[tengri]

Forgot to say "Hello everybody!":)))

[davelms]

Very similar to the thread I just commented on, where the author said preg might do the trick which I couldn't really say... I don't do it in MySQL either, so that could also give you a better solution. Here's what I do, be it good or bad:

Firstly I find all records:

SELECT text_field FROM table WHERE text_field like '%$text_to_find%'

You know "text_field" contains in it "text_to_find" - somewhere. Find where it is (or the first occurrence).

In this example I intend to go -100 and +100 chars.

$whereisit = strpos($text_field, $text_to_find)-100;
$whereisit = $whereisit > 0? $whereisit:0;

$text_out = substr($text_field, $whereisit, 200);

I now highlight the text in its context:

$text_out = str_replace($text_to_find,"<b>$text_to_find</b>",$text_out);

And display it:

echo "<p>... <i>$text_out</i> ...</p>";

You might find better use of case insenstive functions, maybe, or do like I do and translate everything to lower case first.

[coopster]

Welcome to WebmasterWorld, tengri.

Another function that can be used in these situations is str_word_count() [php.net].

[tengri]

Thanks a lot, davelms. Interesting combination of PHP and SQL and it works pretty well. Thank you very much, coopster for information, I really need to learn more about it.