There are many conflicting statements around. What is the best way to get the 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?
Answer
$sql = "SELECT count(*) FROM `table` WHERE foo = ?";
$result = $con->prepare($sql);
$result->execute([$bar]);
$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;