如何在多对多关系中只重复一次行

Question

我在 php 上处理多对多关系我有 3 个表，包含电影名称的电影表和包含流派和流派 ID 的电影 ID 流派表和包含电影 ID 和流派 ID 流派表的 movie_genre 表具有这些

值

(1 ,'action'),

(2 ,'comedy'),

(3 ,'drama'),

(4 ,'mystery');

我使用下面的代码来获取数据

 $query = " SELECT movie_name,movie_id,genre FROM movies

    JOIN movie_genre ON movies.movie_id = movie_genre.movie_id

    JOIN genre ON movie_genre.genre_id = genre.genre_id

    WHERE movie.movie_id = ?";

    $stmt = mysqli_prepare($conn, $query);

    $stmt->bind_param('i', $id);

    $stmt->execute();

    $result = $stmt->get_result();

    if(mysqli_num_rows($result) > 0 ){

    while ($row = mysqli_fetch_assoc($result)) {

    echo ' movie_id: '.$row["movie_id"].' movie name:'.$row["movie_name"].'

    Genres:' .$row["genre"]

        }}

但结果会像

movie_id movie_name genre[1]

movie_id movie_name genre[2]

movie_id movie_name genre[3]

一个例子

movie_id:55 movie name: titanic genre : action 

movie_id:55 movie name: titanic genre : drama

movie_id:55 movie name: titanic genre : romance

我想要的结果是

movie_id:55 movie name: titanic genre : action drama romance

我确实尝试使用 limit 1 但结果只显示第一种类型

Answer 1

您可以使用GROUP_CONCAT和GROUP BY：SELECT movie_name,movie_id, GROUP_CONCAT(genre SEPARATOR ' ') AS genreFROM moviesJOIN movie_genre ON movies.movie_id = movie_genre.movie_idJOIN genre ON movie_genre.genre_id = genre.genre_idWHERE movie.movie_id = ?GROUP BY movie_name,movie_id

Answer 2

将需要为我无法测试的流派做一个循环，所以这只是将我想象的工作放在一起，但它会沿着这些方向进行，这里可能存在语法错误$query = " SELECT movie_name,movie_id,genre FROM movies    JOIN movie_genre ON movies.movie_id = movie_genre.movie_id    JOIN genre ON movie_genre.genre_id = genre.genre_id    WHERE movie.movie_id = ?";        $stmt = mysqli_prepare($conn, $query);    $stmt->bind_param('i', $id);    $stmt->execute();    $result = $stmt->get_result();    if(mysqli_num_rows($result) > 0 ){    // if it's greater than zero, great, we have at least 1 movie.    // but if it's greater than 1, then we know we have more than 1 genre    while ($row = mysqli_fetch_assoc($result)) {    echo 'movie_id:'.$row["movie_id"].'movie name:'.$row["movie_name"];    break; // so it only runs through this loop once, instead of using limit    }//end while    if(mysqli_num_rows($result) > 1 ){    // more than 1 genre so loop and print out just genres    while ($row = mysqli_fetch_assoc($result)) {          echo $row["genre"];        }//end while    }//end if more than 1 genre    }//end if