What are some best practices for handling database updates in PHP scripts to avoid errors and ensure data consistency?

When handling database updates in PHP scripts, it is important to use prepared statements to prevent SQL injection attacks and ensure data integrity. Additionally, transactions should be used to group multiple database operations into a single unit of work, ensuring that either all updates are applied successfully or none are. Error handling should also be implemented to catch and handle any database errors that may occur during the update process. 

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

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

try {
    // Prepare the update statement
    $stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");

    // Bind parameters
    $stmt->bindParam(':email', $email);
    $stmt->bindParam(':id', $id);

    // Set values for email and id
    $email = 'newemail@example.com';
    $id = 1;

    // Execute the update statement
    $stmt->execute();

    // Commit the transaction
    $pdo->commit();

    echo "Update successful!";
} catch (PDOException $e) {
    // Roll back the transaction if an error occurs
    $pdo->rollBack();
    echo "Update failed: " . $e->getMessage();
}
?>

Keywords

SQL injection PDO transactions error handling data validation

Related Questions