PDO SQLite query with zero result

Question

I looked around, but did not seem to find any information about this. I am not sure if this is a problem with my code or a known problem with SQLite databases in memory and PDO.

Basically, after inserting one row into the SQLite database table in memory, I expect a query that does not match the inserted element to return zero rows. However, the following code gives one line (false), but does not have the actual PDO error code.

<?php // Create the DB $dbh = new PDO('sqlite::memory:'); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Data we'll be using $name = 'Entry'; // Create DB table $dbh->query(' CREATE TABLE Test ( id INTEGER PRIMARY KEY AUTOINCREMENT, name VARCHAR(50) NOT NULL ) '); // Insert data $stmt = $dbh->prepare(' INSERT INTO Test ( name ) VALUES ( :name ) '); $stmt->bindParam(':name', $name, PDO::PARAM_STR, 50); $stmt->execute(); // Check data has actually been inserted $entries = $dbh->query(' SELECT * FROM Test ')->fetchAll(PDO::FETCH_ASSOC); var_dump($entries); // Query DB for non-existent items $stmt = $dbh->prepare(' SELECT * FROM Test WHERE name != :name '); $stmt->bindParam(':name', $name, PDO::PARAM_STR); $stmt->execute(); // How many rows returned echo $stmt->rowCount(); // Actual data returned var_dump($stmt->fetch(PDO::FETCH_ASSOC)); ?> 

I managed to get around the problem with some hackers, but it would be nice not to do this:

 <?php echo ( (0 == $stmt->rowCount()) || ( (1 == $stmt->rowCount()) && (false === (($row = $stmt->fetch(PDO::FETCH_ASSOC)))) && ('0000' == array_pop($dbh->errorInfo())) ) ) ? 'true' : 'false'; ?> 

Can someone help or point out any egregious mistakes I could make?

Answer 1

PDOStatement :: rowCount () returns

... the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object.

If the last SQL statement executed by the associated PDOStatement was a SELECT statement, some databases may return the number of rows returned by this statement. However, this behavior is not guaranteed for all databases and should not rely on portable applications.

Welcome to PDO, where simple stuff works and not-so-simple stuff ruins your day. SQLite is one of the drivers that does not have the reliable "how many rows in my result set?" function. From the comments:

As in SQLite 3.x, the SQLite API itself has changed, and now all queries are implemented using "statements". Because of this, it is not possible for PDO to know the rowCount of the SELECT result, because the SQLite API itself does not offer this feature.

Returning false from PDOStatement :: fetch () is a guarantee that "nothing returned" and your verification code is completely normal if it is a little difficult to read. You might consider wrapping or getting PDO and PDOStatement for your own sanity.

(Disclaimer: I'm a PDO fan.)