PDO's rowCount() Not Working on PHP 5.2.6+

Question

So I've been using PHP's PDO as my database goto class for a while now, unfortunately today after debugging for a while on a client's server (with PHP 5.2.6 installed) I discover this. We tried upgrading to the newest stable release (5.2.9) but the problem persists.

Has anyone found a workaround?

Answer 1

You could do it through MySQL itself by using the FOUND_ROWS() function, not sure if there are any better alternatives.

Edit: It seems as though the only reason this was possible with MySQL is because it internally fetched all the result rows and buffered them, to be able to give you this information. See mysql_unbuffered_query(). If you use that function instead of mysql_query(), the mysql_num_rows() function will not work. If you really need to know the number of rows while using PDO, you can fetch all of the rows from PDO into an array and then use count().

Answer 2

Notice you should not use rowCount() for SELECT queries since PDOStatement->rowCount() returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

Instead you might use fetchAll() into an array and then count().

Answer 3

The only way that databases can give you a count for the number of rows is by running the query and counting the number of rows.

The mysql extension uses a buffered query mode by default that causes the entire dataset to be fetched into memory before control is returned to PHP and it can start to process the rows.

PDO uses an unbuffered mode by default which leads to lower latency in the page load time and is generally what you want. The trade off is that rowCount() won't return valid information until the entire dataset has been fetched.

So how do you get that count?

Easy:

$q = $db->query("SELECT ...");
$rows = $q->fetchAll();
$rowCount = count($rows);
echo "There are $rowCount rows\n";
foreach ($rows as $row) {
    print_r($row);
}

But that sucks because it queries all the rows up front and makes my page load slower, the old mysql extension didn't have this problem!?

But that's exactly what the old mysql extension is actually doing under the covers; it's the only way to get that count.

Answer 4

Thanks, worked for me as well!