0

I have created a search function on my site that searches my DB using LIKE as shown here:

$term = $_GET['search'];
$newterm = '%'.$term.'%';

$params = [$newterm];
$sql = "SELECT * FROM products WHERE product_name LIKE ?";
$stmt = DB::run($sql,$params);

However, this isn't great for misspellings or additional characters.

So for example, if my DB contains: [blue hat, red hat, black hat, white hat]. If I search for hat, all the results come back as expected. If I search for hats, I get no results. If I search for hta, I get no results.

Is there a better way to search the DB for similar results or have a warning like Google that says something like this based on what actually is in the DB:

You searched for hta, did you mean hat?
Paddy Hallihan
  • 1,624
  • 3
  • 27
  • 76
  • 1
    You probably want a combination of full-text searching and a database of common misspellings (like https://en.wikipedia.org/wiki/Wikipedia%3aLists_of_common_misspellings/For_machines) – Devon Bessemer Jun 19 '18 at 15:15
  • [Natural Language Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html) with FULLTEXT indexes or [Apache Solr](http://lucene.apache.org/solr/) choose your poison. – CD001 Jun 19 '18 at 15:15
  • Maybe this thread is useful, https://stackoverflow.com/questions/35212058/mysql-search-for-right-words-fixing-spelling-errors – user3783243 Jun 19 '18 at 15:15
  • What you are looking for is call the `levenshtein` method. IT's really intensive and will dog MySQL down pretty good. At that point you might think about a third party indexing tool like `SOLR`. It basically indexes your MySQL and creates XML files -- It makes for faster searches and a much better version of the `levenshtein` type search. – Zak Jun 19 '18 at 15:25

0 Answers0