What's the Most Efficient Way to Count Rows Using PDO in PHP?

Counting Rows with PDO

Background

Determining the optimal method for retrieving row counts using PDO in PHP remains a topic of debate. This article explores alternative approaches, providing guidance for programmers seeking an efficient solution.

Database-centric Approach

For scenarios where accessing the actual data is not necessary, a database-centric approach is recommended. With this method, the database performs the row count, returning only the numerical value. The following code exemplifies this approach:

$sql = "SELECT count(*) FROM `table` WHERE foo = ?";
$result = $con->prepare($sql);
$result->execute([$bar]);
$number_of_rows = $result->fetchColumn();

Alternatively, PDStatement::rowCount() can be used to obtain the row count when the data itself is also retrieved. This method works effectively with MySQL's buffered queries (enabled by default).

PDO-centric Approach

However, relying solely on PDStatement::rowCount() is not guaranteed for other databases. According to the PDO documentation:

"For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement."

To overcome this limitation, the documentation suggests issuing a SELECT COUNT(*) statement using PDO::query() and then using PDStatement::fetchColumn() to retrieve the desired value.

Finally, for simple queries without variable placeholders, PDO::query() can be used directly:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn();
echo $nRows;

The above is the detailed content of What's the Most Efficient Way to Count Rows Using PDO in PHP?. For more information, please follow other related articles on the PHP Chinese website!