How do I show mysql query results for fields that contain certain data?

Question

I have a mysql query:

$query = "SELECT * FROM movielist WHERE genre = 'comedy' ORDER BY dateadded DESC";

I need to change it so that it selects from movielist where genre contains 'comedy'.

(Note: that my genre field will often contain more than one genre example "comedy, action, drama".

Answer 1

You need to LIKE the genre:

$query = "SELECT * FROM movielist WHERE genre LIKE '%comedy%' ORDER BY dateadded DESC"

Answer 2

$query = "SELECT * FROM movielist WHERE genre LIKE '%comedy%' ORDER BY dateadded DESC";

OR

$query = "SELECT * FROM movielist WHERE FIND_IN_SET('comedy', genre) ORDER BY dateadded DESC";

Second one is better in most cases.

Even better, you should use a separate table for facilitating many-to-many relationships.

i.e. New table called 'movies_genres' with two fields - movie_id and genre_id (both indexed foreign keys). Every time you add a new genre to a movie or a new movie to a genre, add an entry to this table.

To find all movies belonging to a particular genre:

SELECT movies.*
FROM movies
    JOIN movies_genres
        ON movies_genres.movie_id = movies.id
    JOIN genres
        ON movies_genres.genre_id = genres.id
WHERE genres.name = 'comedy'

To find all genres belonging to a particular movie:

SELECT genres.*
FROM genres
    JOIN movies_genres
        ON movies_genres.genre_id = genres.id
    JOIN topics
        ON movies_genres.movie_id = movies.id
WHERE movies.name = 'Citizen Kane'

Answer 3

For an immediate solution, do a Dexter suggested. But in the long run you will get a lot of benefit from normalizing your database

Answer 4

With your schema you might be interested in find_in_set()

SELECT
  x,y,z
FROM
  movielist
WHERE
  find_in_list('comedy', genre)

Keep in mind that this is not index-friendly and potentially slow.

---

With normalized tables you wouldn't have genre='comedy, action, drama' (i.e. structured data within one field) but a table

genres (
  id int auto_increment,
  genre_name varchar(...)
  ...
)

a table

movielist (
  id int auto_increment,
  title varchar(...)
  ...
)

and a table

moviegenres (
  movieid int,
  genreid int,
  ...
)

And a query that "glues" the information stored in these three tables together using one or two JOINs.