What is the best practice for inserting data into multiple MySQL tables with auto-increment values in PHP?

When inserting data into multiple MySQL tables with auto-increment values in PHP, it is important to use transactions to ensure data consistency. By using transactions, you can make sure that either all inserts succeed or none of them do. Additionally, you should retrieve the auto-incremented ID values after inserting data into each table to use in subsequent inserts. 

<?php
// Establish a connection to the MySQL database
$pdo = new PDO("mysql:host=localhost;dbname=database", "username", "password");

// Begin a transaction
$pdo->beginTransaction();

try {
    // Insert data into the first table
    $pdo->exec("INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2')");
    
    // Retrieve the auto-incremented ID from the first insert
    $id1 = $pdo->lastInsertId();
    
    // Insert data into the second table using the retrieved ID
    $pdo->exec("INSERT INTO table2 (column1, column2) VALUES ($id1, 'value3')");
    
    // Commit the transaction if all inserts were successful
    $pdo->commit();
    
} catch (Exception $e) {
    // Rollback the transaction if an error occurs
    $pdo->rollback();
    
    echo "Error: " . $e->getMessage();
}
?>

Keywords

PHP MySQL auto-increment multiple tables data insertion

Related Questions