如何通过PHP和MySQL进行更智能的搜索 - how can i make more intelligent search by php and mysql

how can i make more intelligent search by php and mysql

本文关键字：智能搜索何通过 PHP MySQL | 更新日期: 2023-09-27

我有一个数据库位置表，它包含两行标题和描述

我像这样通过PHP和MYSQL进行了搜索

$select_place = $mysqli->query("SELECT * FROM places where title LIKE '%$searchname%' or description LIKE '%$searchname%'");
$num_place = $select_place->num_rows;
while ($rows_place = $select_place->fetch_array(MYSQL_ASSOC)){
$id_place            = $rows_place ['id'];
$title_place         = $rows_place ['title'];
$description_place   = $rows_place ['description'];
echo "<p>{$title_place}</p><br>";
}

它运行良好，但是，例如，

如果您搜索单词塔，如果以错误的方式编写，例如 twer 或 towr，它不起作用如何让它更智能？

一个不错的选择是包含MySQL SOUNDEX()函数：

SELECT
    *
FROM
    places
WHERE
    title LIKE '%$searchname%'
    OR description LIKE '%$searchname%'
    OR SOUNDEX(title) = SOUNDEX('$searchname')
    OR SOUNDEX(description) = SOUNDEX('$searchname')

这将匹配示例中的towr和twer。

更多可以在这里找到：

http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_soundex

请注意：

当前实现的此功能旨在与仅使用英语的字符串。其他字符串语言可能无法产生可靠的结果。

我找到了非常有用的方法

在这里

$searchname = stringsafe($_GET['search']);
$searchname2 = explode(" ", $searchname);
$query = "SELECT * FROM places WHERE ";
foreach ($searchname2 as $each) {
    $i++;
    if($i == 1){
        $query .= " title LIKE '%$each%'
    OR description LIKE '%$each%'
    OR SOUNDEX(title) = SOUNDEX('$each')
    OR SOUNDEX(description) = SOUNDEX('$each')";
    }else{
        $query .= " OR title LIKE '%$each%'
    OR description LIKE '%$each%'
    OR SOUNDEX(title) = SOUNDEX('$each')
    OR SOUNDEX(description) = SOUNDEX('$each')";
    }
}
$select_place = $mysqli->query("$query limit 20");
$num_place = $select_place->num_rows;
if($num_place == 0){
    echo "<div class='message-no'>No results</div>";
}
else{
while ($rows_place = $select_place->fetch_array(MYSQL_ASSOC)){
$id_place            = $rows_place ['id'];
$title_place         = $rows_place ['title'];
$description_place   = $rows_place ['description'];
echo $title_place."<br>";
}