I know this question is kind of broad, but all I really need is some best-practice code structure or a link to a good tutorial.
I am working on a CRM that runs on php and mysql. Currently, our search query looks something like this:
SELECT * FROM contacts WHERE name LIKE '%ric flair%'
Now, that's really dumbed down, but that's what it amounts to. In fact, we are using prepared statements, firing off several 30+ line queries, and joining lots of tables to verify ownership and all the rest.
But if the user types
rick flair or
ric m flair, the search query will not find him. Now, I could split the string into three search terms,
%flair%, and work from there, but then I'm going to get every guy named Ric (and Rick for that matter), and then Ric Flair could be anywhere among the search results.
I feel like I'm doing this like an amateur, and when I look up info about search algorithms, all I can find is people wanting to be Google.
Any pro advice on this would be appreciated.
This is for a global search function that looks through contacts, phones, emails, tasks, sales opportunities, conversations, and notes, looking for anything that has or is connected to your search string (in this case, Ric Flair).
The main goal here is to have a "smarter" search, such that if you type
rick flair or
ric m. flair, it will still have a good chance of returning the contact
ric flair at the top, even though you might have people named Rick in you contact list. Maybe that's because the last name matched, and that's more important? Or am I overthinking this?