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
MySQL returning matched sentence, like Google results
Moderator: General Moderators
- 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
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.
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
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):
[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
Re: MySQL returning matched sentence, like Google results
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??!!

But... Vladsun... Woah! What the heck is that MySQL syntax called and where can I learn it??!!
Re: MySQL returning matched sentence, like Google results
From the manualsbatfastad wrote:But... Vladsun... Woah! What the heck is that MySQL syntax called and where can I learn it??!!![]()
![]()
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
Re: MySQL returning matched sentence, like Google results
Sweet, that's cool! Cheers Vladsun 