How can PHP developers ensure data integrity when dealing with relational database tables in MySQL?

To ensure data integrity when dealing with relational database tables in MySQL, PHP developers can use transactions to group multiple SQL queries into a single unit of work. This helps to maintain consistency and prevent data corruption by ensuring that either all queries are successfully executed or none of them are. 

<?php

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

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

try {
    // Perform multiple SQL queries within the transaction
    $pdo->exec("INSERT INTO table1 (column1) VALUES ('value1')");
    $pdo->exec("UPDATE table2 SET column2 = 'new_value' WHERE id = 1");

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

Keywords

PHP data integrity relational database MySQL transactions

Related Questions