English
English
In PHP development, using PDO for database operations is one of the recommended ways. PDOStatement::rowCount() is a method provided by PDO to return the number of rows affected by the previous SQL statement. However, its behavior varies slightly among different SQL statement types, especially when executing SELECT , UPDATE , DELETE and UNION queries, it is necessary to understand the mechanism behind it.
This article will introduce the basic usage of PDOStatement::rowCount() , and explore in-depth how it performs when processing UNION merge query results, as well as the method of correctly counting the number of rows of merge result.
When executing DELETE , INSERT , or UPDATE , rowCount() can return the number of affected rows. Examples are as follows:
<?php
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');
$stmt = $pdo->prepare("UPDATE users SET status = 'active' WHERE last_login > NOW() - INTERVAL 30 DAY");
$stmt->execute();
echo "Updated " . $stmt->rowCount() . " Line records。";
?>
In the above code, rowCount() returns the number of rows that have been updated.
But when executing a SELECT query, rowCount() does not always work as expected. For most databases (especially MySQL), under the default PDO::MYSQL_ATTR_USE_BUFFERED_QUERY setting, rowCount() does not return the number of result rows in the SELECT query, but returns 0.
If you are using a SELECT query and want to get the number of rows of the result, the recommended method is to get all the results and calculate it through count() , for example:
<?php
$stmt = $pdo->query("SELECT * FROM articles WHERE category = 'php'");
$results = $stmt->fetchAll();
echo "There are a total of " . count($results) . " Record。";
?>
UNION is the syntax in SQL for merging two or more SELECT query results. By default, it removes duplicate rows ( UNION ALL is used if you want to keep duplicate rows). So, how to get the number of rows of UNION query results through PDO?
Error demonstration:
<?php
$stmt = $pdo->query("SELECT name FROM authors UNION SELECT name FROM editors");
echo $stmt->rowCount(); // In most MySQL In case it will return 0
?>
This is because rowCount() does not support returning result row count for SELECT queries in MySQL.
<?php
$stmt = $pdo->query("
SELECT name FROM authors
UNION
SELECT name FROM editors
");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "合并查询后There are a total of " . count($rows) . " Record。";
?>
This method obtains all results through fetchAll() and then counts the number of rows with count() . It can not only correctly obtain the number of merged results of UNION query, but also be suitable for other complex SELECT queries.
When you use UNION ALL (no deduplication), you can also use the same method to get all records:
<?php
$stmt = $pdo->query("
SELECT name FROM authors
UNION ALL
SELECT name FROM editors
");
$rows = $stmt->fetchAll();
echo "UNION ALL The number of rows in the query result is：" . count($rows);
?>
If your UNION query results are many and need to be paged, you can use the entire UNION query as a subquery to handle pagination:
<?php
$page = 1;
$limit = 10;
$offset = ($page - 1) * $limit;
$sql = "
SELECT * FROM (
SELECT name FROM authors
UNION
SELECT name FROM editors
) AS combined
LIMIT :limit OFFSET :offset
";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach ($results as $row) {
echo $row['name'] . "<br>";
}
?>
PDOStatement::rowCount() usually does not return valid results for SELECT statements (especially in MySQL).
Using fetchAll() + count() is a more general and reliable way to get the number of query rows.
For UNION or UNION ALL queries, it is also recommended to use fetchAll() to count the array length to get the total number of rows.
If you need to paging the merge query, you can process the UNION result as a subquery.
I hope this article can help you understand the usage scenario of rowCount() more clearly and avoid placing traps when counting the number of rows in UNION query results. These tips are especially important if you are building an API or data analytics platform (such as in a project on gitbox.net ).
Do you need me to write the paging wrapper function for you too?
