-1

I'm working on a project involving PHP and MySQL. I admit that I'm pretty new to both.

I have a table that has some company names, let's say CocaCola, if the user searches CocaCola, I have to read some info from the table. My question is, how can I make it work even if the user writes CocaCola Company, or anything else that matches?

I already implemented SOUNDEX which gives a basic error handling, but now I'd like something that allows me to check if the name that's inside my table (CocaCola) matches the string coming from the user ("CocaCola Company" includes "CocaCola").

I found the "LIKE" statement

 WHERE Name LIKE %$name%

but it seems it works the way around, it can detect if the user writes something less, but how to detect if he/she writes something more? (something like 

WHERE %Name% LIKE $name

if it even exists).

Cœur
  • 37,241
  • 25
  • 195
  • 267
FrCr
  • 263
  • 7
  • 19
  • LIKE can detect both more and less...you need to explain your question a lot better – developerwjk Jul 20 '15 at 22:25
  • You start by collecting a list of synonyms. After you have that, you can ask another question. – Gordon Linoff Jul 20 '15 at 22:29
  • Why would you want to support such functionality? If the record you have is `CocaCola` and you want to support `CocaCola Company`, then by the same logic any user could stick any gibberish after the actual value that you store, meaning they would get same results for `CocaCola`, `CocaCola Company` and `CocaCola 12345123gsfhfha0502$$$$$$$------111111111`. If they write stupid stuff and you don't have it, then that's it - they get no results. – N.B. Jul 20 '15 at 22:30
  • @developerwjk how can I use LIKE to detect "less"? It seems like I need to put the column name first, then LIKE, then a string to compare it, and I can only apply special characters like % to this string. Am I wrong? – FrCr Jul 20 '15 at 22:33
  • 1
    Just put the value first, then follow with the column surrounded by '%' characters: `SELECT * FROM Companies WHERE 'CocaCola Company' LIKE CONCAT('%', Name, '%');`. Also think about the advice you got in the comments here - fuzzy searches are much more than what you're trying to do. – Ed Gibbs Jul 20 '15 at 23:19

1 Answers1

0

If you really want to check for the user writing less why not create a clause that checks the search string length and if it is say 12 or more characters, use the SUBSTRING function to search the first 8 characters input by the user. 

WHERE NAME LIKE CONCAT('%', SUBSTRING($searchString, 8), '%')

It sounds like your project could really benefit from a robust search library. Have you tried looking at a library or plugin similar to Apache Lucene? I know Lucene is written in Java but looking it up may help you find a powerful plugin written in a language appropriate for your project.

Nathan
  • 3,082
  • 1
  • 27
  • 42