If you are working with a MySQL database only, then you may want to employ MySQL's PDO driver buffered query mode. When the connection is set to the buffered query mode, the whole result set for every SELECT query is pre-fetched into memory before it is returned to the application. This gives us one benefit—we can use the PDOStatement::rowCount() method to inspect how many rows the result set contains. In Chapter 2, we discussed this method and showed that it returns 0 for MySQL and SQLite databases. Now, when PDO is instructed to use buffered queries, this method will return meaningful values.
To force PDO into MySQL buffered query mode, you have to specify the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY connection attribute. Consider the following example:
$conn = new PDO($connStr, $user, $pass);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, 1);
$q = $conn->query("SELECT * FROM books");
echo...