how do I narrow down mysql search results?

Report · Mar 16, 2011

I want to create a mysql search (MATCH x AGAINST y) where it will only show the results where the complete term of 'x' is found in 'y'.

for example, in database 'x' I have two entries: 1) 'good forum' and 2) 'bad forum'.

When the input of 'y' is "dreamweaver has a good forum", I only want the first entry of x to show ('good forum').

However, using WHERE MATCH x AGAINST y always displays both 1 and 2 of database 'x' because they both have the word 'forum'. How do I arrange the search result code to only get the the entry of 'x' that fully matches that of 'y'?

Thank you,
YWSW

Report · Mar 16, 2011

The MySQL documentation provides very helpful information about Boolean Full-Text Searches :: the IN BOOLEAN MODE modifier lets you specify a "required" word by adding (quote) "a leading plus sign which indicates that this word must be present in each row that is returned"

In your case this example, which specifies both words as required, should work:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+good +forum' IN BOOLEAN MODE);

Report · Mar 16, 2011

Gunter Shenk, thank you so much for replying.

My problem is that entry 'y' can be many words (as in my example above) or even many paragraphs. It is the full term of 'x' that I want to be matched (in my example above, I only want the first term of 'good forum' to show).

WHERE MATCH (x) AGAINST (y)

is there any boolean code that works to make the full 'x' mandatory?

Thanks,
YWSW

Report · Mar 16, 2011

is there any boolean code that works to make the full 'x' mandatory?

The documentation also says that "a phrase that is enclosed within double quote (“"”) characters matches only rows that contain the phrase literally, as it was typed" -- what in your case should result in:

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"good forum"' IN BOOLEAN MODE);

Report · Mar 16, 2011

Gunter Schenk, it is not the AGAINST (y) that I need to be exact, it is the first part of the phrase, the MATCH (x).

I need something like:

WHERE MATCH ("x" IN BOOLEAN MODE) AGAINST (y)

[which, by the way, does not work]

I cannnot put "..." in AGAINST (y) as 'y' is a very long string of words (as in my example in my question above)- only some of which match up to 'x'.

It is the 'MATCH (x)' that I need to be specified.

Does this make sense?

Report · Mar 16, 2011

Does this make sense?

Honestly said, not at all -- maybe I´m just temporarily a little dense

I need something like:
WHERE MATCH ("x" IN BOOLEAN MODE) AGAINST (y)
[which, by the way, does not work]

Apart from the fact that a literal "x" will certainly not work because you´ll have to specify the table name(s) here, "does not work" can have many reasons, like the fact that fulltext searches can only work with MySQL tables of the type MyISAM, and those +/- operators seem to be supported with MySQL 5 and higher