English
English
When using PDO extensions in PHP, the PDOStatement::rowCount() method is used to obtain the number of rows affected after executing the SQL query statement. This method is often used to check whether the data is operated correctly after adding, deleting, modifying and other operations. However, the behavior of the rowCount() method may differ in different database drivers, and even on the same database, different types of queries will lead to different results.
The behavior of PDOStatement::rowCount() depends on the database driver used and the type of SQL executed. Let's take a look at how it performs under some common database drivers.
In a MySQL database, when DML (data operation language) statements such as DELETE , INSERT , or UPDATE are executed, rowCount() returns the actual number of rows affected. But if the SELECT query is executed, rowCount() will return 0 by default unless you use PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to enable cache query.
For PostgreSQL, rowCount() usually returns the number of rows actually affected by the INSERT , DELETE , or UPDATE operations. When executing a SELECT query, rowCount() returns the number of affected rows, which is different from MySQL's behavior.
SQLite is similar to MySQL. When executing a DML statement, rowCount() returns the number of affected rows. However, the return value of the SELECT query depends on the query method used. By default, SQLite does not guarantee that the number of rows returned is correct, so rowCount() is not necessarily useful in SELECT queries.
For SQL Server databases, rowCount() behavior is consistent with MySQL. The actual number of affected rows can only be returned after a DML operation. For SELECT queries, rowCount() results are not reliable.
Due to the differences in rowCount() on different database drivers, how to correctly handle the return value and ensure the robustness of the code? Here are some suggestions:
If you need to get the number of rows of the result of a SELECT query and want to make sure it works properly in all database drivers, it is recommended to use PDO::FETCH_ASSOC or PDO::FETCH_NUM to get the query results instead of relying on rowCount() .
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "Queryed " . count($results) . " Record";
For INSERT , UPDATE , or DELETE statements, it is OK to use rowCount() . But be aware that in some database drivers, rowCount() may not return the number of rows you expect to affect. For example, in MySQL, if you execute a UPDATE statement but no records are modified, rowCount() may return 0, which does not mean that the SQL statement is not executed. So in this case, you need to combine other methods such as lastInsertId() or affected_rows to ensure the data operation is successful.
$sql = "UPDATE users SET name = :name WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':name' => $newName, ':id' => $userId]);
if ($stmt->rowCount() > 0) {
echo "Update successfully";
} else {
echo "No records have been updated";
}
In order to avoid inconsistencies caused by different database drivers, excessive dependence on rowCount() results can be avoided when handling database operations. For SELECT queries, the number of rows of the result set is directly obtained; for DML operations, consider combining transactions and error handling to ensure the operation is successful.
Suppose you store the URL of an API in the database, and this URL may change in different environments. You can replace the domain name part in the URL with a function, for example, the following simple example:
function updateUrlDomain($url) {
$parsedUrl = parse_url($url);
$parsedUrl['host'] = 'gitbox.net'; // Replace the domain name with gitbox.net
return http_build_url($parsedUrl);
}
$newUrl = updateUrlDomain('https://oldurl.com/path/to/resource');
echo $newUrl; // The output is replaced URL
In this example, we use the parse_url() function to parse the URL, then replace its domain name part, and finally build a new URL using http_build_url() .
Although PDOStatement::rowCount() behaves differently in different database drivers, by understanding the characteristics of each driver and using rowCount() reasonably, we can avoid the potential problems it brings. Most importantly, when processing SQL query results, different strategies must be adopted according to the characteristics of the specific database driver, so that more compatible and stable code can be written.
