我在 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 但結果只顯示第一種類型
2 回答
撒科打諢
TA貢獻1934條經驗 獲得超2個贊
您可以使用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
慕神8447489
TA貢獻1780條經驗 獲得超1個贊
將需要為我無法測試的流派做一個循環，所以這只是將我想象的工作放在一起，但它會沿著這些方向進行，這里可能存在語法錯誤
$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
- 2 回答
- 0 關注
- 124 瀏覽
添加回答
舉報
0/150
提交
取消