I’m working on many to many relationship on php
I have 3 tables , movie table which contains movie name and movie id
genre table which contains genre and genre id
and movie_genre table which has movie id and genre id
genre table has these values
JavaScript
x
(1 ,'action'),
(2 ,'comedy'),
(3 ,'drama'),
(4 ,'mystery');
I used the code below to fetch the data
JavaScript
$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"]
}}
but the result would be like
movie_id movie_name genre[1]
movie_id movie_name genre[2]
movie_id movie_name genre[3]
an example
JavaScript
movie_id:55 movie name: titanic genre : action
movie_id:55 movie name: titanic genre : drama
movie_id:55 movie name: titanic genre : romance
the result I want is
JavaScript
movie_id:55 movie name: titanic genre : action drama romance
I did try using limit 1 but the result was showing only the first genre
Advertisement
Answer
Will need to do a loop just for the genres I can’t test this, so this is throwing together just what I imagine will work, but it will be something along these lines, there might be errors in syntax here
JavaScript
$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