[SOLVED]MySQL Fulltext Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

[SOLVED]MySQL Fulltext Search

Post 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
Last edited by bluenote on Thu Aug 12, 2004 2:32 am, edited 1 time in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post 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);
User avatar
bluenote
Forum Commoner
Posts: 93
Joined: Sat Mar 01, 2003 4:59 am
Location: Heidelberg, Germany

Post by bluenote »

This worked!!

Thanx-a-lot!!!

- bluenote
Post Reply