CODEWITHSUNDEEP
phpmysql
Mark Rune
Mark Rune

Reputation: 37

How to properly check for keywords with MYSQL "LIKE"

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?

Upvotes: 1

Views: 164

Answers (2)

Yves Kipondo
Yves Kipondo

Reputation: 5603

It's a best practice to use PDO::prepare when you are passing user data in your request, to evoid SQL Injection

$search = $odb->prepare("SELECT * FROM cars WHERE keywords 
    LIKE CONCAT('%',:keyword,'%') 
    ORDER BY id ASC");
$results = $search->execute([":keywork" => $search_keywords]);

Upvotes: 0

Barmar
Barmar

Reputation: 780879

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);

Upvotes: 1

Related Questions