Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save richardschoen/fd59f9a371cba5ec05e6c37f42666995 to your computer and use it in GitHub Desktop.
Save richardschoen/fd59f9a371cba5ec05e6c37f42666995 to your computer and use it in GitHub Desktop.
SQLite3 PHP Quickstart Tutorial
<?php
// This file walks you through the most common features of PHP's SQLite3 API.
// The code is runnable in its entirety and results in an `analytics.sqlite` file.
// Create a new database, if the file doesn't exist and open it for reading/writing.
// The extension of the file is arbitrary.
$db = new SQLite3('analytics.sqlite', SQLITE3_OPEN_CREATE | SQLITE3_OPEN_READWRITE);
// Errors are emitted as warnings by default, enable proper error handling.
$db->enableExceptions(true);
// Create a table.
$db->query('CREATE TABLE IF NOT EXISTS "visits" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"user_id" INTEGER,
"url" VARCHAR,
"time" DATETIME
)');
// Insert some sample data.
//
// INSERTs may seem very slow in SQLite, which happens when not using transactions.
// It's advisable to wrap related queries in a transaction (BEGIN and COMMIT),
// even if you don't care about atomicity.
// If you don't do this, SQLite automatically wraps every single query
// in a transaction, which slows everything down immensely.
$db->exec('BEGIN');
$db->query('INSERT INTO "visits" ("user_id", "url", "time")
VALUES (42, "/test", "2017-01-14 10:11:23")');
$db->query('INSERT INTO "visits" ("user_id", "url", "time")
VALUES (42, "/test2", "2017-01-14 10:11:44")');
$db->exec('COMMIT');
// Insert potentially unsafe data with a prepared statement.
// You can do this with named parameters:
$statement = $db->prepare('INSERT INTO "visits" ("user_id", "url", "time")
VALUES (:uid, :url, :time)');
$statement->bindValue(':uid', 1337);
$statement->bindValue(':url', '/test');
$statement->bindValue(':time', date('Y-m-d H:i:s'));
$statement->execute(); // you can reuse the statement with different values
// Fetch today's visits of user #42.
// We'll use a prepared statement again, but with numbered parameters this time:
$statement = $db->prepare('SELECT * FROM "visits" WHERE "user_id" = ? AND "time" >= ?');
$statement->bindValue(1, 42);
$statement->bindValue(2, '2017-01-14');
$result = $statement->execute();
echo("Get the 1st row as an associative array:\n");
print_r($result->fetchArray(SQLITE3_ASSOC));
echo("\n");
echo("Get the next row as a numeric array:\n");
print_r($result->fetchArray(SQLITE3_NUM));
echo("\n");
// If there are no more rows, fetchArray() returns FALSE.
// free the memory, this in NOT done automatically, while your script is running
$result->finalize();
// A useful shorthand for fetching a single row as an associative array.
// The second parameter means we want all the selected columns.
//
// Watch out, this shorthand doesn't support parameter binding, but you can
// escape the strings instead.
// Always put the values in SINGLE quotes! Double quotes are used for table
// and column names (similar to backticks in MySQL).
$query = 'SELECT * FROM "visits" WHERE "url" = \'' .
SQLite3::escapeString('/test') .
'\' ORDER BY "id" DESC LIMIT 1';
$lastVisit = $db->querySingle($query, true);
echo("Last visit of '/test':\n");
print_r($lastVisit);
echo("\n");
// Another useful shorthand for retrieving just one value.
$userCount = $db->querySingle('SELECT COUNT(DISTINCT "user_id") FROM "visits"');
echo("User count: $userCount\n");
echo("\n");
// Finally, close the database.
// This is done automatically when the script finishes, though.
$db->close();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment