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, %ric%
, %m%
, %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.
To Clarify
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?
LIKE '%rick flair%'
intoLIKE %'; SELECT * FROM information_schema.tables;
It can be a hassle trying to recreate the wheel... you might try looking at ORMs available for PHP.WHERE LIKE "Search String"
. There's really no rhyme or reason besides that for returning results.