How to join tables in PHP

Question

I'm trying to create a Website about movies. I'd like to display the actors for each movie (in my database, actors.film_id correspond to the films.id). But when I use the code below, I have these 2 errors:

Fatal error: Uncaught Error: Call to a member function fetch() on boolean in C:\wamp64\www\Cine\index.php on line 38

Error: Call to a member function fetch() on boolean in C:\wamp64\www\Cine\index.php on line 38

(There is a comment "line 38" in the code below)

I think that it's not the right method...

<?php
try{
    $bdd = new PDO('mysql:host=localhost;dbname=cinema_db;charset=utf8', 'root', '');
}
catch(Exception $e){
    die('Erreur : ' . $e->getMessage());
}
$reponse = $bdd->query('SELECT * FROM films');

while ($donnees = $reponse->fetch())
{
?>
    <img src="<?php echo $donnees['poster'];?>"/>
    <h1><?php echo $donnees['title'];?></h1>
    <p><?php echo $donnees['synopsis'];?></p><br/>

<?php $reponse2 = $bdd->query('SELECT * FROM actors WHERE actors.film_id = films.id');

 while ($donnees2 = $reponse2->fetch()){ ?>
    <p><strong><?php echo $donnees2['name'];?></strong> : <?php echo $donnees2['character_name'];?></p>//line 38
<?php
}}
$reponse->closeCursor();
?>

Possible duplicate of [My PDO Statement doesn't work](https://stackoverflow.com/questions/32648371/my-pdo-statement-doesnt-work) — Dharman, Sep 22 '19 at 11:29
You should read about the `JOIN` function in SQL, then you don't need to do two queries, just one. — Anuga, Sep 22 '19 at 12:11

score -1 · Answer 1 · answered Sep 22 '19 at 11:28

You need to pass your film ID value to your second query

$bdd->query('SELECT * FROM actors WHERE actors.film_id = films.id');

like this:

$bdd->query('SELECT * FROM actors WHERE actors.film_id =' . $donnees['id']);

Alternatively you can retrieve all films and actors from two tables in one select statement:

select f.id, f.title, a.name from films as f inner join actors as a on f.id=a.film_id;

In that case you don't need the second query. Make sure to display the same film values only once in case you have several actors per film by checking the repetition of film IDs in the PHP code.

How to join tables in PHP

1 Answers1