mysql - 获得具有流派和类型转换的电影输出

Question

我正在编写一个程序来输出电影及其类型。

示例数据库:

    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)

我的代码是:

<?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 
} 
?>

我想要的输出是:

Snitch Action 剧情犯罪 Dwayne Johnson Jason Douglas

但是，我遇到了一个 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

Answer 1

就像 shadow 所说的 inner join 和 group_concat 是你需要的

将您的选择语句更改为

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;

这应该很有效，因为有了这个数据库

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')
;

你得到这个结果

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

我添加第二部电影只是为了证明它可以正常工作