Can't find FULLTEXT index matching the column list

[redgoals]

here is the code im usin for the search..

$query = mysql_query("SELECT article, title, page_title FROM news 
WHERE MATCH (article,title) AGAINST ('keyword')") or die(mysql_error()); 

and the error

Can't find FULLTEXT index matching the column list.

columns

FULLTEXT KEY title (title),
FULLTEXT KEY article (article)

mysql version 3.23.37

any ideas why i get the error?

[redemption]

$query = mysql_query("SELECT article, title, page_title FROM news 
WHERE MATCH (article,title) AGAINST ('keyword')") or die(mysql_error()); 

you've only these 2 indices:
FULLTEXT KEY title (title),
FULLTEXT KEY article (article)

but you're matching (article,title) which requires a FULLTEXT index of (article,title)...

you'd need to create a new FULLTEXT index for (article,title)

[redgoals]

hi, thnx for the reply.

i didnt quite understand what you meant by:

"create a new FULLTEXT index for (article,title)"

i thought i already did that when i altered the table?

anyway i ve got this far with the query and it seems to be workin but not printing anything out:

<?
$query = "select article,title FROM news where MATCH(title) AGAINST ('manchester')" or die(mysql_error());
?>

now how do i go about echoing the result of that query?

[redemption]

read the manual here: http://www.mysql.com/doc/F/u/Fulltext_Search.html

excerpt from Tech Times:

Say you have a table called Products with columns called Title and Description (among others), and you want visitors to be able to enter keywords to search based on those two columns. First, add a FULLTEXT index to your Products table for those two columns:

ALTER TABLE Products ADD FULLTEXT (Title,Description)

you need another FULLTEXT index (article,title)... having 2 separate indices FULLTEXT KEY title (title),
FULLTEXT KEY article (article) is different from one index based on 2 columns...

[redgoals]

oh i c thnx for pointing that out redemption, sometimes i can be a little...u know.

[redgoals]

anyone know how i can highlight the matched word?

[redemption]

this thread should help you...

i myself needed that too so thanks for making me go search for it

[Richi]

hi there ...

can i know about Full-text Search ?

can u plz post simple code about it ?

[redgoals]

i only learnt about it through the SitePoint tech times newsletter.

its very fast though and heres some simple code:

<?
$query = mysql_query("SELECT article, title FROM news WHERE MATCH (article,title) AGAINST ('$keyword')") or die(mysql_error());
while($r=mysql_fetch_array($query)) {
$title=$r["title"];
$article=$r["article"];
echo "$article | $title";
}
?>

you have to add a fulltext index (refer to redemtion's post above) on the two columns article,title or whatever your columns are called.

[redgoals]

i could't get the highlight code to work for me, did it work for you redemption?

$article = str_replace('$keyword', '<b>$keyword</b>', $article);

[Richi]

i had made phonebook, now there more than 1500, include every thing ..

i write search code with using like '%$search%' ..

if i rewrite it with full text search will be fast ?

full hightlighting the searched word :

$print .= "<td><font color=\"#ff6600\">". str_replace ("$search","<font color=\"#ff0000\"><B>$search</B></font>",$name) ."</td>"; 

i think the same as u wrote there !

[redgoals]

FULLTEXT search has improved the speed of my searches by about 40% - 50%.

id definately choose it.

i cant get the highlight code to work, any one else have any other ways of highlighting?

[Richi]

ur code is ok as i see ...

but how u printed out, maybe u miss something there !

[redgoals]

ok fixd it now, thanx for the nudge in the right direction Richi

echo "". str_replace ("$keyword","<font color=\"#ff0000\"><B>$keyword</B></font>",$article) ." | $title"; 

[redgoals]

what about case highlighting?

i want it to highlight both cases of the keyword?

[Richi]

hmm... what u mean ?

to hiightling more than one wrod ?

[redgoals]

what i mean is the str_replace wont match "PHP" if the keyword is "php"

i want it to match both cases of the same keyword not seperate keywords.

[Richi]

u need to use strtolower ..

so when u query the data from db u need to make it lower case, and also the $keyword to lower case ..

strtolower
(PHP3 , PHP4 )

strtolower -- Make a string lowercase
Description

string strtolower (string str)


Returns string with all alphabetic characters converted to lowercase.

Note that 'alphabetic' is determined by the current locale. This means that in i.e. the default "C" locale, characters such as umlaut-A (Ä) will not be converted.

Example 1. Strtolower() example

$str = "Mary Had A Little Lamb and She LOVED It So";
$str = strtolower($str);
print $str; # Prints mary had a little lamb and she loved it so

[Richi]

or u can make it as an option for the user, with radio button (uppercase, lowercase) ..

and make 2 function for that !

[redgoals]

i decided to turn to my good old friend preg_replace..

$article = preg_replace("/($keyword)/i", '<b>\\1</b>', $article); 

thnx for the help though Richi, much appreciated.

[Richi]

no problem