MySQL query - how to look for certain string in the field

Question

I have a table "story" as follows:

+++++++++++++++++++++++++++++++++++++++++++
|   id   |    keywords                    |
+++++++++++++++++++++++++++++++++++++++++++
|    1   | romance,movie,drama            |
|    2   | newmovie,horor,comedy          |
|    3   | movie,scifi                    |
|    4   | newmovie,romance,drama,asia    |
|    5   | kids,movie                           |
+++++++++++++++++++++++++++++++++++++++++++

I try a query to search 'movie' in keywords field as below:

SELECT id FROM story WHERE keywords LIKE '%movie%'

and the result is

1,2,3,4,5

but in this case I wanted the result is 1,3,5 (field value with newmovie not include). Can someone help me how the query to do it?

Thank you for your help..

Answer 1

You want to use find_in_set like this:

SELECT id FROM story WHERE find_in_set('movie', keywords) > 0;

Though you should really consider normalizing your table structure.

In this case, you could've stored one single keyword in one row, then the query would be simply like:

select id from story where keyword = 'movie';

and that would've been the end of it. No heavy string functions needed.

You could have structure like this:

keywords(id, name);

story(story_id,. . ., keyword_id);

then, you could simply join the two like this:

select s.* 
from story s
inner join keywords k on s.keyword_id = k.id
where k.name = 'movie';

Answer 2

Your problem is that "newmovie" can be found by "%movie%" you need only search "movie".