MySQL FULLTEXT search counting occurrences

Question

I there a way to get the logic behind a full text search

Example table

+----+-------------+
| id | title       |
+----+-------------+
| 1  | Hello World |
| 2  | World World |
| 3  | Hello       |
| 4  | Hello Hello |
+----+-------------+

This is the query

SELECT `title` FROM `example` WHERE MATCH(`title`) AGAINST('World')

Result

+-------------+
| title       |
+-------------+
| World World |
| Hello World |
+-------------+

Now 'World World' is first since it has the 'World' Twice Is there any way I can get somthing like this

+-------------+------------+-------+
| title       | appearance | score |
+-------------+------------+-------+
| World World | 2          | 1     |
| Hello World | 1          | 2     |
+-------------+------------+-------+

Answer 1

I would try using a syntax like this, instead of attempting to count how many times the word occurs.

Notice that it uses IN BOOLEAN MODE that means when a + is before, it must occur. https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

SELECT Title, MATCH(`title`) AGAINST ('+World +World' IN BOOLEAN MODE) AS Score 
FROM `example`
WHERE MATCH(`title`) AGAINST ('+World +World' IN BOOLEAN MODE)
ORDER BY Score DESC

It will generate a relevance score that you later can sort on.

If you however need to have the count, this will do the trick.

SELECT Title,
ROUND (
      (CHAR_LENGTH(`title`) - CHAR_LENGTH(REPLACE(`title`, "World", ""))) / CHAR_LENGTH("World")       
      ) AS appearance 
FROM `example`
WHERE MATCH(`title`) AGAINST ('World')

Notice that it is case sensitive so could be an idea to wrap title with LCASE