Tous les films
$pdo = Connect::getPDO();
$movies = $pdo->query("SELECT m.id_movie, m.title,
DATE_FORMAT(SEC_TO_TIME(m.timeMovie * 60), '%HH%imn') AS timeMovie,
DATE_FORMAT(m.releaseDate, '%d/%m/%Y') AS releaseDate,
m.synopsis,
m.image_url,
d.id_director,
p.firstName,
p.lastName,
p.birthday,
p.sex
FROM director d
INNER JOIN movie m ON d.id_director = m.id_director
INNER JOIN person p ON d.id_person = p.id_person");
Tous les genre
$pdo = Connect::getPDO();
$genres = $pdo->query("SELECT g.id_genre, g.nameGenre
FROM genre g");
Genres par id film
SELECT gm.id_genre,
nameGenre
FROM genre_movie gm
INNER JOIN genre g ON gm.id_genre = g.id_genre
WHERE gm.id_movie = 1
Détails d’un film (id_film) : titre, année, durée (au format HH:MM) et réalisateur
$pdo = Connect::getPDO();
$details = $pdo->prepare("SELECT m.title,
DATE_FORMAT(m.releaseDate, '%d %m %Y') AS releaseDate,
DATE_FORMAT(SEC_TO_TIME(m.timeMovie * 60), '%HH%imn') AS timeMovie,
m.synopsis,
m.image_url,
d.id_director,
p.firstName,
p.id_person,
p.lastName,
p.birthday,
p.sex
FROM director d
INNER JOIN movie m ON d.id_director = m.id_director
INNER JOIN person p ON d.id_person = p.id_person
WHERE id_movie = :movie_id");
Liste des films et de leurs reéalisateur, dont la durée excède 2h15 classés par durée (du + long au + court)
SELECT
DATE_FORMAT(SEC_TO_TIME(m.timeMovie * 60), '%HH%imn') AS timeMovie,
DATE_FORMAT(m.releaseDate, '%d/%m/%Y') AS releaseDate,
m.id_movie,
m.title,
m.synopsis,
m.id_director,
m.image_url
FROM director d
INNER JOIN movie m ON d.id_director = m.id_director
INNER JOIN person p ON d.id_person = p.id_person
WHERE timeMovie > 135
ORDER BY title ASC
Liste des films d’un réalisateur (en précisant l’année de sortie)
SELECT title, DATE_FORMAT(releaseDate, '%d %m %Y')
FROM person
INNER JOIN director ON director.id_person = person.id_person
INNER JOIN movie ON director.id_realisator = movie.id_realisator
WHERE person.id_person = 1
SELECT title, DATE_FORMAT(releaseDate, '%d %m %Y')
FROM person
INNER JOIN director ON director.id_person = person.id_person
INNER JOIN movie ON director.id_realisator = movie.id_realisator
WHERE person.lastName = "Wingard"
Nombre de films par genre (classés dans l’ordre décroissant)
SELECT genre.nameGenre, COUNT(movie.title) AS nombre_films
FROM genre
INNER JOIN genre_movie ON genre_movie.id_genre = genre.id_genre
INNER JOIN movie ON movie.id_movie = genre_movie.id_movie
GROUP BY genre.nameGenre
ORDER BY nombre_films DESC
Nombre de films par réalisateur (classés dans l’ordre décroissant)
SELECT person.firstName, person.lastName, COUNT(movie.title) AS nombre_films
FROM director
INNER JOIN movie ON director.id_realisator = movie.id_realisator
INNER JOIN person ON person.id_person = director.id_person
GROUP BY person.lastName, person.firstName
ORDER BY nombre_films DESC
Casting d’un film en particulier (id_film) : nom, prénom des acteurs + sexe
SELECT movie.title, person.lastName, person.firstName, person.sex, role.nameRole, DATE_FORMAT(person.birthday, '%d %m %Y') AS date_naissance
FROM casting
INNER JOIN movie ON casting.id_movie = movie.id_movie
INNER JOIN role ON role.id_role = casting.id_role
LEFT JOIN actor ON actor.id_actor = casting.id_actor
LEFT JOIN person ON actor.id_person = person.id_person
WHERE movie.id_movie = 1
Films tournés par un acteur en particulier (id_acteur) avec leur rôle et l’année de sortie (du film le plus récent au plus ancien)
select * films
Liste des personnes qui sont à la fois acteurs et réalisateurs
SELECT person.lastName, person.firstName
FROM person
INNER JOIN director ON director.id_person = person.id_person
INNER JOIN actor ON actor.id_person = person.id_person
Liste des films qui ont moins de 5 ans (classés du plus récent au plus ancien)
$pdo = Connect::getPDO();
$movies = $pdo->query("SELECT
DATE_FORMAT(SEC_TO_TIME(m.timeMovie * 60), '%HH%imn') AS timeMovie,
DATE_FORMAT(m.releaseDate, '%d/%m/%Y') AS releaseDate,
m.id_movie,
m.title,
m.synopsis,
m.id_director,
m.image_url
FROM director d
INNER JOIN movie m ON d.id_director = m.id_director
INNER JOIN person p ON d.id_person = p.id_person
WHERE DATEDIFF(CURDATE(), m.releaseDate) < 365 * 5
ORDER BY m.releaseDate ASC;");
Nombre d’hommes et de femmes parmi les acteurs
SELECT sex, COUNT(*) AS nombre_acteurs
FROM actor
INNER JOIN person ON actor.id_person = person.id_person
GROUP BY sex;
Liste des acteurs
$pdo = Connect::getPDO();
$person = $pdo->query("SELECT
DATE_FORMAT(p.birthday, '%Y/%m/%d') AS birthday,
p.id_person,
p.lastName,
p.firstName,
p.sex,
p.image_url
FROM actor a
INNER JOIN person p ON a.id_person = p.id_person
ORDER BY p.lastName ASC");
Liste des réalisateurs
$pdo = Connect::getPDO();
$person = $pdo->query("SELECT
DATE_FORMAT(p.birthday, '%Y/%m/%d') AS birthday,
p.id_person,
p.lastName,
p.firstName,
p.sex,
p.image_url
FROM director d
INNER JOIN person p ON d.id_person = p.id_person
ORDER BY p.lastName ASC");
Détails d'une personne
$pdo = Connect::getPDO();
$person = $pdo->prepare("SELECT
DATE_FORMAT(p.birthday, '%Y/%m/%d') AS birthday,
p.id_person,
p.lastName,
p.firstName,
p.sex,
p.image_url
FROM person p
WHERE p.id_person = :person_id");
$person->execute(["person_id" => $id_person]);
Liste des acteurs ayant plus de 50 ans (âge révolu et non révolu)
$pdo = Connect::getPDO();
$person = $pdo->query("SELECT
DATE_FORMAT(p.birthday, '%Y/%m/%d') AS birthday,
p.id_person,
p.lastName,
p.firstName,
YEAR(CURDATE()) - YEAR(birthday) - (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birthday, '%m%d')) AS age_revolu,
YEAR(CURDATE()) - YEAR(birthday) + IF(DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(birthday, '%m%d'), -1, 0) AS age_non_revolu,
p.sex,
p.image_url
FROM actor a
INNER JOIN person p ON a.id_person = p.id_person
WHERE YEAR(CURDATE()) - YEAR(birthday) > 50");
Acteurs ayant joué dans 3 films ou plus
SELECT lastName, firstName, COUNT(casting.id_movie) AS nombre_films_joues
FROM actor
INNER JOIN person ON actor.id_person = person.id_person
INNER JOIN casting ON actor.id_actor = casting.id_actor
GROUP BY actor.id_actor
HAVING COUNT(casting.id_movie) >= 3
La liste des 4 prochaines sortie en salle
$movies = $pdo->query("SELECT title,
DATE_FORMAT(releaseDate, '%d %m %Y') AS releaseDate,
DATE_FORMAT(SEC_TO_TIME(timeMovie * 60), '%HH%imn') AS timeMovie,
synopsis,
m.image_url,
d.id_director,
p.firstName,
p.lastName,
p.birthday,
p.sex
FROM director d
INNER JOIN movie m ON d.id_director = m.id_director
INNER JOIN person p ON d.id_person = p.id_person
ORDER BY releaseDate DESC
LIMIT 4");
Affiche le JOB de chaque personne (acteur ou réalisateur)
SELECT
COALESCE(d.id_director, a.id_actor) AS id_job,
CASE
WHEN d.id_director IS NOT NULL AND a.id_actor IS NOT NULL THEN 'Réalisateur, Acteur'
WHEN d.id_director IS NOT NULL THEN 'Réalisateur'
WHEN a.id_actor IS NOT NULL THEN 'Acteur'
ELSE 'Unknown'
END AS description
FROM
person p
LEFT JOIN
director d ON d.id_person = p.id_person
LEFT JOIN
actor a ON a.id_person = p.id_person
Affiche le JOB d'une personne par id_person
SELECT
COALESCE(d.id_director, a.id_actor) AS id_job,
CASE
WHEN d.id_director IS NOT NULL AND a.id_actor IS NOT NULL THEN 'Réalisateur, Acteur'
WHEN d.id_director IS NOT NULL THEN 'Réalisateur'
WHEN a.id_actor IS NOT NULL THEN 'Acteur'
ELSE 'undefined'
END AS description
FROM
person p
LEFT JOIN
director d ON d.id_person = p.id_person
LEFT JOIN
actor a ON a.id_person = p.id_person
WHERE
p.id_person = :id_person