mysql find best matching word in one row

Question

i have following table:

------------------------
| uid |  attrvalue     |
------------------------
| 1   |  Spray         |
| 2   |  strong        |
| 3   |  very strong   |
| 999 |  Creme         |
------------------------

now i have a query in php and like to find all rows where all queries are found.

SELECT * FROM attrtable WHERE MATCH (attrvalue) AGAINST ('Spray+very+strong' IN BOOLEAN MODE);

I like that it only finds row 1 and 3. But the resultrows are 1, 2 and 3.

So its important that it founds all words or word combinations in the table rows. The query doesn't contain words or combined words which aren't in the table (I check this first).

Answer 1

Just use LIKE, but the other way around to what your probably used to.

select query
from table1
where 'attrvalue' like concat(query,'%')
order by length(query) desc
limit 1