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
(1 ,'action'), (2 ,'comedy'), (3 ,'drama'), (4 ,'mystery');
I used the code below to fetch the data
$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
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
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
$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