Page 1 of 1

MySQL returning matched sentence, like Google results

Posted: Tue Mar 10, 2009 4:59 am
by batfastad
Hi everyone
I've got a text field that users will be searching on, I was planning on using the old LIKE %foo% syntax for the search

Is there a way for MySQL to return say the 10 words before and after the matched string?
Or is that something best done in PHP?

I'm looking to do something similar to the way Google results are listed, where you have the search string highlighted within a sentence/para on the original page.

Anyone know the best way of doing this?

Thanks, Ben

Re: MySQL returning matched sentence, like Google results

Posted: Tue Mar 10, 2009 6:57 am
by jayshields
The only solution I can think of is basically like you said. Just do a SELECT ... WHERE ... LIKE "%xxx%" query and then do a strpos() on it in PHP. Then you can use PHP to find the 10th space before and the 10th space after the strpos() value.

I'm sure I've seen some example code of this logic on this forum. Might of been years ago though. Search around.

Re: MySQL returning matched sentence, like Google results

Posted: Tue Mar 10, 2009 7:51 am
by VladSun
If your content size is relatively small you'd better do it in PHP. And if your content size is relatively big (thus transferring it to the WWW server is an issue) you could use this:
[sql]SET @nchars:=20, @loc:=0, @len:=0;SELECT  @loc:=LOCATE('keyword', `content`),  @len:=CHAR_LENGTH(`content`),  SUBSTRING(    `content`,    IF(@loc > @nchars AND @nchars > @len, @loc - @nchars, 1),    2*@nchars + CHAR_LENGTH('keyword')  ) AS `quote`FROM  `article`WHERE  `content` LIKE '%keyword%'[/sql]

It will search for "keyword" text and will return the position this string is found in `content` field, the length of the text `content` field and the +/- 20 neighbour chars.

So, you could easily produce this ouput in PHP (without searching again):
...0 some text here 1 keyword some text here 2 so... (full version: 234 chars)

Re: MySQL returning matched sentence, like Google results

Posted: Thu Mar 12, 2009 11:20 am
by batfastad
For this current project, PHP will be accessing the data from MySQL on the same machine so the data size won't be too much of an issue at the moment. Looks like PHP might be the way to go at the moment.

But... Vladsun... Woah! What the heck is that MySQL syntax called and where can I learn it??!! :D :D :lol:

Re: MySQL returning matched sentence, like Google results

Posted: Thu Mar 12, 2009 11:39 am
by VladSun
batfastad wrote:But... Vladsun... Woah! What the heck is that MySQL syntax called and where can I learn it??!! :D :D :lol:
From the manuals ;)
http://dev.mysql.com/doc/refman/5.0/en/ ... ables.html

Re: MySQL returning matched sentence, like Google results

Posted: Thu Mar 12, 2009 12:01 pm
by batfastad
Sweet, that's cool! Cheers Vladsun ;)