Prepared statement selecting and inserting values from multiple tables into a single table

Question

I'm trying to select values from 2 different tables "actors" , "movies" and insert them into a new table "relationships". I'm using PHP to do this and I'm quite confused about how I can use a prepared statement to do this.

$firstNamesForDb = trim($_POST["firstNames"]);  
$lastNameForDb = trim($_POST["lastName"]);
$movieForDb = trim($_POST["movieName"]);  

$selectInsertQuery = "INSERT INTO relationships (actorid, movieid) SELECT actors.actorid, movies.movieid FROM actors, movies WHERE actors.first_names = $firstNamesForDb AND actors.last_name = $lastNameForDb AND movies.title = $movieForDb VALUES(?, ?)";
$statement = $db->prepare($selectInsertQuery);
$statement->bind_param("ii", actorid, movieid);
$statement->execute();

Right now I'm getting the error:

Fatal error: Uncaught Error: Call to a member function bind_param() on bool in D:\Apps\XAMPP\htdocs\iitajax\movies.php:82 Stack trace: #0 {main} thrown in D:\Apps\XAMPP\htdocs\iitajax\movies.php on line 82

With line 82 being $statement->bind_param("ii", actorid, movieid);

How can I resolve this issue?

Answer 1

I think you want:

INSERT INTO relationships (actorid, movieid) 
VALUES (
    (SELECT actorid FROM actors WHERE first_names = ?),
    (SELECT movieid FROM movies WHERE title = ?)
)

Note that this is only safe as long as each of the two subqueries returns just one row - which, presumably, is your intent. Otherwise, you would get error subquery returns more than one row.

A workaround would be to use the insert ... select syntax:

INSERT INTO relationships (actorid, movieid) 
SELECT a.actorid, m.movieid
FROM actors a
CROSS JOIN movies m
WHERE a.first_names = ? AND m.title = ?

Be aware of the implications of this syntax though: if there are several actors with the same first name, or several movies with the same title, then all of them will be inserted in the target table.