Counting rows / SQLite and prepared statement

[Gandalf]

I’m trying to count the number of rows in a SQLite table. I’ve got so far but what comes next please?

$query = 'SELECT count(*) FROM table WHERE keywords LIKE :keywords;';
$stmt  = $db->prepare($query);
$stmt->bindValue(':keywords', '%'.$keywords.'%');
$stmt->execute();

Without a prepared statement I have:

$query  = 'SELECT count(*) FROM table;';
$result = $db->query($query);
$data   = $result->fetch(PDO::FETCH_ASSOC);
$count  = $data['count(*)'];

[ahundiak]

Almost there:

$stmt->execute();
$data = $stmt->fetch(PDO::FETCH_ASSOC);
$count = $data['count(*)'];

http://php.net/manual/en/pdo.prepare.php

You can avoid the need for the fetch assoc constant when creating your pdo object.

[Gandalf]

Many thanks @ahundiak

[Dormilich]

since there is only one result item, $count = $stmt->fetchColumn() would be sufficient.

[Gandalf]

Thanks @Dormilich. I have replaced

$query  = 'SELECT count(*) FROM talks WHERE keywords LIKE :keywords;';
$stmt   = $db->prepare($query);
$stmt->bindValue(':keywords', '%'.$keywords.'%');
$stmt->execute();
$data   = $stmt->fetch(PDO::FETCH_ASSOC);
$count  = $data['count(*)'];

with

$query  = 'SELECT count(*) FROM talks WHERE keywords LIKE :keywords;';
$stmt   = $db->prepare($query);
$stmt->bindValue(':keywords', '%'.$keywords.'%');
$count = $stmt->fetchColumn();

but whereas the first returns 5, the second returns null. Have I misunderstood?

[Dormilich]

you’re missing the execute() call.

[Gandalf]

Der! Thanks. I thought I tried it with and without. Clearly not.