Generally, while working with simple search engines programmers have to anticipate issues involving users’ typos and mistakes. In this article I would like to present, compare and contrast three main types of full text searching in MySQL database – LIKE, SOUNDEX and MATCH AGAINST as these methods are some of the most popular solutions as well as they are readable and easy to implement.

Code examples below are prepared in Symfony 2.1 edition with preinstalled Doctrine 2 ORM extension.

LIKE

It comes as no surprise that LIKE is a first option to consider. It’s a matching pattern used in SQL simple regular expression comparison.

It’s often implemented to search a piece of text in database’s table. There are two main types of regular expression signs:

  • % – matches any number of characters
  • _ – matches exactly one number of character

In example

SELECT login WHERE login LIKE “%@xsolve.pl”;
this statement should return us all logins in domain xsolve.pl.

But we can choose another way and use it to search all logins in all kind of xsolve namespaces:

SELECT login FROM User WHERE login LIKE “%@xsolve.%”;

Symfony 2 implementation

In Symfony 2 framework with Doctrine 2 implementing this kind of searching is very simple, because this part is built in QueryBuilder.

You can use it in repositories’ classes:

$query = $this>createQueryBuilder(‘u’)
->where(‘u.login LIKE :login’)
->setParameter(‘login’,”%$login%”)
->getQuery();

return $query->getResult();

You can find more in MySQL documentation’s site.

SOUNDEX

Soundex is a more advanced function to compare text – not only it matches strings but also guesses if there are any typos. Unfortunately, it doesn’t work with foreign characters (for example with Polish or Russian language).
Soundex function returns a soundex string from parameter and that means two strings that sound almost identical should have the same soundex.
Additionally all non-alphabetical characters in parameters are ignored and all international alphabetical characters outside the A-Z range are treated as vowels.

In example

SELECT title FROM posts WHERE title SOUNDS LIKE “Atricle”;
this statement should return a ‘title’ of an ‘Article’ if there is one at all.

Likewise:
SELECT title FROM posts WHERE SOUNDEX(title) = SOUNDEX(“Atricle”);

Symfony2 implementation

Unfortunately, Soundex isn’t supported by Doctrine2.2, however there is a simple method to implement a DQL function.

First of all, we have to create a DQL class with a new method which returns string to SQL.

Then we configure Doctrine to find our new DQL option:

All right. Now we can use it in queryBuilder:

$query->andWhere(‘SOUNDEX(q.street) = SOUNDEX(:street)’);

You can read more about this method in MySQL documentation.

MATCH AGAINST

MySQL performs full text search using match()…against() syntax. MATCH() takes a comma-separated list that indicates columns to be searched. AGAINST() takes a string to search for and an optional modifier that determines type of search to perform.
The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because it can be different for each row.

This syntax has three types of searches:

  • Boolean – interprets the search string using the rules of a special query language. The IN BOOLEAN MODE modifier specifies a boolean search.
  • Natural – interprets the search string as a phrase in natural human language (a phrase in free text). Full-text searches are natural language searches if no modifier is given.
  • Query expansion – is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The WITH QUERY EXPANSION modifier specifies a query expansion search.

In example

SELECT * FROM film_text WHERE MATCH (description) AGAINST (‘redeem’);

Symfony2 implementation

This case is similar to SOUNDEX method, but we will take a look at Jérémy Hubert‘s code.

We need to add a new function into config.yml file as in SOUNDEX example. After that we can use it in Symfony application:

You can read more about this method in MySQL documentation.


 

We have 10-years experience with over a 100 completed projects in Symfony2, PHP and Java. Our mission is to help people around the world achieve business goals. 

 

Tell us about your software project or the software team you need.

We’ll be in touch right away!

hello@xsolve.pl

xsolve.pl/contact

white Land Rover
Business Featured Post

How BlaBlaCar beat the competition in the race for market domination in 27 countries.

When BlaBlaCar approached XSolve, they were at a stage of rapid growth with around 24 million users already buzzing about their excellent carpooling platform. But the visionaries at BlaBlaCar...

ebook cover
Business Featured Post

E-book: How to Create an Agile Office

We present to you an e-book which collects all our experiences and thoughts regarding creation of an agile office. Hands on, practical, functional. This resource was created primarily for...

Holacracy why it’s a good idea to share the power
Business Featured Post

Holacracy: why it’s a good idea to share the power

We are on standing on the brink of the AI revolution. Researchers at the University of Oxford predict that in the next two decades up to 66% of American...

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies.

To find out more click here

Ok, Thanks