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
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?
$sql = "SELECT count(*) FROM `table` WHERE foo = bar"; $result = $con->prepare($sql); $result->execute(); $number_of_rows = $result->fetchColumn();
Not the most elegant way to do it, plus it involves an extra query.
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;
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: