match against problem

[Lynky]

Hi,

I just build a simple table to test "match against". I think I used exact the same syntax as described in the mysql-manual, but it doesn't seem to work.

Structure:

CREATE TABLE `match_against` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `keywords` tinytext NOT NULL,
  `keywords2` tinytext NOT NULL,
  PRIMARY KEY  (`id`),
  FULLTEXT KEY `searchindex` (`keywords`,`keywords2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data:

INSERT INTO `match_against` VALUES (1, 'php', 'test');
INSERT INTO `match_against` VALUES (2, 'test', 'test test');
INSERT INTO `match_against` VALUES (3, 'test test test', 'php php');

Query:

SELECT id, MATCH (keywords, keywords2) AGAINST ('test')
FROM match_against

In the result the match-against column is 0 for all 3 records. Anyone has an idea why?? 😕

Lynky

PS: running MySQL 4.1.8

[kidgeek_dfw]

try using:

SELECT * FROM match_against WHERE MATCH (keywords, keywords2) AGAINST ('test')

or

SELECT id, MATCH (keywords, keywords2) AGAINST ('test') AS score FROM match_against WHERE MATCH (keywords, keywords2) AGAINST ('test') ORDER BY score DESC

[Lynky]

Hi,

thanks for your response.
Both Queries deliver an empty result. Is it possible that this is a MySQL bug?😕
Because - if I try

SELECT id, MATCH (keywords, keywords2) AGAINST ('test' IN BOOLEAN MODE)

then the math against column contains '1' for every record.

Lynky

[kidgeek_dfw]

Sorry for the delayed response...

The Boolean search is only available on MySQL 4.1.x

There is an awesome tutorial on this here:
http://www.phpfreaks.com/tutorials/129/0.php

[Lynky]

Thx, cool! That's really an excellent one!!
However - did you try to search in that script for 'search' or 'MSN'? The script doesn't find these words (not on my machine) but therefore words like e.g. 'firefox'. I looked up in the stopword list, but it doesn't contain any of the words. Someone posted also that it doesn't work with the word 'php' - I can confirm that. Maybe it is just the same with the word 'test'

For me, everything looks like there's a bug - at least in my version. I would really appreciate if someone can tell me if these examples/issues are working at all!

Lynky

PS: I'm using version 4.1.8, boolean mode WORKS (with some words 😉 ), but scoring doesn't.

[kidgeek_dfw]

Works great for me, including the scoring...

I'll look into this some more a little later...
I'll try to remember the address of this post...