5

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?

  • 1
    I think you could improve this question by scoping it a bit more narrowly, removing the resource requests, and explaining in more detail what the goals of your search algorithm are. – user22815 Oct 31 '16 at 13:52
  • Be careful.. if your search is as simple as you describe, it looks vulnerable to SQL Injection. If an end user were to pass in a quote and semicolon they could possibly turn LIKE '%rick flair%' into LIKE %'; SELECT * FROM information_schema.tables; It can be a hassle trying to recreate the wheel... you might try looking at ORMs available for PHP. – DanK Oct 31 '16 at 17:24
  • @Dank Not to worry, we're actually using Laravel with prepared statements and all that. My point was that our global search pours through contacts, tasks, sales opportunities, and all that simply based on WHERE LIKE "Search String". There's really no rhyme or reason besides that for returning results. – Captain Hypertext Oct 31 '16 at 18:26
3

You may want to have a look at Lucene. It's written in Java but you can use it from PHP through either its Zend port or Solr, although I think Solr might not be a solution since you probably want something embedded within your CMS.

The idea is that you can build an index from all the searchable data and then search that index. It has the advantage that it can be much faster than database queries. You can also implement a scoring system, so that some records show up higher in the results list if they are more relevant to the user. One possible downside is that you have to update the index each time the data is updated.

If the Lucene approach doesn't fit your scenario, you could go with a mix of PHP and database code. A view or a stored procedure could act as a surrogate index, aggregating the searchable data. You'd need to split strings by whitespaces and this could make it rather slow, but will get you there. The PHP code will be responsible for building the WHERE clause (you can have a look at how Solr implements query strategies for suggestions as source of inspiration). Assuming you have Id, Name and Score as index fields and two contact records with Id=1, FirstName="Ric", LastName="Flair" and Id=2, FirstName="Ric", LastName="Flare" the index records would be looking something like this:

+--------------+-------+
| Id | Name    | Score |
+----+---------+-------+
| 1  | Ric     |   1   |
+----+---------+-------+
| 1  | Flair   |   1   |
+----+---------+-------+
| 2  | Ric     |   1   |
+----+---------+-------+
| 2  | Flare   |   1   |
+----+---------+-------+

An example could look like this (SQL Server):

DECLARE @index TABLE (
    Id INT NOT NULL,
    Name NVARCHAR(50) NOT NULL,
    Score INT NOT NULL
)

INSERT INTO @index (Id, Name, Score) VALUES (1, 'Ric', 1)
INSERT INTO @index (Id, Name, Score) VALUES (1, 'Flair', 1)
INSERT INTO @index (Id, Name, Score) VALUES (2, 'Ric', 1)
INSERT INTO @index (Id, Name, Score) VALUES (2, 'Flare', 1)

SELECT
    Id,
    SUM(Score) AS Score
FROM
    @index
WHERE
    Name = 'Ric'
    OR Name = 'Flair'
GROUP BY
    Id
ORDER BY
    Score DESC

Ric Flair would have a higher score since it matches on both values, thus popping first in the search results.

The index could also contain title and summary fields to be used as values to be displayed in the search results page. Or you could join the results with a view that preselects those values.

You can toy around with the conditions in the WHERE clause or with the Score field (you could give different scores to different type of records or properties) to get a more refined search experience.

  • Yeah, the data does get updated regularly. If a user entered a contact 5 minutes ago, they should be able to search and find them. – Captain Hypertext Nov 2 '16 at 12:13
  • @CaptainHypertext i've updated the answer to include a possible database solution – devnull Nov 2 '16 at 12:58
0

You can normalize data to search for Ric Flair as a person entity first and then display contacts of this person. This is a way of Big CRM and is suitable if Ric is important for you, a customer. You should expect a sophisticated search control for person entities via many data attributes.

Often you don't want to create a person each time, for example as an alternative contact for an order. In this case you can't do much but provide operators with clear pattern and/or instruction on how to fill the name field while capturing data.

Split of search pattern by whitespaces of course would produce more results, however my guess is that users would not like it since this is not a standard pattern.

1

I would look into Elasticsearch.

Elasticsearch is sort of a document database. It's a bit like MongoDB, but it's designed for very sophisticated text searches. I believe Stack Exchange itself uses Elasticsearch for the search box at the top of the page. It's also part of ELK (which stands for Elasticsearch, Logstash, and Kibana). It's built on top of Lucene, but it has a lot of extra functionality for search that you wouldn't want to have to build yourself.

I found this package for using Elasticsearch from Laravel. You'll have to integrate it into your infrastructure somehow, but in return you'll get very powerful search capabilities without a lot of extra work.

Solr is similar; it's also built on Lucene and offers powerful search capabilities. Either of these is a good choice that's much more powerful than SQL LIKE and easier to use than Lucene directly.

  • You still have to update the indexs when CMS data changes. It comes with many capabilities that you probably wont use. To install an Eslatiksearch, in this case, seems to me overkill. Plus you add a new component to maintaint. – Laiv Nov 2 '16 at 19:11
  • @Laiv Sure, it's not a drop-in solution; there will be work to integrate and maintain it. Still, it's an option that the OP can consider. – Torisuda Nov 2 '16 at 20:42
  • Just wanted to point out these drawbacks :-). To use EKS is as valid solution as a customized Indexation with Lucence – Laiv Nov 2 '16 at 21:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.