How to query one result of genre from array genres in php and mysql?

Question

I stored array genres (action,adventure,animation) in mysql database.

If i want to retrieve all results of queries where that item have one of genre i searched for, for example animation, how could i do in php and mysql?

Please give me some instruction.

Thank anyway

please explain in detail , what are you trying to do , and what you have tried — sandeepKumar, Jul 09 '15 at 06:42
I want to create a movie database which one movie has at least one genre eg. animation or action. So, I insert that item in the database and in column genres I stored it as array like animation,action,adventure. Finally I want to query the movie database back which have one genre as my keyword. so, how can i do with php? — Hour Sokaon, Jul 09 '15 at 06:51
possible duplicate of [MySQL query finding values in a comma separated string](http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string) — reaanb, Jul 09 '15 at 07:18

score 0 · Accepted Answer · answered Jul 09 '15 at 07:12

It sounds like you're storing the genres as a comma-separated string. You can use the MySQL function FIND_IN_SET, i.e.:

SELECT *
FROM movies
WHERE FIND_IN_SET('adventure', genres) > 0

Another way to check a CSV field string is via the LIKE operator, like so:

SELECT *
FROM movies
WHERE ',' + genres + ',' LIKE '%,adventure,%'

We surround the genres field with commas so that the format is more regular.

Unfortunately, CSV fields don't index as easily, and your query may be slow. A better solution would be a many-to-many table of movies and genres.

How to query one result of genre from array genres in php and mysql?

1 Answers1