How to organize my pages by genre or category? Should I be using a mysql database?

I am very new to the concept of mySQL DBs and am not sure if this is what I require to organize my website content. I am putting together a site that has a database of movies and I want to be able to have the movies divided by genre, categories, year, etc... Some of these movies have multiple genres and categories, and I don't know how to go about organizing the content and tags.

An example of what I am looking for could be something like this: http://www.thelonelyisland.com/videos

I really want to know if I am supposed to be using a mySQL database or whether or not there is another way to filter content. I apologize for the naivety of the question but I have looked everywhere and have no idea what I am doing!!

Solution

Using a database would most certainly be the best thing to. This a basic layout:

Start with a movie table: ID_movie, name, year, etc.

Genre table: ID_genre, name.

Category table: ID_category, name.

Movies have a many - to - many relationship with genre / category. So you will need a bridge table between movies and genre / category. In this table you will have the combinations between movies and genre / category.

movies_genre table: ID_movies, ID_genre

movies_category table: ID_movies, ID_category

This is a SQL query to create the table structure and sample data:

CREATE TABLE movie(
  id_movie INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  release_date DATETIME
  );

CREATE TABLE genre(
  id_genre INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE category(
  id_category INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE movie_genre (
  ID_movie_genre INT AUTO_INCREMENT PRIMARY KEY,
  ID_movie INT,
  ID_genre INT,
  UNIQUE (ID_movie,ID_genre),
  INDEX (ID_movie,ID_genre)
  );


CREATE TABLE movie_category (
  ID_movie INT,
  ID_category INT
  );

INSERT INTO movie (name, release_date) VALUES 
('Transformers 3 Dark of the moon','2012-01-01'), 
('Harry potter and the deathly hallows 2','2011-01-01'), 
('Pirates of the caribbean. On stranger tides', '2012-01-01');

INSERT INTO genre (name) VALUES ('Action'), ('Fantasy'), ('Comedy');

INSERT INTO movie_genre(id_movie, id_genre) VALUES (1,1), (2,1),(2,2),(3,2),(3,1);

SQL FIDDLE DEMO

In response to the remarks below. You could change release_date into year if you only want to have years. One way would be to turn them in to strings value (Year VARCHAR(4) instead of release_date DATETIME). Furthermore, I have include unique and index to the bridge table. Index will speed up your query time.

Below you will find a example query that will retrieve the name and genre from movie number 2. SELECT m.name AS Movie, g.name AS Genre FROM (movie AS m left JOIN movie_genre AS mg ON mg.id_movie = m.id_movie) JOIN genre AS g ON g.id_genre = mg.ID_genre WHERE m.id_movie = 2;