Row count with PDO

Question

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?

Answer 1

Before using PDO I just simply used mysql_num_rows().

You shouldn't have been using it in the first place.

mysql_num_rows(), as well as PDOStatement::rowCount() implies that you already selected your data. In this case there are only two possible use cases for such a function:

1. You were selecting your data only to get the count.
2. You want to know whether your query returned any rows.

And the former one should never be used at all. One should never select rows to count them, as your server indeed may choke due to large dataset returned.

Besides, selecting rows only to count them simply makes no sense. A count(*) query have to be run instead, with only one row returned.

The second use case is less disastrous but rather pointless: in case you need to know whether your query returned any data, you always have the data itself!

Say, if you are selecting only one row, then just fetch it, and check the result:

$stmt->execute();
$row = $stmt->fetch();
if ($row) { // here! as simple as that
}

In case you need to get many rows, then you can use fetchAll().

fetchAll() is something I won't want as I may sometimes be dealing with large datasets

Note that in a web application you should never select a huge amount of rows. Only rows that will be actually used on a web page should be selected. For which purpose a LIMIT or similar clause in SQL have to be used. And for such a moderate amount of data it's all right to use fetchAll()

In such a rare case when you need to select a real huge amount of rows (in a console application for example), to reduce the amount of memory used, you have to use an unbuffered query, but in this case rowCount() won't be available anyway, thus there is no use for this function as well.

So you see, there is no use case neither for rowCount() nor for an extra query to substitute it, as suggested in the accepted answer.

Answer 2

I ended up using this:

$result = $db->query($query)->fetchAll();

if (count($result) > 0) {
    foreach ($result as $row) {
        echo $row['blah'] . '<br />';
    }
} else {
    echo "<p>Nothing matched your query.</p>";
}

Answer 3

For straight queries where I want a specific row, and want to know if it was found, I use something like:

function fetchSpecificRow(&$myRecord) {
    $myRecord = array();
    $myQuery = "some sql...";
    $stmt = $this->prepare($myQuery);
    $stmt->execute(array($parm1, $parm2, ...));
    if ($myRecord = $stmt->fetch(PDO::FETCH_ASSOC)) return 0;
    return $myErrNum;
}