How to select and echo MySQL data (one to many) using PHP

debugcn Published at Dev

John Doo

This is my current code, it works fine with one problem.

$stmt = $conn->prepare("SELECT * FROM tmdb_movies JOIN genres ON genres.genres_tmdb_id=tmdb_movies.tmdb_id 
  ");
     // Then fire it up
     $stmt->execute();
     // Pick up the result as an array
     $result = $stmt->fetchAll();

    // Now you run through this array in many ways, for example
     foreach ($result as $row) {
    print "".$row["movie_title"]." ".$row["genres_name"] ."   ".$row["cast_name"] ."<br/>";
}

The thing is, I am trying to echo Movie Name and it's Genres.

What I want to echo:

The Dark Knight - Action, Crime, Drama

What the code echo:

The Dark Knight - Action

The Dark Knight - Crime

The Dark Knight - Drama

Screenshot of main movie table http://prntscr.com/fokwip

and Screenshot of genres movie table http://prntscr.com/fokwoy

Bdloul

To echo what you want you can change your sql query :

$stmt = $conn->prepare("SELECT *, GROUP_CONCAT(genres.genres_name SEPARATOR ', ') AS genre_concat FROM tmdb_movies JOIN genres ON genres.genres_tmdb_id=tmdb_movies.tmdb_id GROUP BY tmdb_movies.movie_title");

//  sql strict group by

$stmt = $conn->prepare("SELECT tmdb_movies.movie_title, tmdb_movies.cast_name, GROUP_CONCAT(DISTINCT genres.genres_name SEPARATOR ', ') AS genre_concat FROM tmdb_movies JOIN genres ON genres.genres_tmdb_id=tmdb_movies.tmdb_id GROUP BY tmdb_movies.movie_title, tmdb_movies.cast_name ORDER BY tmdb_movies.movie_title");

$stmt->execute();

$result = $stmt->fetchAll();

foreach ($result as $row) {
        print "".$row["movie_title"]." ".$row["genre_concat"] ."   ".$row["cast_name"] ."<br/>";}

GROUP_CONCAT allows to concat all the different genre for each unique movie title.