Get Number of Affected Rows in PHP PDO

When modifying database records using PHP Data Objects (PDO), it is crucial to verify how many rows were actually altered. This article explains how to retrieve the number of affected rows after executing an UPDATE or DELETE query using the built-in PDO rowCount() method, accompanied by clear, practical code examples.

Using PDOStatement::rowCount()

In PHP PDO, the PDOStatement::rowCount() method returns the number of rows affected by the last SQL statement (such as DELETE, INSERT, or UPDATE) executed by the corresponding PDOStatement object.

Example: Affected Rows in an UPDATE Query

When you run an UPDATE statement, rowCount() returns the number of rows that were actually modified. Note that if you update a row with the exact same values it already has, some databases (like MySQL) will return 0 because no data actually changed.

<?php
// Assuming $pdo is your active PDO connection

$sql = "UPDATE users SET status = :status WHERE role = :role";
$stmt = $pdo->prepare($sql);

// Execute the query
$stmt->execute([
    ':status' => 'active',
    ':role' => 'editor'
]);

// Retrieve the number of affected rows
$affectedRows = $stmt->rowCount();

echo "Number of updated rows: " . $affectedRows;
?>

Example: Affected Rows in a DELETE Query

When executing a DELETE statement, rowCount() returns the total number of rows that were successfully removed from the database.

<?php
// Assuming $pdo is your active PDO connection

$sql = "DELETE FROM sessions WHERE last_activity < :expiry";
$stmt = $pdo->prepare($sql);

// Execute the query
$stmt->execute([
    ':expiry' => time() - 3600 // 1 hour ago
]);

// Retrieve the number of affected rows
$deletedRows = $stmt->rowCount();

echo "Number of deleted rows: " . $deletedRows;
?>

Key Considerations