問題描述

我在 MySQL 中有一個用戶表，想按名稱進行搜索.現在我有以下代碼:

I have a users table in MySQL and would like a search by name. Right now I have the following code:

<?php
$search = @$_GET['q'];
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$query = $con->prepare('SELECT * FROM `users` WHERE name LIKE ?');
$query->execute(array('%'.$search.'%'));

$result = $query->rowCount();
echo $result;
?>

問題是我想要多個關鍵字.假設有人輸入這里應該是一個人的好名字"，然后它會搜索這里"、應該"、是"等，并顯示姓名"列中有單詞的每一行的結果.我在網上搜索并了解到可以執行OR name LIKE ?"與關鍵字一樣多，但我無法真正讓它工作，我不確定它是否用大約 20 個詞進行了足夠的優化(以防他們用那么多詞進行搜索).如果應該使用它，您能幫我更改我的代碼，使其獨立搜索每個單詞嗎?

The problem is that I want to have multiple keywords. Let's say someone types "Here should be a nice name of a person" then it would search for "here", "should", "be" etc. and display results for every row where there words are in 'name' column. I searched on the web and read that it is possible to do "OR name LIKE ?" as many times as the keywords, but I could not really get it working and I'm not sure if it is optimized enough with ~20 words (in case they search with that many words). If it should be used, can you help me change my code so it would search for every word independently?

謝謝！

我能夠通過在此線程中發帖的一個人解決此問題.以下解決方案對我有用:

I was able to fix this issue by one guy who posted in this thread. The following solution works for me:

<?php
$search = isset($_POST['q']) ? $_POST['q'] : '';
$search = strtoupper($search);
$search = strip_tags($search);
$search = trim($search);

$split_words = explode(" ", $search);

if(count($split_words) > 0) {

    $sql = "SELECT * FROM `users` WHERE ";

    for($i=0 ; $i < count($split_words); $i++){
        $sql .= " name LIKE ? OR";
    }

    $sql = substr($sql , 0, -3); //Remove last 3 characters OR with space
    array_walk($split_words, "addPercentage");


    $query = $con->prepare($sql);
    $query->execute($split_words);
}

function addPercentage(&$value, $key) {
    $value = '%'.$value.'%';
}
?>

推薦答案

您不應該使用 @ 來消除錯誤，這是一種不好的做法，請檢查該值是否已設置.下面的示例應該可以工作，但結果可能并不完全相關.

You shouldn't use @ to silence errors it is a bad practice, check if the value is set. The example below should work, but the results might not be all that relevant.

$search = isset($_GET['q']) ? $_GET['q'] : ''; 
$search = strtoupper($search);
$search = strip_tags($search); 
$search = trim($search);
$words = explode(' ', $search);
$words_condition = array();
$arguments = array();
foreach ($words as $word) {
    $words_condition[] = 'name LIKE ?';
    $arguments[] = '%'.$word.'%';
}

$query = $con->prepare('SELECT * FROM `users` WHERE '.implode(' OR ', $words_condition));
$query->execute($arguments);

$result = $query->rowCount();
echo $result;

這篇關于PHP PDO MySQL 查詢 LIKE ->多個關鍵詞的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！