PDO rowCount() not working in PHP 5.1.6

Posted by 6bytes at 30 March 2010

Category: MySQL, PHP

Tags: , , , ,

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.
 

  • Kevan Benson

    Are you sure this does what you think in all cases? FOUND_ROWS() actually returns the number of rows that WOULD have been returned if no limit modifier was to be used on the statement. In most cases, that’s probably fine, but if you use a limit on your SELECT statement, the result will not be correct.

  • http://allurcode.com Wojtek Kosinski

    @Kevan I just tested it to be 100% sure and with the use of above fix it all ended up just fine.
    I run a simple query that returned 246 rows, then I added LIMIT 10 to the query and rowCount() retuned 10 rows.
    Did it not work as expected for you? Can you elaborate a little more?

  • James

    rowCount is mean delete/update/insert operation, not the calculation of the rows its found.