0

I would like to create a function where, if a user search for Nicklas, and if no matches, following suggestions should be printed to the user: Nicoline, Nicky etc. (all entries in the database) - Basically closest matches to a string.

Using a Like operator in Mysql, is close, but if there is no result, simply no results would be shown:

SELECT * FROM employees WHERE fullname LIKE '%Nicklas%'

I need somehow to expand the Like to come up with the closest names in the database, that matches the string the user types in.

ElasticSearch/Algolia has this functionality, but this is custom needed. Would would be the best way if implementing this?

I thought about using an PHP Explode of the sentence, and then LIKE in sql (left and right), but I think that would create unwanted results (duplicates).

simon
  • 2,235
  • 6
  • 33
  • 53
  • what about searching for the first two characters in a `LIKE 'Ni%'`? – Jeff Mar 16 '19 at 21:13
  • further read (search for 'phonetic search'): https://sjhannah.com/blog/2014/11/03/using-soundex-and-mysql-full-text-search-for-fuzzy-matching/ – Jeff Mar 16 '19 at 21:13
  • and related on SO: https://stackoverflow.com/questions/29598065/how-to-query-soundex-in-mysql – Jeff Mar 16 '19 at 21:14
  • I think exploding the string could work and then use like valued OR like val2 OR like... Etc, and you could use a group by to handle duplicates, but your problem would be the ordering to make sure that if your original name does exist it shows at the top of the list, you could use an ORDER BY if(name = 'searchstring',0,1), first_name... perhaps? – imposterSyndrome Mar 16 '19 at 22:12
  • The other thing is, how far do you go i.e. down to a single letter? Maybe you'd want to to substrings rather than exploding so you could have sensible options, but you'd have to think about how you'd approach that – imposterSyndrome Mar 16 '19 at 22:17

0 Answers0