你如何忽略php搜索栏中的破折号和逗号

Question

目前，在我的搜索栏中，如果我以完全输入数据库的方式输入城市名称，我就能找到它。例如：Nuits-Saint-Georges 或北卡罗来纳州达勒姆。我希望我的用户能够输入 Nuits Saint Georges 或 Durham NC。城市位于“位置”。function Wo_GetAllJobs($filter_data = array()) {global $wo, $sqlConnect;$data = array();$query_one = " SELECT * FROM " . T_JOB . " WHERE status = '1'";if (!empty($filter_data['c_id'])) {$category = $filter_data['c_id'];$query_one .= " AND `category` = '{$category}'";}if (!empty($filter_data['after_id'])) {if (is_numeric($filter_data['after_id'])) {$after_id = Wo_Secure($filter_data['after_id']);$query_one .= " AND `id` < '{$after_id}' AND `id` <> $after_id";}}if (!empty($filter_data['keyword'])) {$keyword = Wo_Secure($filter_data['keyword']);$query_one .= " AND (`title` LIKE '%{$keyword}%' OR `location` LIKE '%{$keyword}%') ";}if (!empty($filter_data['user_id'])) {$user_id = Wo_Secure($filter_data['user_id']);$query_one .= " AND `user_id` = '{$user_id}'";}if (!empty($filter_data['type'])) {$type = Wo_Secure($filter_data['type']);$query_one .= " AND `job_type` = '{$type}'";}if (!empty($filter_data['length'])) {$user_lat = $wo['user']['lat'];$user_lng = $wo['user']['lng'];$unit = 6371;$query_one = " AND status = '1'";$distance = Wo_Secure($filter_data['length']);if (!empty($filter_data['c_id'])) {$category = $filter_data['c_id'];$query_one .= " AND `category` = '{$category}'";}if (!empty($filter_data['after_id'])) {if (is_numeric($filter_data['after_id'])) {$after_id = Wo_Secure($filter_data['after_id']);$query_one .= " AND `id` < '{$after_id}' AND `id` <> $after_id";}}if (!empty($filter_data['keyword'])) {$keyword = Wo_Secure($filter_data['keyword']);$query_one .= " AND (`title` LIKE '%{$keyword}%' OR `location` LIKE '%{$keyword}%') ";}if (!empty($filter_data['user_id'])) {$user_id = Wo_Secure($filter_data['user_id']);$query_one .= " AND `user_id` = '{$user_id}'";}if (!empty($filter_data['type'])) {$type = Wo_Secure($filter_data['type']);$query_one .= " AND `job_type` = '{$type}'";}

Answer 1

用空格分割你的关键字，然后将它们内爆 %

//TODO query_one

//$query_one .= " AND (`title` LIKE ? OR `location` LIKE ?) ";

$stmt = $mysqli->prepare($query_one);

$keyWord = "%" . implode("%", explode(" ", $keyword)) . "%";

$stmt->bind_param("s", $keyWord));

$stmt->bind_param("s", $keyWord));

$stmt->execute();

请记住，准备好的语句是必须的并且易于切换到

笔记：

%在关键字开头使用会使优化器无法使用该列上的索引，如果存在的话，所以如果您的数据量很大并且您开始遇到性能问题，您可以去全文搜索（FTS ) 检查这个

不要担心 MySQL 上的区分大小写比较

关于 MySQL 将执行的比较案例，我将引用手册“字符串搜索中的大小写敏感性”

对于非二进制字符串（CHAR、VARCHAR、TEXT），字符串搜索使用比较操作数的排序规则。对于二进制字符串（BINARY、VARBINARY、BLOB），比较使用操作数中字节的数值；这意味着对于字母字符，比较将区分大小写。

..

默认字符集和排序规则是 utf8mb4 和 utf8mb4_0900_ai_ci，因此默认情况下非二进制字符串比较不区分大小写。

Answer 2

这就是我最终解决这个问题的方式。

if (!empty($filter_data['keyword'])) {

    $keyword = $filter_data['keyword'];

    $keywordSearch = Wo_Secure(str_replace(',','',str_replace('-','',str_replace('.','',str_replace(' ', "", $keyword)))));

    $keyword = Wo_Secure($keyword);

    $query_one .= " AND (`title` LIKE '%{$keyword}%' OR REPLACE(REPLACE(REPLACE(REPLACE(`location`,' ',''),'-',''),'.',''),',','')LIKE '%{$keywordSearch}%') ";

}