Looking for a search engine that search a mysql database

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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.


Re: Looking for a search engine that search a mysql database

Read in http://dev.mysql.com/doc/refman/5.0/en/fulltext-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.

Re: Looking for a search engine that search a mysql database

Quoted text here. Click to load it
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


Re: Looking for a search engine that search a mysql database

Drakazz wrote:
Quoted text here. Click to load it

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:

rch.')((?:.)\b|(?:.)$)/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"></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|^.{'./*exact number of preceeding
chars*/'}).{'./*exact_length from first offset to last offset plus
stringlength last searchterm*/.'}(.\b|.{'./*exact
number of proceeding chars*/'}$)/si', $substring, $combinations.

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


Rik Wasmus

Re: Looking for a search engine that search a mysql database

 [ 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.


Re: Looking for a search engine that search a mysql database

Martien van Wanrooij wrote:
Quoted text here. Click to load it

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

Rik Wasmus

Site Timeline