Why is nothing written in the Sqlite3 database with this PHP code?

Question

I usually used PDO with new PDO('sqlite:test.log'); to write in Sqlite3 databases with PHP.

Now, for a performance comparison, I'd like to try using the SQLite3 PHP class :

$db = new SQLite3('test.db');
$db->query("CREATE TABLE IF NOT EXISTS log (a, b, d);");
$stmt = $db->prepare("INSERT INTO log VALUES (?, ?, ?);");
$stmt->execute(array("a", date("Y-m-d\TH:i:sO"), 123));

Unfortunately, after this code is executed, nothing seems to be written: SELECT * from log gives no row.

What's wrong in my code?

I also tried $db->commit(); or $db->query('COMMIT;') without success.

Context: I usually use PDO, but I noticed it takes ~ 50 ms for a simple 1) open of the DB, 2) add a row, 3) commit and close. So I was curious if it was any better with Sqlite3 class instead of PDO. Result: it's the same: ~ 50 ms.

Check for errors.

Barmar
– Barmar

2021-09-20 15:48:48 +00:00
Commented Sep 20, 2021 at 15:48 — Barmar
– Barmar, Commented Sep 20, 2021 at 15:48

Barmar · Accepted Answer · 2021-09-20 15:53:08Z

2

Unlike PDO, you can't provide parameters in the arguments to $stmt->execute(), you have to call $stmt->bindParam() or $stmt->bindValue().

$stmt = $db->prepare("INSERT INTO log VALUES (?, ?, ?);");
$stmt->bindValue(1, "a");
$stmt->bindValue(2, date("Y-m-d\TH:i:sO"));
$stmt->bindValue(3, 123);
$stmt->execute();

If you have an array of values, you can call bindValue() in a loop:

foreach (array("a", date("Y-m-d\TH:i:sO"), 123) AS $i => $value) {
    $stmt->bindValue($i+1, $value);
}

edited Sep 20, 2021 at 15:53

answered Sep 20, 2021 at 15:51

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

11 Comments

Basj Over a year ago

Thanks @Barmar! How to bindValue 10 params in a single call instead of 10 calls of bindValue?

Barmar Over a year ago

I added a loop to do it.

Barmar Over a year ago

Read the documentation and you'll see that there isn't.

Barmar Over a year ago

Why don't you just use PDO?

Basj Over a year ago

I usually use PDO @Barmar, but I noticed it takes 50ms for such a simple 1) open of the DB, 2) add a row, 3) commit and close. So I was curious if it was any better with Sqlite3 class instead of PDO. Result: it's the same: ~50 ms.

|

Collectives™ on Stack Overflow

Why is nothing written in the Sqlite3 database with this PHP code?

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related