Trying to count how many times words from one table match a column in another table

I am not even sure if a query like this is possible without some scripting around it.

I have a table of keywords (ie: bunny, hamster, chicken, clock, wall, etc.) and I want to count how many times those keywords are found in the title column of another table in my database.

The other table has a column called ‘title’ and it is filled with rows of information like:

The black bunny and yellow chicken turned back the clock.
We have five clocks on our wall.
Our house has two hamsters and one chicken stuck in the wall.
Every bunny has its picture on someones clock.

I want the query to count up how many times the keywords appear in all of the rows of titles.

Any ideas?

SELECT COUNT( w.word ) AS total, w.word
FROM data d
RIGHT JOIN words w ON ( w.word LIKE '%d.title%' )
GROUP BY w.word

I attempted this, but it just returns 1 for the count for each of the words.

@zeropsi Interesting.

I have been trying to accomplish something very similiar with zero luck so far.

I don’t necessarily have a solution, however, I think the RIGHT JOIN clause is incorrect.

assuming this is for mysql (if not, the concatenation function is different)…

     , w.word
  FROM words w
  JOIN data d 
    ON d.title LIKE CONCAT('%',w.word,'%') 
    BY w.word
    BY total DESC