我正在php上研究多对多关系，我有3个表，包括电影名称和电影id的movie表genre表包括类型和类型id的movie_genre表包括电影id和类型id的movie_genre表
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
我确实尝试过使用Limit1，但结果显示的只是第一个流派
我需要做一个循环，只是为流派，我不能测试，所以这是把我想象的工作，但它将是沿着这些路线的东西，这里可能有语法错误
$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
