dongzhan1948
2013-01-24 01:12
浏览 66
已采纳

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

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.

图片转代码服务由CSDN问答提供 功能建议

我使用以下查询(这里简化)来检查字符串是否包含“watch-word” 监视字包含在MySQL表中:

  $ sql =“SELECT ww_id FROM watch_words WHERE ww_word IN(”。$ string。“)”; 
   
 
 

这适用于单个单词,但现在我需要使其适用于短语(即字段ww_word可能包含多个单词)。 我能想到的就是把整个表读成一个数组,然后做多个循环来比较字符串中单词的组合,但我确信(希望)有更好的方法。

编辑:感谢您的建议,但正如迈克布兰特所指出的,针是在MySQL和PHP中的干草堆 - 而不是“通常”的方式(例如搜索表单)。 我需要检查一个字符串(实际上是一个消息)是否包含一个或多个“监视短语” - 就像一个坏语言过滤器(但不是那个)。

样本表因此:

  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,'what'); 
INSERT INTO`watch_words` VALUES(4,'我的全名在这里'); 
INSERT INTO`watch_words` VALUES(5,'keyword'  ); 
   
 
 

所以字符串“我们有一个美好的阳光灿烂的日子”应该返回一个匹配,而“多么可爱的阳光灿烂的日子......”不会 吨。 TIA。

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doulianglou0898 2013-01-24 01:17
    已采纳

    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.

    已采纳该答案
    评论
    解决 无用
    打赏 举报
  • dow66098 2013-01-24 01:14

    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题