How to search for multiple keywords using sql search

Question

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

Which dbms are you using? (REGEXP is usually a bit product specific.) — jarlh, Nov 05 '19 at 08:37
Duplicate your questions https://stackoverflow.com/questions/58704735/how-to-search-for-multiple-keywords-in-sql — Au Nguyen, Nov 05 '19 at 08:40
I am using mySQL and am using PHP on my server to do the actual search function — , Nov 05 '19 at 08:42

vertikaldash · Answer 1 · 2019-11-05T09:03:38.190

1

maybe this what you're looking for

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

edited Nov 05 '19 at 09:03

answered Nov 05 '19 at 08:55

vertikaldash

11
3

I need to do it in PHP as per the above sample code. Also, the actual search terms will vary depending upon user input – Nov 05 '19 at 09:10
put the query inside $stmt, and if u want to use user input, then u can pass $name to query something like this `$stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name like "%' .$name.%"');` – vertikaldash Nov 05 '19 at 10:26
I have tried that way and it works, but only in word order, ie if user puts "Ford Mustang" then it will search for Ford and Mustang in that order. If the data was "the ford was a mustang" it will not show search results because Ford and Mustang are not text to each other – Nov 05 '19 at 10:36
`select * from example where name REGEXP "^(?=.*ford)(?=.*mustang).*$" ` change ford and mustang with input. `^ means start of line, $ means end of line, ?= means lookahead, .* means match any characters` – vertikaldash Nov 07 '19 at 09:00
**edit** : add `?` before `ford/mustang(input)` to make it lazy search, so it will stop searching when find `input` `select * from example where name REGEXP "^(?=.*?ford)(?=.*?mustang)"` – vertikaldash Nov 07 '19 at 09:26

score 0 · Answer 2 · answered Nov 05 '19 at 09:31

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

How to search for multiple keywords using sql search

2 Answers2