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

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 : dramamovie_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 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 = ?

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