How can PHP developers ensure data consistency and integrity when inserting data from multiple tables into a new table?

To ensure data consistency and integrity when inserting data from multiple tables into a new table, PHP developers can use transactions. By wrapping the insert statements in a transaction, all the inserts will either succeed or fail together, preventing partial data insertion which could lead to inconsistencies. 

<?php

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

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

try {
    // Insert data from multiple tables into a new table
    $pdo->exec("INSERT INTO new_table SELECT * FROM table1");
    $pdo->exec("INSERT INTO new_table SELECT * FROM table2");
    
    // Commit the transaction
    $pdo->commit();
    
    echo "Data inserted successfully!";
} catch (PDOException $e) {
    // Rollback the transaction in case of an error
    $pdo->rollBack();
    
    echo "Error inserting data: " . $e->getMessage();
}

Keywords

transactions foreign keys database normalization data validation SQL queries

Related Questions