Pdo Sqlite and php

Question

I have a question related to php / pdo and sqlite. I have ported some code from a mysql backend to a sqlite backend. I have used rowCount() alot in this project.

In my original Mysql application i did this:

$stmt = $db->query("SELECT id FROM table where id = $id "); 
$rc = $stmt->rowCount();
if ($rc == 1) {
// do something 
    }

The documentation says this method is only for returning affected rows from UPDATE, INSERT, DELETE queries, with the PDO_MYSQL driver (and this driver only) you can get the row count for SELECT queries.

So, how to achive the same thing with a sqlite backend?

This is how I have ended up doing it:

$stmt = $db->query("SELECT count(id) as cnt FROM table where id = $id "); 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($row['cnt'] == "1") {
        // do something
    } else {
        return false;
        }
    }
}

I am looking for a more elegant solution, while achieving the same thing as rowCount().

Since you're still using PDO you can still use `rowCount()`. — Jay Blanchard, Dec 09 '14 at 21:17
possible duplicate of [Row count with PDO](http://stackoverflow.com/questions/883365/row-count-with-pdo) — Jay Blanchard, Dec 09 '14 at 21:19

DevPGSV · Accepted Answer · 2014-12-09T21:39:08.837

This is the way to do that in pdo:

$stmt = $db->query('SELECT * FROM table');
if ($stmt->rowCount() == 1) {
    //...
} else {
    //...
}
echo $row_count.' rows selected';

(The same way XD)

BTW, I wouldn't recommend doing something like

$stmt = $db->query("SELECT count(id) as cnt FROM table where id = $id ");

It's not good to have variables in statements like that. use something like:

$stmt = $db->query('SELECT id FROM table where id = ?');
$stmt->execute(array($id));
if ($stmt->rowCount() == 1)
{
    $arr = $stmt->fetch(PDO::FETCH_ASSOC);
    foreach($arr as $element)
    {
        echo '<pre>'.print_r($element).'</pre>';
    }
}
else
{
    //...
}

This is part of some code I'm using:

$stmt = $db->prepare('SELECT * FROM users WHERE id=?');
$stmt->execute(array($id));
if ($stmt->rowCount() == 1) {
    $currentUser = $stmt->fetchAll(PDO::FETCH_ASSOC)[0];
} else {
    return false;
}

Edit: (for compatibility issues)

$stmt = $db->query('SELECT * FROM table');
$arr = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (($arr === false) || (sizeof($arr) == 0)) {
    return false;
} else {
    //... do ....
    echo sizeof($arr);
}

As I said in my question, according to the documentation and my experience, rowCount() only works with select statements if you are using Mysql as backend, not with sqlite. @DevPGSV — TheG, Dec 09 '14 at 21:35

Pdo Sqlite and php

1 Answers1