Skip to content
Advertisement

how to repeat rows only once in many to many relationship

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
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement