CODEWITHSUNDEEP
mysqlsql
Ravi Jaiswal
Ravi Jaiswal

Reputation: 25

To get output of movie with its genre and cast

I am writing a program to get an output of movies with their genres.

Sample database:

    Movie        movie_id , title (1, snitch)
    genre        genre_id , genre (1, Action)(2, Animation)(3, Drama)(4, Crime )
    movie_genre  movie_id, genre_id (1,1)(1,3)(1,4)
    people       people_id, people (1, Dwayne Johnson)(2, Jason Douglas)
    movie_people movie_id, people_id (1,1) (1, 2)

My code is:

<?php 
$stmt = $pdo->query("
    SELECT * FROM movie, genre, movie_genre 
    WHERE 
        movie.movie_id = movie_genre.movie_id 
        AND genre.genre_id = movie_genre.genre_id 
        AND movie.name='snitch'
    UNION SELECT title, people FROM movie, people, movie_people 
    WHERE movie.movie_id = movie_people.movie_id                 
        AND people.people_id = movie_people.people_id 
        AND movie.name='snitch'
    ");

$oldtitle = $title = '';

while ($row = $stmt->fetch()){
    $title= $row['title'];

    if ( $oldtitle == $title ) {
        $title = '';
    } else {
        $oldtitle = $title;
    }

    $genre = $row['genre'];
    $people = $row['people'];
    ?>
        <h1> <?php echo $title ; ?> </h1>
        <h1> <?php echo $genre ; ?> </h1>
        <h1> <?php echo $people ; ?> </h1>
    <?php 
} 
?>

The output I want is:

Snitch Action Drama Crime Dwayne Johnson Jason Douglas

But, I am getting a fatal error:

Uncaught PDOException: 
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns in C:\xampp\htdocs\new\single-movie-test.php:3 
Stack trace: #0 C:\xampp\htdocs\new\single-movie-test.php(3): 
PDO->query('\r\nSELECT * FROM...') #1 {main} thrown in C:\xampp\htdocs\new\single-movie-test.php on line 3

Upvotes: 1

Views: 119

Answers (1)

nbk
nbk

Reputation: 49373

Like shadow said inner join and group_concat is what you need

Change your select statement to

SELECT 
 mo.title title
 , GROUP_CONCAT(DISTINCT p.people) people
  , GROUP_CONCAT(DISTINCT ge.genre) genre
FROM 
  Movie mo
  inner join  movie_people mp 
    on mo.movie_id = mp.movie_id
  inner join people p 
    on mp.people_id = p.people_id
  inner join movie_genre mg
    on mg.movie_id = mp.movie_id
  inner join genre ge
    on ge.genre_id = mg.genre_id
 GrOUP By mo.movie_id,mo.title;

This should work like a charm, because with this database

CREATE TABLE movie_people
    (`movie_id` int, `people_id` int)
;

INSERT INTO movie_people
    (`movie_id`, `people_id`)
VALUES
    (1, 1),
    (1, 2),
    (2, 1),
    (2, 2)

;


CREATE TABLE `people`
    (`people_id` int, `people` varchar(14))
;

INSERT INTO `people`
    (`people_id`, `people`)
VALUES
    (1, 'Dwayne Johnson'),
    (2, 'Jason Douglas')
;


CREATE TABLE `movie_genre`
    (`movie_id` int, `genre_id` int)
;

INSERT INTO `movie_genre`
    (`movie_id`, `genre_id`)
VALUES
    (1, 1),
    (1, 3),
    (1, 4),
    (2, 1),
    (2, 2),
    (2, 4)
;


CREATE TABLE `genre`
    (`genre_id` int, `genre` varchar(9))
;

INSERT INTO `genre`
    (`genre_id`, `genre`)
VALUES
    (1, 'Action'),
    (2, 'Animation'),
    (3, 'Drama'),
    (4, 'Crime')
;


CREATE TABLE Movie
    (`movie_id` int, `title` varchar(6))
;

INSERT INTO Movie
    (`movie_id`, `title`)
VALUES
    (1, 'snitch'),
    (2, 'snitch')
;

You get this result

title   people                          genre
snitch  Jason Douglas,Dwayne Johnson    Action,Crime,Drama
snitch  Dwayne Johnson,Jason Douglas    Animation,Crime,Action

I added the second movie only to demonstrate, that it works properly

Upvotes: 1

Related Questions