搜索与 MySQL 中的数据匹配的关键字 [英] Search keyword(s) that matches data in MySQL

问题描述

我正在整理图书馆系统并修复凌乱的书名.我想编写一个 SQL 查询或 PHP 代码来搜索与 MySQL 表中数据匹配的关键字.

I am doing a tidyup for a library system and fixing messy book titles. I would like to write an SQL query or PHP code that searches keyword(s) that matches data in MySQL table in a sentence.

[tbl_keywords]

[tbl_keywords]

id | keyword             | title
====================================================================
 1 | Harry Potter        | Harry Potter
 2 | Philosopher's Stone | [Harry Potter] Harry Potter and the Philosopher's Stone
 3 | Chamber of Secrets  | [Harry Potter] Harry Potter and the Chamber of Secrets
 4 | Dr. Seuss           | Dr. Seuss
 5 | Green Eggs and Ham  | [Dr. Seuss] Green Eggs and Ham
 6 | The Cat in the Hat  | [Dr. Seuss] The Cat in the Hat

例如

"Harry Potter(1)" => matches 1
"[Harry Potter] Harry Potter and the Philosopher's Stone(1)" => matches 1 and 2
"(HARRY POTTER2) THE CHAMBER OF SECRETS" => matches 1 and 3
"Dr. Seuss - Green Back Book" => matches 4
"Green eggs and ham" => matches 5
"the cat in the hat(Dr. Seuss)" => matches 4 and 6

这也可能吗(容易实现)?如果要做太多，我只是将变量值添加到表中..

Also is this possible (easy to be implemented)? If it's too much to do, I just add variable values into the table..

"HarryPotter" => matches 1
"Dr.Seuss" => matches 4
"Dr Seuss" => matches 4

任何有关如何执行此操作的帮助或想法将不胜感激.提前致谢.

Any help or ideas on how to do this would be appreciated. Thanks in advance.

推荐答案

PHP stristr 代替 SQL 用于查找数组中的每个关键字.

Instead of SQL, PHP stristr is used to look for each keywords in array.

在文本中搜索，并通过 php 选择关键字

然后，在 MySQL 表中查找 ID 以获取其他信息/字段；标题、类别等

Then, look up the IDs in a MySQL table to get other information/fields; title, category etc.

$keywords = array(NULL, 'Harry Potter', 'Philosopher\'s Stone', 'Chamber of Secrets');
$input_title = "[Harry Potter] Harry Potter and the Philosopher\'s Stone(1)"

$keyword_found = array();
foreach ($keywords as $key => $val) {
    if (stristr($input_title, $val)) $keyword_found[] = $key;
}

if ($keyword_found) {
    foreach ($keyword_found as $val) {
        $sql = "SELECT * FROM `tbl_keywords` WHERE `id` = '" . $val . "'";
        ...
    }
}

它不整洁，必须有更好的方法，但是..至少它有效！再次感谢那些试图帮助我的人:)

It's not tidy and there must be better ways, but.. at least it works! Thank you again for people who try to help me :)

这篇关于搜索与 MySQL 中的数据匹配的关键字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！