What best practices should be followed when updating multiple rows in a database using PHP and MySQL?

When updating multiple rows in a database using PHP and MySQL, it is best practice to use prepared statements to prevent SQL injection attacks and improve performance. Additionally, you should consider using transactions to ensure data consistency and rollback changes if an error occurs during the update process. 

<?php
// Connect to the database
$pdo = new PDO('mysql:host=localhost;dbname=mydatabase', 'username', 'password');

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

// Prepare the update statement
$stmt = $pdo->prepare('UPDATE mytable SET column1 = :value1 WHERE id = :id');

// Loop through the rows to be updated
foreach ($rowsToUpdate as $row) {
    // Bind parameters and execute the statement
    $stmt->bindParam(':value1', $row['new_value']);
    $stmt->bindParam(':id', $row['id']);
    $stmt->execute();
}

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

echo 'Multiple rows updated successfully';
?>

Keywords

PHP MySQL database update multiple rows

Related Questions