Row count with PDO

$sql = "SELECT count(*) FROM `table` WHERE foo = ?"; 
$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;

Leave a Comment