Basic Usage of SQLite in a PHP Application

SQLite is a lightweight, serverless, and self-contained database engine that’s commonly used for local storage and small-scale web applications. In this article, we’ll explore the fundamental steps to use SQLite in a PHP application. We’ll cover database connection, data manipulation, and error handling to help you get started.

Prerequisites

Before we begin, make sure you have the following prerequisites:

1. PHP: Ensure you have PHP installed. You can download PHP from the official PHP website.

Step 1: Creating an SQLite Database

SQLite databases are simple files that store data locally. To create an SQLite database in PHP, you need to connect to it. If the database doesn’t exist, SQLite will create it for you.

<?php
$database = new SQLite3('my_database.db');
?>

This code creates or opens a SQLite database file named my_database.db. You can replace 'my_database.db' with your desired database name.

Step 2: Executing SQL Queries

Once you have a database connection, you can execute SQL queries. Let’s start with a simple example: creating a table and inserting data into it.

<?php
// Create a "users" table
$query = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    email TEXT NOT NULL
)";
$database->exec($query);

// Insert data into the "users" table
$query = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";
$database->exec($query);

echo "Table created and data inserted successfully";
?>

Step 3: Querying Data

You can retrieve data from the SQLite database using SQL queries. Here’s an example of selecting data from the “users” table:

<?php
// Select data from the "users" table
$query = "SELECT id, username, email FROM users";
$result = $database->query($query);

while ($row = $result->fetchArray()) {
    echo "ID: " . $row['id'] . ", Username: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
}
?>

Step 4: Updating and Deleting Data

Updating and deleting data in SQLite is straightforward. Here’s an example of updating a user’s email address:

<?php
// Update a user's email address
$query = "UPDATE users SET email='[email protected]' WHERE username='john_doe'";
$database->exec($query);

echo "Data updated successfully";
?>

And here’s an example of deleting a user:

<?php
// Delete a user
$query = "DELETE FROM users WHERE username='john_doe'";
$database->exec($query);

echo "User deleted successfully";
?>

Step 5: Error Handling and Closing the Connection

Always handle errors gracefully and close the SQLite connection when done.

<?php
// Closing the SQLite database connection
$database->close();
?>

Conclusion

In this article, we’ve covered the basic usage of SQLite in a PHP application. You’ve learned how to create an SQLite database, execute SQL queries for data manipulation, retrieve data, update records, and delete records. SQLite is an excellent choice for local storage and small-scale web applications due to its simplicity and lightweight nature. As you continue your journey in web development, you can explore more complex database operations and learn how to integrate SQLite into larger PHP projects.