SQLite CRUD Tutorials in PHP: A Step-by-Step Guide

SQLite is a lightweight and self-contained relational database management system (RDBMS) that is often used for small-scale applications and mobile development. In this tutorial, we’ll explore the fundamental steps to perform CRUD (Create, Read, Update, Delete) operations using SQLite in a PHP application. We’ll cover database connection, data manipulation, and error handling with practical examples and detailed explanations.

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.

2. SQLite: SQLite is bundled with PHP, so there’s no need to install it separately.

Step 1: Connecting to SQLite

To connect to an SQLite database from a PHP application, you’ll use the PDO (PHP Data Objects) extension. Create a connection to the SQLite database:

<?php
$databaseFile = 'your_database.sqlite'; // Replace with your SQLite database file name

try {
    $connection = new PDO("sqlite:$databaseFile");
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    echo "Connected successfully";
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

Replace 'your_database.sqlite' with the name of your SQLite database file.

Step 2: Create (Insert) Data

Let’s start with creating (inserting) data into a table. Assume you have a “users” table with columns id, username, and email. Here’s how you can insert a new user:

<?php
// SQL query to insert data into the "users" table
$sql = "INSERT INTO users (username, email) VALUES ('john_doe', '[email protected]')";

try {
    $connection->exec($sql);
    echo "Data inserted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 3: Read (Select) Data

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

<?php
// SQL query to retrieve data from the "users" table
$sql = "SELECT id, username, email FROM users";

try {
    $result = $connection->query($sql);

    if ($result !== false) {
        foreach ($result as $row) {
            echo "ID: " . $row['id'] . ", Username: " . $row['username'] . ", Email: " . $row['email'] . "<br>";
        }
    } else {
        echo "No results found";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 4: Update Data

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

<?php
// SQL query to update a user's email address
$sql = "UPDATE users SET email='[email protected]' WHERE username='john_doe'";

try {
    $connection->exec($sql);
    echo "Data updated successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 5: Delete Data

You can delete data from the “users” table using SQL queries. Here’s an example of deleting a user:

<?php
// SQL query to delete a user
$sql = "DELETE FROM users WHERE username='john_doe'";

try {
    $connection->exec($sql);
    echo "User deleted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Step 6: Error Handling

Handle errors gracefully using try-catch blocks:

<?php
try {
    // Your SQLite operations here
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

Conclusion

In this tutorial, we’ve covered the basic CRUD operations using SQLite in a PHP application. You’ve learned how to connect to an SQLite database, perform create, read, update, and delete operations on data in a table. SQLite’s simplicity and self-contained nature make it a suitable choice for lightweight applications and prototyping. As you continue your journey in web development, you can explore more complex SQLite operations and best practices for efficient data management.