GreenPilgrim GreenPilgrim - 10 months ago 76
MySQL Question

MySQL MATCH() AGAINST() with reversed parameters

I have a database table that looks a bit like this:

1|+Word +Matching -Goodbye|/url-1
2|+Redirect +Me|/url-2
3|+Goodbye +Word|/url-3

When a user types a search for: "Hello I am matching a word", I would like the table.words field to be given a 'relevance' score against that string, a lot like the MATCH() AGAINST() function, but with the parameters reversed.

Effectively, the query I am looking to run would be along the lines of:

SELECT id, words, url,
MATCH ("Hello I am matching a word") AGAINST (words IN BOOLEAN MODE) AS relevance
FROM table
ORDER BY relevance DESC

But this does not work, unfortunately. I could do something in PHP where I create a function to loop through each inclusive/exclusive word, but I fear that this will be really slow when the table size grows.

Just to tie it up, I would expect the query to return id: 1 in that instance, as it includes "Word" and 'Matching", and does not include "Goodbye". I should point out that these words could be in any order within the string, so I couldn't really use LIKE, I don't think.

If such a function does not exist, is there a better way I could approach this?


Answer Source

The documentation is pretty clear:

The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

Hence, you cannot do what you want with a query.

You can use dynamic SQL. Or a loop in PHP to loop through the patterns in your table.