470,874 Members | 1,435 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,874 developers. It's quick & easy.

Looking for a search engine that search a mysql database

I have been using phpdig in some websites but now I stored a lot of larger
texts into a mysql database. In the phpdig search engine, when you entered a
search word, the page where the search word was found was displayed with
about 2 lines before and 2 lines behind the search word itself. Let us say
you look for "peanut butter" an the word is found in a larger text about
sandwiches, even when it is on the 40th line of the text you would get
something like
....In Holland peanut butter is a popular spread on sandwiches "
A query like "SELECT title, maintext FROM MYTEXTS WHERE maintext LIKE
$searchedword" will do most of the job and I can create a query that
displays only the first 200 characters of maintext, so there will be an
introductory text about sandwiches and our peanut butter lover maybe will
skip this page :)

but I am puzzled about a command that ( either in php or mysql) jumps to
$searchedword in the maintext field and returns a couple of lines around it.
Any ideas? If there is an open source php module that could do this I will
be happy too and maybe I just am overseeing a relatively easy function that
will do the job.. Google-ing to "mysql php search engines" did not give too
many hints.

Thanks for any help.

May 10 '06 #1
5 2281
Read in http://dev.mysql.com/doc/refman/5.0/...xt-search.html
Full text search is mostly used. About the 200 characters I am not
Also, about the highlighting etc., you could look at MediaWiki's source
( http://www.mediawiki.org/ ).

Thank You.

May 10 '06 #2

"Drakazz" <vy****************@googlemail.com> schreef in bericht
news:11*********************@g10g2000cwb.googlegro ups.com...
Read in http://dev.mysql.com/doc/refman/5.0/...xt-search.html
Full text search is mostly used.

Thank you Drakazz.. as I said I thought maybe I am just entering the wrong
keywords in google , I didn't think about keywords like fulltext search but
as an afterthought it makes sense to me . I think this article will resolve
a lot of the problem

May 10 '06 #3
Drakazz wrote:
Full text search is mostly used. About the 200 characters I am not

No idea, but two methods come to mind:
Assuming $text is the returned text from the database, and $string is the

Normal functions:

$occurance = stripos($text, $string);
$start = ($occurance-100 < 0) ? 0: $occurance-100;
$display = substr($start, 200 + strlen($text));

Advantage is it's quick, disadvantage is will only find the first occurance,
and will cut up words.

A probably more versatile method are regular expressions:

$chars = 100; // (the desired characters before and after)
$allowword = 20; //extra characters allowed to find a word boundary

$allow = $chars + $allowword;
$else = $chars-1; //pff, naming variables is a drag

$search = preg_quote($string, '/'); //escape all characters that could have
special meaning:

preg_match_all('/(^(?:.){0,'.$else.'}|\b(?:.){'.$chars.','.$allow.' })('.$sea
rch.')((?:.){'.$chars.','.$allow.'}\b|(?:.){0,'.$e lse.'}$)/si', $text,
$matches, PREG_SET_ORDER);

Now you have an array $matches, that contains the searchstring and
surrounding $chars characters. The expressions tries to keep words whole,
with a maximum of extra characters given bij $allowword. It's no problem
when there aren't that many characters in front or behind the searchstring,
in that case the matchs just returns from the beginning or untill the end

$matches is now an array, containg:
$matches[index_of_match][0] = The entire text.
$matches[index_of_match][1] = The preceeding text.
$matches[index_of_match][2] = The searchstring.
$matches[index_of_match][3] = The proceeding text (? don't know wether this
is good english)

Matches can be diplayed like:
foreach($matches as $match){
print $match[0];

But maybe you want to highlight your searchstring, no problem:

foreach($matches as $match){
print $match[1].'<span

When looking for several words, you could even change the search string like

$searcharray = array('searchstring','some other word', 'yet another');
$search = implode('|',array_map('preg_quote', $searcharray));

And just apply the same regex. Note that will give back a match for each
word seperately. How to prevent those "double" matches is a whole other
ballgame. Coming here I realize that even searching for one term could give
you doubles.

Highlighting the other searchterms can't be done using just the matches
array. While keeping the double entries, every searchterm can be highlighted

foreach($matches as $match){
print preg_replace('/('.$search.')/si', '<span
class="highlight">\1</span>', $match[0]);

Doubles could be prevented by using PREG_OFFSET_CAPTURE in the folowwing

$searcharray = array('searchstring','some other string', 'yet another');
$search = implode('|',array_map('preg_quote', $searcharray));
preg_match_all('/'.$search.'/si',$text, $matches,PREG_OFFSET_CAPTURE);

And then looping through $matches[0], gathering the surrounding text with
preg_matches on substrings (makes it a lot quicker), and checking wether or
not the offset of the following match is "within reach".

Create a substring from the text from searchterms close to eachother, with
max allowed characters +1 on either side.

pregmatch('/(\b{'.$chars.','.$allow.'}|^.{'./*exact number of preceeding
chars*/'}).{'./*exact_length from first offset to last offset plus
stringlength last searchterm*/.'}(.{'.$chars.','.$allow.'}\b|.{'./*exact
number of proceeding chars*/'}$)/si', $substring, $combinations.

foreach($combinations as $final){
print preg_replace('/('.$search.')/si', '<span
class="highlight">\1</span>', $final[0]);

Rik Wasmus
May 10 '06 #4

"Rik" <lu************@hotmail.com> schreef in bericht
[ detailed explanation}
Thank you Rik, for a "luiheidsgoeroe" you did a lot of work to resolve my
problem :) This is the solution I was looking for.

May 10 '06 #5
Martien van Wanrooij wrote:
Thank you Rik, for a "luiheidsgoeroe" you did a lot of work to
resolve my problem :) This is the solution I was looking for.

No problem.
Not living up to the name indeed... it's about time to rectify that...

Rik Wasmus
May 11 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by michela rossi | last post: by
3 posts views Thread by Zaphod Beeblebrox | last post: by
8 posts views Thread by Rod | last post: by
2 posts views Thread by correo | last post: by
2 posts views Thread by sathyashrayan | last post: by
3 posts views Thread by hazly | last post: by
19 posts views Thread by bb nicole | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.