Use SQLite with PHP

I'm writing these mostly as a shorthand for myself when I need to cobble together some simple PHP thing with a database (and also to get better with writing out SQL by hand). Most of what's here is taken almost verbatim from a much more comprehensive gist.

Please ignore the fact that these todos are neither completable nor removable.

Connecting

The first argument here is the name of the database you're connecting to. The second argument should be pretty clear: if the database doesn't exist, create it; and if it does exist, open it for reading and writing.

$db = new SQLite3('db_name.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);

Run a query

$db->query('CREATE TABLE IF NOT EXISTS "todos" (
 "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
 "name" TEXT
)');

If you're doing multiple queries at once

If you're going to be running more than one query at a time, wrap them all in a 'transaction' (BEGIN and COMMIT). SQLite wraps each query individually by default. Breaking each query out into individual transactions seems to be a resource-intensive procedure, so wrapping multiple queries is a cheap way to boost performance.

$db->exec('BEGIN');
$db->query('INSERT INTO "todos" ("name")
 VALUES ("Write the blog post about PHP and SQLite"');
$db->query('INSERT INTO "todos" ("name")
 VALUES ("Insert a second todo just for the purposes of clarification"');
$db->exec('COMMIT');

Query a row -> associative array

You can 'prepare' a query, which helps SQLite plan out how to actually run the query, for performance reasons. For simple INSERTs and SELECTs, you probably won't see much of a difference in performance, but prepared queries with JOINs, indexes, or lots of iterations will see a marked improvement over unprepared $db->query calls.

Thus:

$result = $db->query('SELECT * FROM "todos" WHERE "id" = "1");

Can be rewritten as:

$statement = $db->prepare('SELECT * FROM "todos" WHERE "id" = ?');
$statement->bindValue(1, 1);
$result = $statement->execute();

Either way, you have to get the todo itself by running fetchArray() on the SQLite3Result object:

$todo = result->fetchArray(SQLITE_ASSOC);

Query for all rows

$statement = $db->prepare('SELECT * FROM "todos"');
$result = $statement->execute();

// alternately:
// $result = $db->query('SELECT * FROM "todos"');

while($row = $result->fetchArray(SQLITE_ASSOC)) {
 $todos[] = $row
}

Query for a single value

$statement = $db->prepare('SELECT name FROM "todos" WHERE "id" = ?');
$statement->bindValue(1, 1);
$result = $statement->execute();
$name = $result->fetch();

Or the shorthand:

$name = $db->querySingle('SELECT name FROM "todos" WHERE "id" = "1"');

Insert new data from, say, a form

if(!empty($_POST)) {
 $db->exec('BEGIN');
 $db->query('INSERT INTO "todos" ("name") VALUES ("' . $_POST['name'] . '")');
 $db->exec('COMMIT');
}

Free the memory when you're done querying

$result->finalize();

Consider closing the database connection when you're through with it

That being said, SQLite will do this when the script ends anyway.

$db->close();