Introduction
SQLite is a software library that provides a relational database management system. The lite in SQLite means lightweight in terms of setup, database administration, and required resource. PHP has built-in extensions for integrating with SQLite, making it a convenient choice for small to medium-sized projects. In this tutorial, we’re going to learn how to use SQLite in PHP, including basic operations like creating databases, writing queries, and managing data.
Setting up Your Environment
To work with SQLite in PHP, you’ll need PHP 5 or newer, as SQLite is bundled with PHP from version 5 onwards. Most current web hosting providers support PHP and SQLite, so you should be ready to go. If you are working locally, install a server environment like XAMPP or MAMP that supports PHP and comes with SQLite.
Creating a New SQLite Database
<?php
$db = new SQLite3('my_database.db');
echo 'Database created successfully';
?>This snippet creates a new SQLite database file called ‘my_database.db’ or opens it if it already exists.
Creating Tables
<?php
$db->exec('CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
phone TEXT NOT NULL
);');
echo 'Table created successfully';
?>This code will create a new table called ‘contacts’ if it doesn’t already exist in the database.
Inserting Data
<?php
$statement = $db->prepare('INSERT INTO contacts (name, phone) VALUES (:name, :phone)');
$statement->bindValue(':name', 'John Doe');
$statement->bindValue(':phone', '123-456-7890');
$statement->execute();
echo 'Record created successfully';
?>We’re using a prepared statement to insert data into our contacts table safely. It helps prevent SQL injection attacks.
Querying Data
<?php
$results = $db->query('SELECT * FROM contacts');
while ($row = $results->fetchArray()) {
echo 'ID: ' . $row['id'] . ' Name: ' . $row['name'] . ' Phone: ' . $row['phone'];
}
?>This snipped fetches everything from the contacts table and prints out the results.
Updating Data
<?php
$db->exec("UPDATE contacts SET phone = '999-999-9999' WHERE name = 'John Doe'");
echo 'Record updated successfully';
?>This code updates the phone number for contacts where the name is ‘John Doe’.
Deleting Data
<?php
$db->exec("DELETE FROM contacts WHERE name = 'John Doe'");
echo 'Record deleted successfully';
?>This snippet will delete records from the contacts table where the name is ‘John Doe’.
Error Handling
<?php
if (!$db->exec('SOME INVALID SQL')) {
echo 'SQLite Error: ' . $db->lastErrorMsg();
}
?>This code checks if your SQL is executed correctly. If not, it will print out an error message.
Securing Your SQLite Database
# Keeping your .db files safe
You should ensure your .db files are not accessible via the web. Store your database file outside of the public_html directory whenever possible.
# Using encryption
SQLite databases do not come with built-in encryption. For sensitive data, consider using an extension like SQLCipher which extends SQLite with AES encryption.
Conclusion
In this tutorial, we’ve covered the basics of using SQLite in PHP. We’ve created a new SQLite database, defined a table structure, inserted data into that table, performed queries to retrieve and update that data, and cleaned up by deleting unnecessary records. Always remember to follow best practices when handling databases and ensure the security of your data. SQLite is a powerful tool when combined with PHP, providing a simple yet effective way to store data for web applications.