MySQL select genres issue (php)

I have a database:

id       |                movie_name              |           genres
1        |                 Die Hard               |        Action, Thriller
2        |                Gladiator               | Adventure, Action, Drama, History
3     开发者_如何学Go   |  Harry Potter and the Sorcerers Stone  |    Fantasy, Adventure, Family
4        |               Pearl Harbor             |      Action, Melodrama, War

1) How I can select unique genres from genres of all database. I need the next: Action / Adventure / Drama / Family / Fantasy / History / Melodrama / Thriller / War

2) How can I see a movie of a certain genre?

SELECT `movie_name` FROM `movies` WHERE `genre` LIKE ??

But he also can bring not only the drama, but melodrama.

3) How to make a search on a particular genre? May be:

 SELECT `movie_name` FROm `movies` WHERE `movie_name` LIKE `%stone%` AND `genres LIKE 'drama'.

Don't store a comma-delimited list of attributes in a database column.

Instead, have 3 tables:

Movies (id, movie_name)

id | movie_name
---+--------------------------------------
 1 | Die Hard
 2 | Gladiator
 3 | Harry Potter and the Sorcerers Stone
 4 | Pearl Harbor

Genres (id, genre_name)

id | genre_name
---+------------
 1 | Action
 2 | Thriller
 3 | Adventure
 4 | Drama
 5 | History
 6 | Fantasy
 7 | Family
 8 | Melodrama
 9 | War

MovieGenre (movie, genre)

Movie | Genre
------+-------
    1 | 1
    1 | 2
    2 | 1
    2 | 3
    2 | 4
    2 | 5
    3 | 3
    3 | 6
    3 | 7
    4 | 1
    4 | 8
    4 | 9

Then your problems become much, much simpler.

The problem you're facing here is implementing an N to N relationship requirement.

Since the relation between movies is a Many to Many relationship, the proper way of storing this in a database schema is to maintain the relationship in a separate table.

Let's call this table "Movie_Genre_Relationship" for the sake of this exercise. If Die Hard is both an Action & Thriller movie, you should store it with two rows in the relationship table as such:

Movie_Genre_Relationship
Movie_id | Genre_id
1        | 1
1        | 2

Assuming the following Movie and Genre tables:

Movies
Movie_id | Movie_Name
1        | Die Hard

Genres
Genre_id | Genre_Name
1        | Action
2        | Thriller

Then you can search all Action movies by doing:

select * from Movies m
inner join Movies_Genres_Relationship r on m.movie_id = r.movie_id
where r.genre_id = 1

Hope it helps.