What are some common pitfalls when saving data in multiple tables in PHP?

One common pitfall when saving data in multiple tables in PHP is not properly handling transactions. If an error occurs during the data insertion process, it can leave the database in an inconsistent state. To solve this issue, you can use transactions to ensure that all database operations either succeed or fail together. 

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

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

    // Insert data into table 2
    $query2 = $pdo->prepare("INSERT INTO table2 (column2) VALUES (:value2)");
    $query2->execute(array(':value2' => $value2));

    // Commit the transaction
    $pdo->commit();
} catch (PDOException $e) {
    // Rollback the transaction on error
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

Keywords

SQL injection transaction management foreign key constraints data integrity database normalization

Related Questions