Current Location: Home> Latest Articles> Check the impact of DELETE operation using PDOStatement::rowCount

Check the impact of DELETE operation using PDOStatement::rowCount

gitbox 2025-05-28

In PHP, deleting data is a common requirement when we use PDO (PHP Data Objects) for database operations. To ensure that the DELETE operation is successful and obtain detailed information about the operation results, we can use the PDOStatement::rowCount function to check the impact of the deletion operation on the database.

PDO::prepare and DELETE operations

When executing DELETE operations, we usually need to use PDO::prepare to prepare SQL statements and execute them through PDOStatement:: execute. The PDOStatement::rowCount function can tell us how many rows are actually deleted after executing the DELETE statement.

 <?php
// Database connection information
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'root';
$password = '';

// create PDO Example
try {
    $pdo = new PDO($dsn, $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // DELETE SQL Statement
    $sql = "DELETE FROM users WHERE last_login < :date";
    $stmt = $pdo->prepare($sql);

    // Bind parameters
    $date = '2023-01-01';
    $stmt->bindParam(':date', $date);

    // Perform a delete operation
    $stmt->execute();

    // examine DELETE Impact of operation
    $rowCount = $stmt->rowCount();
    echo "Deleted successfully {$rowCount} Record。";
} catch (PDOException $e) {
    echo "mistake: " . $e->getMessage();
}
?>

The role of rowCount function

After executing the DELETE statement, the rowCount() method returns an integer representing the number of affected rows. In the above example, if there are 5 records that meet the deletion criteria, rowCount() will return 5, indicating that these 5 records have been successfully deleted.

It should be noted that rowCount() may have different behaviors when executing DELETE statements under certain database drivers (such as MySQL). For example, if the WHERE condition of the DELETE statement does not match any records, rowCount() will return 0, indicating that no records have been deleted.

Things to note

  1. Transaction Management : When performing DELETE operations, it is recommended to put them in the transaction so that the operation can be rolled back when an error occurs to ensure database consistency.

  2. Verification of deletion results : Checking the actual number of rows deleted through rowCount() can help developers verify whether the deletion operation is successfully executed. Especially when it comes to conditional deletion, rowCount() can effectively help us confirm whether the deleted records meet expectations.

  3. Performance considerations : If a DELETE operation involves a large amount of data, it is recommended to use it with caution. It is best to use LIMIT statements to delete it in batches to avoid the performance impact of deleting large amounts of data at one time.

The above is how to use the PDOStatement::rowCount function to check the impact of DELETE operations on the database. Through this method, we can easily monitor the effect of deletion operations and ensure the reliability and accuracy of the program when interacting with the database.

Related