English
English
When using PHP's PDO (PHP Data Objects) to operate a database, PDOStatement::rowCount() is a method that is often used to obtain the number of rows that the operation affects. But many developers have found in actual development that its behavior varies significantly between different databases. This article will analyze in-depth the performance of rowCount() in various databases and the key points you should pay attention to when using it.
In PDO, the rowCount() method is usually used to return the number of rows affected by the previous SQL statement. This is usually accurate and valid for UPDATE , DELETE , and INSERT statements; however, for SELECT queries, its behavior varies by database driver.
Example:
<?php
$pdo = new PDO("mysql:host=localhost;dbname=testdb", "user", "pass");
$stmt = $pdo->prepare("UPDATE users SET active = 1 WHERE last_login > :date");
$stmt->execute([':date' => '2024-01-01']);
echo $stmt->rowCount() . " rows updated.";
?>
UPDATE/DELETE/INSERT : rowCount() returns the number of rows affected, which is accurate and valid.
SELECT : By default, rowCount() returns the value of 0 , because MySQL driver does not support returning row counts to SELECT statements.
For all statements, including SELECT , rowCount() can usually return the correct number of rows.
Suitable for most scenarios, but some views or functions can cause behavior instability.
Similar to MySQL, UPDATE , INSERT , DELETE support is good.
0 is usually returned for SELECT statements.
All types of statements are supported to return the number of rows affected, including SELECT , but only if the SQL Server is configured correctly and SET NOCOUNT ON is not used when executing the statement.
Don't rely on SELECT's rowCount()
Unless you clearly know the database driver support, it is recommended to use fetchAll() and then use count() to obtain the number of travel.
$stmt = $pdo->query("SELECT * FROM users");
$rows = $stmt->fetchAll();
echo count($rows) . " rows selected.";
Try to use execute after rowCount
The combination of prepare and execute can ensure that rowCount() returns the exact number of rows in the supported database.
The behavior in a transaction may be different
Some databases do not immediately reflect the number of rows in a transaction until the transaction is committed.
Restricted by drive implementation
PDO itself is a unified interface, and the underlying behavior depends on the driver used (such as pdo_mysql , pdo_pgsql , etc.).
Impact on INSERT IGNORE/ON DUPLICATE KEY
For these statements in MySQL, the return value of rowCount() is not necessarily the number of inserts you expect, it may be 0.
PDOStatement::rowCount() is a convenient function, but its behavior across databases is inconsistent, especially in SELECT queries that need to be used with caution. When building cross-platform or applications that require high compatibility, you should avoid relying on their behavior in SELECT and use alternatives such as fetchAll() + count() .
<?php
$pdo = new PDO("mysql:host=localhost;dbname=demo", "user", "pass");
$stmt = $pdo->prepare("SELECT * FROM articles WHERE status = :status");
$stmt->execute([':status' => 'published']);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "total " . count($results) . " Record。\n";
foreach ($results as $row) {
echo $row['title'] . " - Link: https://gitbox.net/article/" . $row['id'] . "\n";
}
?>
Through the above methods, query results can be processed more securely and cross-platform, without worrying about the troubles caused by database differences to rowCount() .
