0

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;
}
Improve this question
4
  • Which dbms are you using? (REGEXP is usually a bit product specific.) Commented Nov 5, 2019 at 8:37
  • Duplicate your questions stackoverflow.com/questions/58704735/… Commented Nov 5, 2019 at 8:40
  • I am using mySQL and am using PHP on my server to do the actual search function Commented Nov 5, 2019 at 8:42
  • Au Nguyen, that post does not answer my question Commented Nov 5, 2019 at 8:43

2 Answers 2

Reset to default
1

maybe this what you're looking for

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

I need to do it in PHP as per the above sample code. Also, the actual search terms will vary depending upon user input
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.%"');
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
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
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)"
0

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);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.