Current Location: Home> Latest Articles> How to use PDOStatement::rowCount to get the actual number of rows affected by a UPDATE query

How to use PDOStatement::rowCount to get the actual number of rows affected by a UPDATE query

gitbox 2025-05-28

PDOStatement::rowCount() is a common but often misunderstood method when operating a database using PDO in PHP. Especially after executing UPDATE or DELETE statements, we often use it to determine whether the data has been "updated" or "deleted". But do you know the details of the behavior behind it?

Basic usage

 $pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$sql = "UPDATE users SET status = 'active' WHERE last_login > :date";
$stmt = $pdo->prepare($sql);
$stmt->execute([':date' => '2024-01-01']);

echo "Updated " . $stmt->rowCount() . " Line records";

This code executes a UPDATE statement, using rowCount() to get the actual number of rows affected.

Things to note

  1. Only valid for DML : rowCount() can correctly return the number of affected rows after INSERT , UPDATE and DELETE . But for SELECT , the MySQL driver usually returns 0.

  2. Related to data changes : rowCount() may return 0 if the target row data does not really change (i.e. the new value is the same as the old value). This is a problem with MySQL behavior, not PDO.

  3. Not equal to "match number of rows" : it counts "number of rows that actually change", not "number of rows that meet the WHERE conditions".

Example: Detect whether user information is updated

 $sql = "UPDATE users SET email = :email WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':email' => '[email protected]', ':id' => 1]);

if ($stmt->rowCount() > 0) {
    echo "User information has been updated";
} else {
    echo "No update，It may be that the data has not changed or ID Does not exist";
}

Mastering the behavior of rowCount() can make your business logic more rigorous and avoid misjudging whether the database operation takes effect. For more examples, please see https://gitbox.net/docs/pdo-update .

Related