如何使用MYSQL“LIKE”正确检查关键字

Question

I am wondering how I can properly check keywords with SQL "LIKE";

I have a script which looks like that:

$search_keywords = $_POST['search-keywords'];
$search_keywords = str_replace(" ","%", $search_keywords);
$search_keywords = '%' . $search_keywords . '%';
$search = $odb -> query("SELECT * FROM `cars` WHERE `keywords` LIKE '".$search_keywords."' ORDER BY `id` ASC");
$rows = $search->rowCount();
if ($rows > 0) {//Loop}

In fact, it's working but only when keywords in the database look like that:

audi, a3, silver and I search for audi a3

If i am trying to search like that: a3 audi it's not working

Any hints?

Answer 1

You need to split $search_keywords into separate words, and search for each of them.

To search for an item in a comma-separated list, use FIND_IN_SET() rather than LIKE. LIKE '%audi%' will also match audi-quatro.

$keyword_array = explode(" ", $_POST['search_keywords']);
foreach ($keyword_array as &$keyword) {
    $keyword = "FIND_IN_SET('$keyword', keywords)";
}
$tests = implode(' AND ', $keyword_array);
// $tests contains something like: FIND_IN_SET('a3', keywords) AND FIND_IN_SET('audi', keywords)
$sql = "SELECT * FROM `cars` WHERE $tests ORDER BY `id` ASC";
$search = $odb->query($sql);