I have a database:
id | movie_name | genres
1 | Die Hard | Action, Thriller
2 | Gladiator | Adventure, Action, Drama, History
3 | Harry Potter and the Sorcerers Stone | Fantasy, Adventure, Family
4 | Pearl Harbor | Action, Melodrama, War
How I can select unique genres from genres
of all database.
I need the next: Action / Adventure / Drama / Family / Fantasy / History / Melodrama / Thriller / War
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.
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.