The bug

If you’re running PHP 5.1.6 and just started using PDO for your database connection, it’s likely you’ll run into quite an annoying bug.
Lets test a simple query partly taken from PHP documentation.

$calories = 150;
$colour = 'red';
$sth = $myPDO->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
if ($sth->rowCount() >= 1) {
    // iterate through results
}

Everything seems great. The problem is that $sth->rowCount() will always return 0. No matter how many results your query returns, the value of rowCount() will always be 0.

Full bug report can be found here: http://bugs.php.net/40822.

Solution

Upgrade PHP

If that’s not an option read on.

Lets create our own simple PDO and PDOStatement classes.

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
	}
}

class myPDOStatement extends PDOStatement {
	public $db;

	protected function __construct($db) {
		$this->db = $db;
	}
}

Above will not fix anything. This is just a start so we can overload some PDO methods to apply the fix.

To fix the bug we need to tell MySQL to use the buffered versions of the MySQL API by setting attribute MYSQL_ATTR_USE_BUFFERED_QUERY to true.
For some reason, still unknown to me setting this option like that

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
		$this->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
	}
}

will not work.

After many hours of tearing my hair out I found out that all you need to do is this:

class myPDO extends PDO {
	function __construct($name_host, $username='', $password='', $driverOptions=array()) {
		$driverOptions[PDO::MYSQL_ATTR_USE_BUFFERED_QUERY] = true;
		parent::__construct($name_host, $username, $password, $driverOptions);
		$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('myPDOStatement', array($this)));
	}
}

You have to set MYSQL_ATTR_USE_BUFFERED_QUERY before instantiating a connection. Setting it after the connection was made will not work.

When this is done we need to modify our myPDOStatement class.

class myPDOStatement extends PDOStatement {
	public $db;
	// other attributes
	private $foundRows; // will hold number of affected rows

	protected function __construct($db) {
		$this->db = $db;
	}

	public function execute($array = null) {
		if ($array === null) {
			$result = parent :: execute();
		} else {
			$result = parent :: execute($array);
		}

		// fix for PHP 5.1.6 rowCount error
		$this->foundRows = $this->db->query("SELECT FOUND_ROWS()")->fetchColumn();

		return $result;
	}

	public function rowCount() {
		return $this->foundRows;
	}
}

After above changes our rowCount() method will return proper values.