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?
When you need only the number of rows, but not the data itself, such a function shouldn't be used anyway. Instead, ask the database to do the count, with a code like this:
$sql = "SELECT count(*) FROM `table` WHERE foo = ?";
$result = $con->prepare($sql);
$result->execute([$bar]);
$number_of_rows = $result->fetchColumn();
For getting the number of rows along with the data retrieved, PDO has PDOStatement::rowCount(), which apparently does work in MySql with buffered queries (enabled by default).
But it's not guaranteed for other drivers. 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.
But in this case you can use the data itself. Assuming you are selecting a reasonable amount of data, it can be fetched into array using PDO::fetchAll(), and then count() will give you the number of rows.
EDIT: The above code example uses a prepared statement, but if a query doesn't use any variables, one can use query() function instead:
$nRows = $pdo->query('select count(*) from blah')->fetchColumn();
echo $nRows;