[vispa]

Hiya,

I am currently working on a new search script for a new FAQ section of my web site, but it don't quite seem to be as clever as I would like.

I have some test data in MySQL (v3.23), and one of the fields is title. In this title is "Connecting with Windows XP" and another is "Connecting with Windows 98".

I am trying to make use of MySQL FULLTEXT, and I currently have the following Db Structure

Code:

CREATE TABLE topics ( 
  id tinyint(4) NOT NULL auto_increment, 
  title varchar(100) NOT NULL default '', 
  description text NOT NULL, 
  details text NOT NULL, 
  keywords text NOT NULL, 
  type varchar(50) NOT NULL default '', 
  category varchar(50) NOT NULL default '', 
  created_by varchar(25) NOT NULL default '', 
  created_date date default NULL, 
  edited_date date default NULL, 
  PRIMARY KEY  (id), 
  FULLTEXT KEY title (title,description,keywords) 
) TYPE=MyISAM;

The code I am currently using to search in MySQL is

Code:

$query1 = "SELECT * FROM topics WHERE MATCH (title,description,keywords) AGAINST ('$term')";

However while the above seems to work it requires me to give a very big search term to find just 1 match. For instance if I give a search term of "Windows XP" it find nothing, and likewise "Windows" or "Connecting" finds nowt, but then "Connecting with Microsoft Windows XP" will start to show results.

The object of the code is to search titles, descriptions and keywords then rank based on what nearest.

How on earth can I make this bit more intelligent without going totally overboard? Maybe FULLTEXT is wrong method (though this naturally ranks right)? Where am I stuffing up?

Cheers guys!

Martin

[mdhall]

Try something like this...

$query1 = "SELECT * FROM topics WHERE title LIKE '$term' AND description LIKE '$term' AND keywords LIKE '$term'";

See if that does any better. You can also try with " % " to see what happens, i.e.

'%$term'
'%$term%'

Different variations may give you different results.