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();
}TIPS:
- Make sure the web server user (www-data usually) has write access to both the database file AND the directory it resides in.
- Might need to install `apt install php-pdo-sqlite`.