Row count with PDO


There are many conflicting statements around. What is the best way to row count using PDO in PHP? Before using PDO, I just simply used mysql_num_rows.

fetchAll is something I won't want because I may sometimes be dealing with large datasets, so not good for my use.

Do you have any suggestions?

5/17/2017 5:32:18 PM

Accepted Answer

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

Not the most elegant way to do it, plus it involves an extra query.

PDO has PDOStatement::rowCount(), which apparently does not work in MySql. What a pain.

From the PDO Doc:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

EDIT: The above code example uses a prepared statement, which is in many cases is probably unnecessary for the purpose of counting rows, so:

$nRows = $pdo->query('select count(*) from blah')->fetchColumn(); 
echo $nRows;
12/30/2013 2:50:25 PM

As I wrote previously in an answer to a similar question, the only reason mysql_num_rows() worked is because it was internally fetching all the rows to give you that information, even if it didn't seem like it to you.

So in PDO, your options are:

  1. Use MySQL's FOUND_ROWS() function.
  2. Use PDO's fetchAll() function to fetch all the rows into an array, then use count() on it.
  3. Do an extra query to SELECT COUNT(*), as karim79 suggested.

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow