How to Use SQLite3 with PHP

SQLite is a relational database that stores data in memory or in a single portable file. These code snippets demonstrate how to use SQLite with PHP. One thing to note about exec verus query: the first executes a result-less query, and the latter performs a query that returns results. It is better to use a prepared statement than a query though. It is easier, safer, and will use less memory.

// Create or open a database file
$file_db = new PDO('sqlite:myDatabase.sqlite3');

// Create an in-memory database
$memory_db = new PDO('sqlite::memory:');

// Close db connection whenever you are done by setting it to null
$db = null;

// Wrap your code in a try statement and catch PDOException
try {   
    // ...SQLite stuff...
} catch(PDOException $e) {
    echo $e->getMessage();
}

// Creating a table
$db->exec(
"CREATE TABLE IF NOT EXISTS myTable (
    id INTEGER PRIMARY KEY,
    title TEXT,
    value TEXT)"
);

// Drop a table
$drop = "DROP TABLE uselessTable";
$file_db->exec($drop);

// Querying
$result = $file_db->query('SELECT * FROM myTable');
foreach ($result as $result) {
    print $result['id'];
}

// Updating
$update = "UPDATE myTable SET value = 'Hakuna matata!' WHERE id = 5";
$database->exec($update);

// Inserting multiple records at once
$items = array(
    array(
        'title' => 'Hello!',
        'value' => 'Just testing...',
    ),
    array(
        'title' => 'Hello Twice!',
        'value' => 'Who is there?',
    ),
);

// Prepare INSERT statement to SQLite3 file db
$insert = "INSERT INTO myTable (title, value) VALUES (:title, :value)";
$statement = $db->prepare($insert);

// Bind parameters to statement variables
$stmt->bindParam(':title', $title);
$stmt->bindParam(':value', $value);

// Insert all of the items in the array
foreach ($data as $item) {
    $title = $item['title'];
    $message = $item['value'];

    $stmt->execute();
}