What if we wanted to allow a customer to search our products? We have a few different options. We can do it the easy and slow way or we can do it the fast and hard way.
The easy and slow way is probably good for most use cases. We can do partial text searches using the likeoperator.
Let's look at that here. Lets imagine that we need a Wig.
mysql> select * from products where description like '%Wig%';
+----+-------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+-------------+--------+-------+-----------+
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+-------------+--------+-------+-----------+
1 row in set (0.00 sec)Let's look at the like operator. You'll see that we added percent signs to our text, %Wig%. Why did we add the percent signs? These percent signs are matching characters. Basically, they match any character. For example, in our products table, we have an entry for Clown Wig. How do percent signs match that record? The first percent sign matches Clown. Basically, the first percent sign matches anything that comes before Wig. The second percent sign matches anything that comes after. Since we are looking for anything with the word Wig in it, it will be matched. What happens if we remove the first percent sign?
mysql> select * from products where description like 'Wig%';
Empty set (0.00 sec)What happens if we put the first percent sign back in and then we remove the second percent sign?
mysql> select * from products where description like '%Wig';
+----+-------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+-------------+--------+-------+-----------+
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+-------------+--------+-------+-----------+
1 row in set (0.00 sec)You get a match. Why? Because in our description field, Wig is the last word in the field. We don't need the second percent sign to match the record.
Let's reverse it and only search for Clown.
mysql> select * from products where description like '%Clown%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
| 8 | Rainbow Clown Suit | Fabric | 5.95 | 0 |
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+--------------------------+--------+-------+-----------+
3 rows in set (0.00 sec)And if we remove the first percent sign?
mysql> select * from products where description like 'Clown%';
+----+-------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+-------------+--------+-------+-----------+
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+-------------+--------+-------+-----------+
1 row in set (0.00 sec)And if we only use the first percent sign?
mysql> select * from products where description like '%Clown';
Empty set (0.00 sec)We get an empty set because there are no characters that come before Clown in any of our records.
What happens if the user types everything in lowercase? The query still works, because the like operator is case-insensitive.
mysql> select * from products where description like '%clown%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
| 8 | Rainbow Clown Suit | Fabric | 5.95 | 0 |
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+--------------------------+--------+-------+-----------+
3 rows in set (0.00 sec)It's the same if the user typed all uppercase.
mysql> select * from products where description like '%CLOWN%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
| 8 | Rainbow Clown Suit | Fabric | 5.95 | 0 |
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+--------------------------+--------+-------+-----------+
3 rows in set (0.00 sec)What are some issues with searching text using likes? Well, what if a user wants to find Clown Shoes, but they type their query, Shoes Clown?
mysql> select * from products where description like '%Shoes Clown%';
Empty set (0.00 sec)Things work well when the user types their query correctly.
mysql> select * from products where description like '%Clown Shoes%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
+----+--------------------------+--------+-------+-----------+
1 row in set (0.00 sec)In an ideal world, users would type their search text the same way we have it in the database! In the real world, users type all kinds of weird stuff. We want to create an environment where our users can type anything and know they're going to get as many results back as they imagine they should get...for a Clown Store. If they want to type Shoes Clown, they should still be able to find something. Right? We're in the business of selling Clown Store items! We still want these users to find stuff no matter what they type! How do we make this work?
There are two ways to do it. The slow easy way will be mentioned here and the fast hard way will be talked about in the next lesson.
Basically, you need to turn your search query into multiple queries. You would need to use your backend language to split each word into an array. Then you would create a query that used the and operator to create a condition where both words are found, no matter which order the words came in.
mysql> select * from products where description like '%Shoes%' and description like '%Clown%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
+----+--------------------------+--------+-------+-----------+
1 row in set (0.00 sec)You could reverse the two words and you would get the exact same results.
We can relax the query a little bit by replacing the and in the last query to an or. Let's do that:
mysql> select * from products where description like '%Shoes%' or description like '%Clown%';
+----+--------------------------+--------+-------+-----------+
| id | description | finish | price | isDeleted |
+----+--------------------------+--------+-------+-----------+
| 2 | Large Floppy Clown Shoes | Rubber | 19.95 | 0 |
| 8 | Rainbow Clown Suit | Fabric | 5.95 | 0 |
| 9 | Clown Wig | Hairy | 7.95 | 0 |
+----+--------------------------+--------+-------+-----------+
3 rows in set (0.00 sec)Why did this query return three rows when the query before only returned one row? This is because, in the original query, the words Shoes and Clown must exist in the description field. If both of those words don't exist in one string, the row is skipped and not returned.
In this last query, one or both of the words can exist in any one description.
If you so desire, you can offer a choice to your users whether they want an or query, or an and query.
Just to stay on the safe side, I would go for the or query. I mean hey, we're trying to sell things right? Might as well give the user plenty of opportunities to explore our products. They might discover something new and buy that too!