bindParam/bindValue not working with my SQLite3 prepared statements

Question

Why is bindParam/bindValue not working with my SQLite3 prepared statements? I have tried both $stmt->bindValue and $stmt->bindParam.

This doesn't work:

$user = "exampleUser"
$db = new SQLite3('example.db');
$stmt = $db->prepare("SELECT COUNT(*) FROM users WHERE username='?'");
if (!$stmt) {
    echo $db->lastErrorMsg();;
    return false;
}
$stmt->bindParam(1, $user);
$result = $stmt->execute();
echo '<br>';
var_dump($result->fetchArray());

Neither does this:

$user = "exampleUser"
$db = new SQLite3('example.db');
$stmt = $db->prepare("SELECT COUNT(*) FROM users WHERE username=':user'");
if (!$stmt) {
    echo $db->lastErrorMsg();;
    return false;
}
$stmt->bindParam(":user", $user);
$result = $stmt->execute();
echo '<br>';
var_dump($result->fetchArray());

But this does work:

$db = new SQLite3('example.db');
$stmt = $db->prepare("SELECT COUNT(*) FROM users WHERE username='exampleUser'");
if (!$stmt) {
    echo $db->lastErrorMsg();;
    return false;
}
$result = $stmt->execute();
echo '<br>';
var_dump($result->fetchArray());

Why you not using `$db->lastErrorMsg();`? `echo 'prepare statement failed';` is almost as bad as no error at all. — Lawrence Cherone, Jan 30 '18 at 04:59
Thanks, will update that. I am not so familiar with PHP/SQLite3 — DarkMatterMatt, Jan 30 '18 at 05:01
np, before you go any further you might want to use PDO instead, as later on you may want to use something other than sqlite, which PDO supports anyway. If your just learning the SQLite3 API for the sake of it then carry on. ;p — Lawrence Cherone, Jan 30 '18 at 05:07

laalto · Accepted Answer · 2018-01-30T05:07:22.320

1

Variables are not string literals.

'?' should be ? and ':user' should be :user, without single quotes.

edited Jan 30 '18 at 05:07

answered Jan 30 '18 at 05:00

laalto

150,114
66
286
303

Thank you, I'll accept your answer in 8 mins. For future reference, the PDO bindParam/bindValue requires the `:` before named variable ([example](https://stackoverflow.com/questions/1179874/what-is-the-difference-between-bindparam-and-bindvalue)), SQLite3 does not. – DarkMatterMatt Jan 30 '18 at 05:05

bindParam/bindValue not working with my SQLite3 prepared statements

1 Answers1