MySQL returning matched sentence, like Google results

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL returning matched sentence, like Google results

Post 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
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: MySQL returning matched sentence, like Google results

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL returning matched sentence, like Google results

Post 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)
Last edited by VladSun on Thu Mar 12, 2009 4:37 pm, edited 3 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL returning matched sentence, like Google results

Post 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:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL returning matched sentence, like Google results

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL returning matched sentence, like Google results

Post by batfastad »

Sweet, that's cool! Cheers Vladsun ;)
Post Reply