检查字符串是否包含数据库中的短语

Question

I am using the following query (simplified for here) to check if a string contains a "watch-word" where the watch words are contained in a MySQL table:

$sql = "SELECT ww_id FROM watch_words WHERE ww_word IN (" . $string . ")";

This works perfectly for single words, but now I need to make it work for phrases (i.e. the field ww_word may contain more than one word). All I can think of are things like reading the whole table into an array and then doing multiple loops to compare against combinations of the words in the string, but I'm sure (hoping) there's a better way.

EDIT: Thanks for the suggestions, but as pointed out by Mike Brant, the needle is in MySQL and the haystack in PHP - not the "usual" way around (like a search form for instance). I need to check if a string (actually a message) contains one or more "watch phrases" - like a bad-language filter (but not that).

Sample table thus:

CREATE TABLE `watch_words` (
  `ww_id` int(11) NOT NULL AUTO_INCREMENT,
  `ww_word` varchar(250) NOT NULL,
  PRIMARY KEY (`ww_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `watch_words` VALUES (1, 'foo bar');
INSERT INTO `watch_words` VALUES (2, 'nice sunny day');
INSERT INTO `watch_words` VALUES (3, 'whatever');
INSERT INTO `watch_words` VALUES (4, 'my full name here');
INSERT INTO `watch_words` VALUES (5, 'keyword');

So string "What a nice sunny day we're having" should return a match, whereas "What a lovely sunny day..." wouldn't. TIA.

Answer 1

You will likely need to take a different approach here. You have the needle in MySQL and the haystack in PHP. Using things like LIKE (which you use for string matches not IN), MySQL can work fine with the haystack being in MySQL table and the needle in the application (in the LIKE).

There is no convenient reverse matching to pass MySQL the haystack and have it apply a needle from a field in a table against it.

You will likely need to select your needles out of the database and compare it to the haystack in your application.