目前，在我的搜索欄中，如果我以完全輸入數(shù)據(jù)庫的方式輸入城市名稱，我就能找到它。例如：Nuits-Saint-Georges 或北卡羅來納州達(dá)勒姆。我希望我的用戶能夠輸入 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}'";}
2 回答
胡子哥哥
TA貢獻(xiàn)1825條經(jīng)驗(yàn) 獲得超6個贊
用空格分割你的關(guān)鍵字，然后將它們內(nèi)爆 %
//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();
請記住，準(zhǔn)備好的語句是必須的并且易于切換到
筆記：
%在關(guān)鍵字開頭使用會使優(yōu)化器無法使用該列上的索引，如果存在的話，所以如果您的數(shù)據(jù)量很大并且您開始遇到性能問題，您可以去全文搜索（FTS ) 檢查這個
不要擔(dān)心 MySQL 上的區(qū)分大小寫比較
關(guān)于 MySQL 將執(zhí)行的比較案例，我將引用手冊“字符串搜索中的大小寫敏感性”
對于非二進(jìn)制字符串（CHAR、VARCHAR、TEXT），字符串搜索使用比較操作數(shù)的排序規(guī)則。對于二進(jìn)制字符串（BINARY、VARBINARY、BLOB），比較使用操作數(shù)中字節(jié)的數(shù)值；這意味著對于字母字符，比較將區(qū)分大小寫。
..
默認(rèn)字符集和排序規(guī)則是 utf8mb4 和 utf8mb4_0900_ai_ci，因此默認(rèn)情況下非二進(jìn)制字符串比較不區(qū)分大小寫。
白衣非少年
TA貢獻(xiàn)1155條經(jīng)驗(yàn) 獲得超0個贊
這就是我最終解決這個問題的方式。
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}%') ";
}
- 2 回答
- 0 關(guān)注
- 120 瀏覽
添加回答
舉報
0/150
提交
取消