我在表中有以下数据,
id Description Category
1 I am Desc with printer main category
2 I am desc with test test category
3 new printer desc third category
4 new test category printer category
等等…
我想在他们的描述字段中找到有相同单词的计数(可以是任何类似打印机的东西,但不是预定义的)。对于出厂,输出应为:
Total Word which is same
2 printer
2 test
我试着用http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html带有布尔选项的示例,但它没有给出所需的输出。
我举的例子是打印机,它可以是任何东西。我不想在查询的任何地方指定这个词,因为它可以是任何东西。只有在任何地方具有相同单词的描述才应该在输出中。
提前感谢。
试试这个:
SELECT SUM(IF(Description like '%printer%',1,0)) AS Printer,SUM(IF(Description like '%test%',1,0)) AS Test FROM `yourTable`
这可能会有所帮助:)
构建描述中所有单词的字典
$dbh = new PDO($dsn, $user, $password);
$sql = "SELECT description FROM tableName";
foreach($dbh->query($sql, PDO::FETCH_ASSOC) as $result){
foreach (explode(' ',$result['description']) as $word){
$words[] = $word;
}
}
使用array_count_values
对数组中的重复值进行计数,可以选择按降序排序
$wordCount = array_count_values($words);
arsort($wordCount);
然后使用array_filter
过滤数组以消除仅出现一次的单词
$filteredWordCount = array_filter($wordCount, function($value){
return $value!=1;
});
这将为您提供一个数组,该数组将单词本身作为索引,将出现次数作为值。
在阵列上迭代并运行COUNT
查询
foreach($filteredWordCount as $word=>$value){
$countSQL = 'select COUNT(*) as rowCount from tableName where description like "%' . $word . '%" ';
$res = $dbh->query($countSQL, PDO::FETCH_ASSOC);
$count[$word] = $res->fetch()['rowCount'];
}
像以前一样,根据值对数组进行排序,并打印出
arsort($count);
print_r($count);
如果你需要一个行数条件,例如,只返回出现在5条以上记录中的单词,你可以像下面的一样调整查询
foreach($filteredWordCount as $word=>$value){
$countSQL = 'select COUNT(*) as rowCount from domains where description like "%' . $word . '%" HAVING rowCount > 5';
$res = $dbh->query($countSQL, PDO::FETCH_ASSOC);
$c = $res->fetch()['rowCount'];
if (isset($c)){
$count[$word] = $c;
}
}