If you are reading this post, you’re probably facing an issue with sqlite and pdo that prevents you from using the rowCount method on a PDOStatement instance.

Apparently the new API of sqlite does not allow pdo to get this information. At work we faced this issue so I wanted to share it the web :)

In order to do this we need to overwrite the default PDOStatement. To do this we need to start by extending PDO and Using the ATTR_STATEMENT_CLASS flag in order to use our tweak statement class.

PDO::ATTR_STATEMENT_CLASS: Set user-supplied statement class derived from PDOStatement. Cannot be used with persistent PDO instances. Requires array(string classname, array(mixed constructor_args)).

Database.class.php

class Database extends PDO
{
	function __construct(string $dsn,$username = null,$password = null,array $driver_options = null)
	{
		parent::__construct($dsn, $username, $password, $driver_options);

		if($this->getAttribute(PDO::ATTR_DRIVER_NAME) == "sqlite") {
			$this->setAttribute(PDO::ATTR_STATEMENT_CLASS, array( 'Statement', array( $this )));
		}
	}

}

Statement.class.php

class Statement extends PDOStatement
{

	/**
	 *
	 * @var \Pdo
	 */
	private $_pdo;

	/**
	 *
	 * Constructor
	 *
	 * @param \Pdo $pdo
	 */
	protected function __construct($pdo)
	{
		$this->_pdo = $pdo;
	}

	/**
	 * Returns the number of rows affected by the last SQL statement
	 *
	 * @link http://www.php.net/manual/en/pdostatement.rowcount.php
	 * @return int the number of rows.
	 */
	public function rowCount()
	{
		// In case of sqllite, we don't have the row count
		// So we have to re run the statement in order to get the row count
		// And to avoid to move the current cursor
		$statement = $this->_pdo->query($this->queryString);
	        return count($statement->fetchAll());
	}

}

As you can see we use the count() function on the sqlite workaround, using this on very large dataset can be a really bad idea. We could create a count query here instead but It would’nt work on all query(complex group by’s etc..). If you are only using simple queries you can rewrite the query on the go to issue a count instead (if you need inspiration ).

Since we check for the driver on the PDO constructor will not be used on other drivers than sqlite.

Have fun.

Share

Related Posts: