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
– Jay Blanchard, Commented Dec 9, 2014 at 21:17

DevPGSV · Accepted Answer · 2014-12-09 21:39:08Z

1

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);
}

edited Dec 9, 2014 at 21:39

answered Dec 9, 2014 at 21:18

DevPGSV

1918 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TheG Over a year ago

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

Collectives™ on Stack Overflow

Pdo Sqlite and php

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related