1

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 only the same order, ie "Ford Mustang" will show results that include "Ford Mustang" in that exact order. If a user types in "Mustang" or "Mustang Ford" then no results show. How do I change the search query to show results that have both keywords, but not necessarily in the same order? I have search through this site and others and have found similar questions, but have not been able to find any answers that I have been able to work out. 

public function getProductByName($name){
    $stmt = $this->pdo->prepare('SELECT * FROM tbl_products WHERE name LIKE :name');
    $name = "%".$name."%";
    $stmt->execute(array('name' => $name));
    return $stmt;
}

Someone suggested the following, which works to search for either keyword, but I need to search for both keywords, just not in order.

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;
}
Dharman
  • 30,962
  • 25
  • 85
  • 135

1 Answers1

0

If you can alter your table to add FULLTEXT to your name column then you can use the Full text Search option.

select *
from tbl_products
where match(name) against ('+Mustang +Ford' in boolean mode)

Check demo here

Arun Palanisamy
  • 5,281
  • 6
  • 28
  • 53
  • A caution: https://hackernoon.com/dont-waste-your-time-with-mysql-full-text-search-61f644a54dfa and some words are ignored: https://www.w3resource.com/mysql/mysql-full-text-search-functions.php – mickmackusa Nov 05 '19 at 07:56
  • A quote from w3resource: Boolean queries against a MyISAM search index can work even without a FULLTEXT index. – mickmackusa Nov 05 '19 at 08:01