CODEWITHSUNDEEP
phpsql
user12324049
user12324049

Reputation:

How to search for multiple keywords using sql search

I have a current SQL search query that lets users enter keywords to search for my SQL database. At the moment, the search will work with multiple words, but will show all results for either keyword. If you type in "Ford Mustang" it will show all results that have either "Ford" or "Mustang", but I need it to only show results that show both "Ford" and "Mustang".

What I have tried is below

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name REGEXP :names');
    $names = "[[:<:]](" . str_replace(" ", "|", $name) . ")[[:>:]]";
    $stmt->execute(array('names' => $names));
    return $stmt;
}

Upvotes: 0

Views: 328

Answers (2)

Kalpashree V. Bal
Kalpashree V. Bal

Reputation: 310

You can write the query

select * from tbl_products where name like "%Mustang%" and name like "%ford%";

PHP code

//you may split search string like
$searchArray = explode(' ', $name);

//for loop for preparing the query
$query = 'SELECT * FROM tbl_products WHERE ';
$searchParams = array();
$conditions = [];
for($searchArray as $searchStr){
    $conditions[] = 'name like ?';
    $searchParams[] = "%$searchStr%";
}

//attach the conditions
$query .= implode(" and ", $conditions);

//execute the query 
$stmt = $this->pdo->prepare($query);

$stmt->execute($searchParams);

Upvotes: 0

vertikaldash
vertikaldash

Reputation: 11

maybe this what you're looking for

select * from example where name like "%mustang%ford%"

Upvotes: 1

Related Questions