How can SQL queries be optimized for inserting data into multiple tables in PHP?

When inserting data into multiple tables in PHP using SQL queries, one way to optimize the process is to use transactions. By wrapping the insert queries in a transaction, you can ensure that all inserts are either committed or rolled back as a single unit, improving performance and data consistency. 

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

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

try {
    // Insert data into first table
    $pdo->exec("INSERT INTO table1 (column1, column2) VALUES ('value1', 'value2')");

    // Insert data into second table
    $pdo->exec("INSERT INTO table2 (column1, column2) VALUES ('value3', 'value4')");

    // Commit the transaction
    $pdo->commit();
} catch (Exception $e) {
    // Rollback the transaction if an error occurs
    $pdo->rollback();
    echo "Error: " . $e->getMessage();
}
?>

Keywords

SQL queries optimization inserting data multiple tables PHP

Related Questions