Insert variables and arrays in different database tables using PHP and MySQLi

Question

Hi, I'm learning PHP and MySQL and I'm creating a movies website for learning purposes.

I want to insert variables and arrays containing movie info into a database but in different tables.

This is what I have right now:

include('inc/connection.php');
$conn = dbConnect();

// create SQL
$sql = 'INSERT INTO movies (movieid, title, year, runtime, plot)
 VALUES(?, ?, ?, ?, ?)';

// initialize prepared statement
$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) {
 // bind parameters and execute statment
 $stmt->bind_param('isiis', $movieid, $title, $year, $runtime, $plot);
 $stmt->execute();
}

This insert the variables into the movies table.

But I also need to insert (at the same time) the genres of the movie, the actors, languages, etc... they are in arrays and go into different tables (and maybe also in different rows). For example the array containing the genres (Action, Crime, Drama) genres in the genres table, and the actors in the actors table and so on... I then will use many-to-many and one-to-many tables to display the info.

Can someone explain to me how to do this?? I need to connect to the database multiple times?? I need loops? I'm really new to PHP and MySQLi so please try to explain as much as you can.

Thanks.

Answer 1

You don't need to use more than one database connection - in fact that's what you shouldn't do at all, because you couldn't group your inserts into one atomic transaction.

What you should do (abstract) is:

BEGIN;
INSERT INTO Genres...;
INSERT INTO Actors...;
INSERT INTO othertable....;
and so on, for referenced columns/tables.
INSERT INTO Movies....;
COMMIT;

This way you should always have a consistent dataset.

Answer 2

1. You only need one database connection
2. Yes you do need loops
3. Use an auto_increment primary key field in each table, and then use $stmt->insert_id() to extract the value after each insert
4. If you can, use InnoDB on the MySQL server to get transaction support, and then commit relevant updates together, to ensure that your database is consistent if your script drops out mid-way through an update. The more common MyISAM database table format doesn't support transactions.

Answer 3

you are probably going after foreign keys - use one ID from the base table to identify other relations in other table (use movieid in other table to identify what movie the other values belong to etc.)

Answer 4

INSERT supports only one table at a time, so you'll certainly have to execute multiple INSERT statements if you have complex data that goes into multiple tables.

If you've inserted a specific $movieid value to movies then you know what value you need to insert to the rows of the other tables, to satisfy referential integrity (pseudocode follows, not fully functional example):

$movies_sql = 'INSERT INTO movies (movieid, title, year, runtime, plot)
        VALUES(?, ?, ?, ?, ?)';

// execute ($movieid, ...)

$actors_sql = 'INSERT INTO movie_actors (movieid, actorid) 
    VALUES (?, ?)';

foreach ($actorlist as $actorid) {
    // execute ($movieid, $actorid)
}

$lang_sql = 'INSERT INTO movie_languages (movieid, language) 
    VALUES (?, ?)';

foreach ($languagelist as $language) {
    // execute ($movieid, $language)
}

If your movies table uses an AUTO_INCREMENT primary key, then you can get the value generated during the first INSERT either by calling $mysqli->insert_id(), or by using the builtin function LAST_INSERT_ID().

$actors_sql = 'INSERT INTO movie_actors (movieid, actorid) 
    VALUES (LAST_INSERT_ID(), ?)';
foreach ($actorlist as $actorid) {
    // execute ($actorid) -- only one param in this INSERT
}

Note that LAST_INSERT_ID() reports the most recent auto-generated value during your current session, so if you insert to another table that uses an auto primary key, the value changes. But the value reported is stable during your sessions; it does not change if another client session is doing inserts concurrently.