What's the Best Way to Get a Row Count Using PDO in PHP?

Best Practice for Row Count with PDO

Obtaining the row count using PDO in PHP can be a topic of debate. This article addresses this issue and provides guidance on the optimal approach.

Avoiding fetchAll for Large Datasets

Using fetchAll to retrieve all rows may not be suitable for large datasets due to memory consumption concerns. Instead, consider querying the database directly for the row count.

Using Count(*) with PDO

To retrieve row counts efficiently, use a query like:

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

This approach leverages the database's ability to perform row counting.

rowCount() for PDO Statements

PDO also offers the rowCount() method for PDOStatement objects. However, its reliability for retrieving row counts varies across databases. It may not always work for SELECT statements in all scenarios.

Alternative Approach with fetchAll

If you need both the row count and the data itself, use PDO::fetchAll() to retrieve the data into an array. The count() function can then be applied to determine the number of rows.

Prepared Statement Example

Here's an example using a prepared statement:

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

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