What are some best practices for inserting data into multiple tables in PHP?

When inserting data into multiple tables in PHP, it is important to ensure data integrity by using transactions. This allows you to either insert data into all tables successfully or rollback the changes if an error occurs. Additionally, you should use prepared statements to prevent SQL injection attacks and sanitize user input. 

<?php

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

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

try {
    // Insert data into table 1
    $stmt1 = $pdo->prepare("INSERT INTO table1 (column1, column2) VALUES (:value1, :value2)");
    $stmt1->execute(array(':value1' => 'data1', ':value2' => 'data2'));

    // Insert data into table 2
    $stmt2 = $pdo->prepare("INSERT INTO table2 (column1, column2) VALUES (:value1, :value2)");
    $stmt2->execute(array(':value1' => 'data3', ':value2' => 'data4'));

    // Commit transaction
    $pdo->commit();
    
    echo "Data inserted successfully into both tables.";
} catch (Exception $e) {
    // Rollback transaction if an error occurs
    $pdo->rollBack();
    
    echo "An error occurred: " . $e->getMessage();
}

Keywords

SQL transactions prepared statements PDO database relations

Related Questions