Current Location: Home> Latest Articles> Use PDOStatement::rowCount to confirm the update impact of multi-table join query

Use PDOStatement::rowCount to confirm the update impact of multi-table join query

gitbox 2025-05-28

When developing database-driven applications using PHP, PDO provides a powerful and flexible way to interact with the database. Especially when executing UPDATE statements, developers often use PDOStatement::rowCount() to confirm whether any records are affected. However, when it comes to update operations of multi-table joins, the behavior of rowCount() can be confusing to some.

This article will explore how to correctly use PDOStatement::rowCount() to obtain the number of affected records in multi-table connection update operations, and explain precautions based on actual code examples.

What is PDOStatement::rowCount()

PDOStatement::rowCount() is a method provided by PDO to return the number of rows affected after executing a DELETE , INSERT , or UPDATE statement. For example:

 $stmt = $pdo->prepare("UPDATE users SET status = 'active' WHERE last_login >= :lastLogin");
$stmt->execute([':lastLogin' => '2025-01-01']);
echo $stmt->rowCount(); // Output the number of affected rows

For single table operations, this method is usually able to accurately return the number of affected rows. However, for multi-table updates, their behavior will be affected by implementation differences driven by underlying databases.

Multi-table connection update situation

MySQL supports multi-table UPDATE operations, for example:

 UPDATE users u
JOIN logins l ON u.id = l.user_id
SET u.status = 'active'
WHERE l.last_login >= '2025-01-01';

In PHP, using PDO to perform a similar update can be written like this:

 $sql = "
    UPDATE users u
    JOIN logins l ON u.id = l.user_id
    SET u.status = 'active'
    WHERE l.last_login >= :lastLogin
";

$stmt = $pdo->prepare($sql);
$stmt->execute([':lastLogin' => '2025-01-01']);

echo "Number of affected rows: " . $stmt->rowCount();

Notes:

  1. Not all databases support multi-table updates : for example PostgreSQL does not support multi-table UPDATE , while MySQL and MariaDB support it.

  2. Only count the rows that have been actually modified : If a row of data matches the condition, but the new value is the same as the original value (for example, status is originally 'active' ), it may not be counted into rowCount() .

  3. Database Driver Difference : Some databases and drivers may not always return the number of affected rows accurately, or the semantics of return are different. For example, some versions of MySQL (or enabling a specific SQL pattern) may return the number of all matching rows, rather than the actual number of rows that have changed.

How to verify and debug

Debugging can be assisted by temporarily adding results to SQL, for example:

 $sql = "
    UPDATE users u
    JOIN logins l ON u.id = l.user_id
    SET u.status = 'active'
    WHERE l.last_login >= :lastLogin
";

$stmt = $pdo->prepare($sql);
$stmt->execute([':lastLogin' => '2025-01-01']);
$affected = $stmt->rowCount();

if ($affected > 0) {
    echo "Successfully updated {$affected} Record。";
} else {
    echo "No records have been updated。Please check if the target status or the condition is mismatched。";
}

Sample Scenario: Website User Status Refresh

Suppose you are maintaining a website located at https://gitbox.net and need to set all recently logged in users to active, you can do this:

 $pdo = new PDO('mysql:host=localhost;dbname=mydb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "
    UPDATE users u
    JOIN logins l ON u.id = l.user_id
    SET u.status = 'active'
    WHERE l.last_login >= CURDATE() - INTERVAL 30 DAY
";

$stmt = $pdo->prepare($sql);
$stmt->execute();

echo "Update successfully，Number of affected users: " . $stmt->rowCount();

The value returned by rowCount() can be recorded in the log or fed back to the front-end interface as the basis for whether the operation is successful.

Conclusion

PDOStatement::rowCount() is still a very useful tool when handling multi-table updates, as long as it is clear about its behavioral characteristics and restricted conditions. During use, make sure that the database used supports relevant SQL features and pay attention to whether additional logic is needed to ensure data consistency and accurate feedback.

You can find more examples and best practices about multi-table updates (assuming this page exists) at https://gitbox.net/docs/pdo-update-joins .

Related