How to select one to many relationship data in PHP PDO

Question

I have total 3 tables in PHPMyAdmin:

1. Movies: tmdb_movies

2. Cast: cast. Foreign key is cast_tmdb_id for table tmdb_movies column tmdb_id screenshot

3. Genres: genres. Foreign key is genres_tmdb_id for table tmdb_movies column tmdb_id screenshot

I want to output the following fields:

• movie_title column from tmdb_movies table

• all genres_name rows from genres table

• all cast_name rows from cast table.

What is the smallest command to select and echo data? I am not familiar with the join command in SQL.

This is my current code

<?php 

    $stmt = $conn->prepare("SELECT tmdb_movies.*, GROUP_CONCAT(genres_name) as genres_name
    FROM tmdb_movies JOIN genres ON tmdb_movies.tmdb_id=genres.cast_tmdb_id
    GROUP BY tmdb_movies.cast_tmdb_id, tmdb_movies.tmdb_id, tmdb_movies.movie_title");

         // 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/>";
    }


    ?>

Answer 1

SELECT tmdb_movies.movie_title, GROUP_CONCAT(DISTINCT genres.genres_name SEPARATOR ', ') AS genres_names, GROUP_CONCAT(DISTINCT cast.cast_name SEPARATOR ', ') AS cast_name
                    FROM tmdb_movies
                    JOIN genres ON tmdb_movies.tmbd_id=genres.genres_tmdb_id
                    JOIN cast ON cast.cast_tmdb_id=tmdb_movies.tmbd_id
                    GROUP BY tmdb_movies.movie_title
                    ORDER BY tmdb_movies.movie_title

Explainations :

GROUP_CONCAT => Concat fields from different row in one field

JOIN => you make two table correspond by their key

GROUP BY => group data per those fields

I've done the query I could without actually seeing your tables, so you might want to adapt it if it doesn't work on first try.