Why PDOStatement::rowCount is not supported in some database drivers

When using PHP's PDO (PHP Data Objects) extension to operate databases, developers often rely on the PDOStatement::rowCount() method to get the number of affected rows. However, many people will find in actual development that this method does not seem to return the correct result in some database drivers, and even returns 0 directly when executing SELECT queries. Why is this? This article will analyze this issue in depth.

1. The function and usage of rowCount

PDOStatement::rowCount() is a method provided by PDO to return the number of rows affected by the last executed SQL statement. It is most commonly used in two types of SQL operations:

• UPDATE , DELETE , INSERT , etc. will modify data operations

• SELECT query (some drivers support)

Basic usage examples:

 $pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$stmt = $pdo->prepare("DELETE FROM users WHERE status = :status");
$stmt->execute([':status' => 'inactive']);
echo $stmt->rowCount(); // Output the number of affected rows

2. Why does rowCount return 0 when SELECT query?

According to the official documentation, rowCount() does not support returning the number of affected rows to SELECT queries in some database drivers. The most typical example is MySQL. When using MySQL's PDO driver, after a SELECT query is executed, calling rowCount() will usually return 0 instead of the expected number of results.

The reason is : for SELECT queries, MySQL's client library does not inform the number of affected rows unless it is used with mysqlnd driver (enabled by default in PHP 5.3+). Even so, you cannot rely entirely on rowCount() .

If you really need to get the number of SELECT query results, a safer way is to use fetchAll() and then count() to count:

 $stmt = $pdo->query("SELECT * FROM users WHERE status = 'active'");
$results = $stmt->fetchAll();
echo count($results);

While this consumes more memory, it ensures accuracy.

3. Different database driver behaviors

The following are the support situations of rowCount() in several common databases:

Therefore, when you use rowCount() , you should make a judgment based on the target database.

4. Recommended practices and precautions

1. Avoid relying on rowCount() to determine whether the SELECT query has results. It is best to use fetch() or fetchAll() to determine whether there is a result based on whether the data is returned.

2. In DELETE/UPDATE/INSERT, rowCount() is usually reliable. As long as the query is successful, it can usually return the number of affected rows.

3. Make sure to use rowCount() to get the SELECT quantity when mysqlnd (MySQL Native Driver) is enabled.

You can use phpinfo() or run the following code to see if you use mysqlnd:

 echo phpinfo();

or:

 echo (extension_loaded('mysqlnd')) ? 'mysqlnd loaded' : 'mysqlnd not loaded';

5. Summary

PDOStatement::rowCount() , although a convenient method, its behavior depends on the degree of support of the underlying database driver. In order to ensure cross-database compatibility of the code, it is recommended to avoid rowCount() when using SELECT queries, and instead use a more stable and reliable way to judge the number of data rows.

For more database related tutorials, please visit: https://gitbox.net/dev/database-tutorials