CODEWITHSUNDEEP
phpmysqlsqlselect
da5id
da5id

Reputation: 9146

Check if string includes phrases from database

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.

Upvotes: 1

Views: 1497

Answers (2)

John Woo
John Woo

Reputation: 263893

use LIKE for pattern matching

$sql = "SELECT ww_id FROM watch_words WHERE ww_word LIKE '%" . $string . "%'";

or maybe interchange the two,

$sql = "SELECT ww_id FROM watch_words WHERE " . $string . " LIKE CONCAT('%', ww_word,'%')";

As a sidenote, the query is vulnerable with SQL Injection if the value(s) came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

Upvotes: 3

Mike Brant
Mike Brant

Reputation: 71422

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.

Upvotes: 1

Related Questions