google type search

[redgoals]

hi,

when i search the database for a word i want the output to display a set amount of text that surrounds the matched word.

e.g

...im searching for a keyword because im lost...

[redgoals]

ok i managed to get this far with my attempt..

$title=$r["title"];
$article=$r["article"];
$match = preg_replace("/($keyword)/i", '<font color="#ff0000"><B>\1</B></font>', $article);
$pos = strpos($match, "$keyword");
$result = substr("$match", $pos-100, 100);
echo "$result | $title | $pos<br>"; 

example of what it returns

blah blah blah keyword blah blah blah

or sometimes if there is a keyword with about 10 characters left in substr it prints out a portion of the opening font tag meaning the proceding text gets coloured red .

[redux]

red, just an idea without looking too hard at your code...why not do the preg_replace AFTER you isolated the $result ?

$title=$r["title"];
$article=$r["article"];
$pos = strpos($match, "$article");
$result = substr("$match", $pos-100, 100);
$result = preg_replace("/($keyword)/i", '<font color="#ff0000"><B>1</B></font>', $result);
echo "$result | $title | $pos<br>"; 

[redgoals]

thnx that worked .

[redgoals]

another thing how could i make sure the first letter returned is a whole word.

..ello

to

...hello

[aspen]

You should do this in your SQL, that way your query doesn't have to return the entire field.

$term = strtolower($term);
$search = mysql_query("SELECT LCASE(SUBSTRING(field, LOCATE('$term', LCASE(Chp_Text)) - 50, 100)) as synopsis FROM table WHERE field like '%$term%'", $db);
if (!$search) {  echo("<P>Error performing query: " . mysql_error() . "</P>");  
exit();
} 

That will return 50 characters to the left and the right of the term. Notice I put everything in lowercase - if you want a case sensitive search you'd remove that part.

Then I do this:

        $numResults = mysql_num_rows($search); 
    if($numResults != 0){
        while ( $main = mysql_fetch_array($search) ) {  
                
print(strip_tags(str_replace($term, "<b>$term</b>", $main["synopsis"])

            }
} 

Never do a regular expression when a str_replace() would work, regular expression functions should only be used when you need to actually use regular expressions, not a simple find and replace. The regular expression functions require more processing.

I also striptags from the result. My text field contains HTML like <br> tags. If one of those <br> tags is within the 100 characters it will make the results look odd on the search page.

[redgoals]

thanx for the reply aspen,

i couldnt get the keyword highlighted in the search results. Here is where im at...

<?
if($search)
{
$keyword = strtolower($keyword);
$search = mysql_query("SELECT title, LCASE(SUBSTRING(article, LOCATE('$keyword', LCASE(article)) - 50, 100)) as synopsis FROM news WHERE article like '%$keyword%'");
if (!$search) {
echo("<P>Error performing query: " . mysql_error() . "</P>");
exit();
}
$numResults = mysql_num_rows($search);
if($numResults != 0){
while ( $main = mysql_fetch_array($search) ) {
print(strip_tags(str_replace($keyword, "<b>$keyword</b>", $main["synopsis"])));
}
}
}
?>
<FORM NAME="form1" METHOD="post" ACTION="<? PHP_SELF ?>">
<INPUT TYPE="text" NAME="keyword">
<INPUT TYPE="submit" NAME="search" VALUE="Submit">
</FORM>

[redgoals]

fixed that problem, now how do i prevent the output from printing rows which do not contain the keyword?

[aspen]

put the where clause in your SQL like I have above.

You can also put other things in that query, I stripped it down to just the important parts.

[redgoals]

i did put the where clause in the query, problem is when there is no keyword found in a row it prints out a blank line.

[redgoals]

sorted.

[redgoals]

one other thing can i use locate to locate the second keyword matched, if so how?

[redgoals]

*bump*

[aspen]

The example I gave was for phrase searches. For boolean searches you need to use a fulltext index.

http://www.mysql.com/doc/F/u/Fulltext_Search.html

It gets complicated but it is exactly what you need if you want a boolean search.

[redgoals]

why do i get the error...

You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1

on...

$search = mysql_query("SELECT * FROM news WHERE MATCH (article,title) 
AGAINST ('$keyword' IN BOOLEAN MODE)") or die (mysql_error()); 

[DR_LaRRY_PEpPeR]

i believe IN BOOLEAN MODE only works in MySQL 4.0+.

[redgoals]

*sigh*

ok, with the folowing bit of code im looking to match a keyword in a string.

$search = mysql_query("SELECT id,title, 

LCASE(SUBSTRING(article, LOCATE('$keyword', LCASE(article)) 0, 150)) as first, 

SUBSTRING(article, +150, 150) as short, 

LOCATE('$keyword', LCASE(short)) as second

FROM news WHERE article like '%$keyword%'") or die (mysql_error()); 

however i get the error "Unknown column 'short' in 'field list'"

the "short" field is used to select the next 150 words from the column article. I do that so i can match the keyword twice in the article and then output the result like so...

...first part of the article with the keyword...second part with the keyword in....

thats how google displays results.

any idea how i could get output as i want it?

[qslack]

What about this?

$search = mysql_query("SELECT id,title, 



LCASE(SUBSTRING(article, LOCATE('$keyword', LCASE(article)) 0, 150)) as first,
LOCATE('$keyword', LCASE(SUBSTRING(article, +150, 150))) as second



FROM news WHERE article like '%$keyword%'") or die (mysql_error()); 

[redgoals]

I'm having one of those days , why i didnt see that obvious solution is completely beyond me *sigh*. Oh well...thanx for that qslack

[redgoals]

ok, i think i spoke too soon.

The query as it is now does locate the fist occurrance of the keyword in the string.

However the second locate function does not locate the second occurance of the keyword in the next 150 words of the string.

Ideally i wanted the second locate function to search through the second 150 words but trim off any words before the position of the keyword.

Did that make sense?

[redgoals]

hi all,

does anyone know how to count the number of occurences of a word in a record?, using a query?

[zoordaan]

I don't think this is what you want but the following should give you the number of times a word occurs in a column.

SELECT articlecontent FROM table WHERE articlecontent LIKE '%searchterm%'

Then for each record do:
print substr_count($articlecontent, "searchterm");

[mrWoot]

I am trying to do the same thing. I've been working at this for some time. I was wondering if you got it to work. If you have, could you post it here?