Page 1 of 1

[SOLVED]MySQL Fulltext Search

Posted: Thu Apr 15, 2004 2:11 am
by bluenote
Hi there,

a simple (and maybe stupid) question, but it's killing me:

I have a medium-scaled table containing articles (the table holds title, summary, abstracts, keywords, a file system link to a pdf, ..., ...). Now I have to write a fulltext query for this table which should extract all entries whose title or keywords contains one or more of the keywords a user hits into a form.

So i started with creating a fulltect index for the two fields

Code: Select all

ALTER TABLE articles ADD FULLTEXT (title);
ALTER TABLE articles ADD FULLTEXT (keywords);
and wrote the following query:

Code: Select all

<?php
$keywords = $_REQUEST["keywords"];
SELECT * FROM articles WHERE MATCH (title, keywords) AGAINST ('$keywords')  ORDER BY date DESC;
?>
PHP is 4.3.4, Apache is 1.3.29, MySQL is 4.0.18; the table is a MyISAM table :lol:

I have tested this query in the command line and in PHPMyAdmin; the result was always an error:

Code: Select all

&#1111;Thu Apr 15 08:46:08 2004] &#1111;error] PHP Warning:  mysql_num_rows(): supplied argument is not a valid MySQL result resource in /export/home/local/Web/include/templates/content/c_articlesearch.inc on line 65

#1191 - Can't find FULLTEXT index matching the column list
So what is wrong? Can anybody help?

Thanx,
- bluenote

Posted: Thu Apr 15, 2004 3:03 am
by markl999
Try removing the 2 existing FULLTEXT properties from the table (not the actual columns, just the FULLTEXT bit) and then do:

ALTER TABLE articles ADD FULLTEXT (title,keywords);

Posted: Thu Apr 15, 2004 3:33 am
by bluenote
This worked!!

Thanx-a-lot!!!

- bluenote