Count number of LIKE-matches per Entry

Question

I try to explain what I mean by matches on a simple example:

I have a table myprods like this:

id | name
1    galaxy s4 mini
2    samsung galaxy s4
3    galaxy galaxy s3
4    iphone 4s
5    apple iphone 4s

I'd have this query so far:

SELECT *
FROM   myprods
WHERE  name LIKE "%samsung%" OR name LIKE "%galaxy%" OR name LIKE "%s4%"

The result of my query is:

id | name
1    galaxy s4 mini
2    galaxy s4
3    galaxy galaxy s3

and now I'd not only return the matched rows but also the number of hits. ONE hit would be if ONE of the LIKE-phrases applies to the row. So in this case I would expect the hits to be 0, 1, 2 or 3. That means: the LIKE phrase "%galaxy%" should NOT be counted as two hits for the id=3, it just counts as "hit" or "not hit".

The expected result would now be:

id | name               | hits
1    galaxy s4 mini       2
2    samsung galaxy s4    3
3    galaxy galaxy s3     1

Possible in a performant mysql phrase? Or should I rather select only the rows with my query above and then match the single substrings to the returned names via strpos in PHP?

Answer 1

In MySQL, boolean expressions can be used as integers -- with 0 for false and 1 for true. So, the following works:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
WHERE  name LIKE '%samsung%' OR name LIKE '%galaxy%' OR name LIKE '%s4%';

EDIT:

If MySQL, you can also express this query as:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
HAVING hits > 0;

The use of having in this context is a SQL extension and doesn't work in other databases. But, it does allow a query to refer to a column alias for filtering, without using a subquery.