SQLite - PHP

SQLite and PHP: A Modern Approach

This chapter will guide you through using SQLite in modern PHP applications. We will focus on current best practices, including secure coding with prepared statements and robust error handling.

Prerequisites and Setup

Modern PHP versions (PHP 7.4+ and newer) typically include the SQLite3 extension by default. To verify it’s enabled, you can run the following command in your terminal:

php -m | grep sqlite3

If it’s not listed, you may need to enable it in your php.ini file by uncommenting (removing the leading semicolon) the line extension=sqlite3. Windows users would uncomment extension=php_sqlite3.dll.

For managing project dependencies, it’s a best practice to use Composer, even for projects without external libraries. It provides a structured project setup and reliable autoloading.

Key PHP SQLite3 Class Methods

PHP’s SQLite3 class provides an object-oriented interface to SQLite. While there are many methods, modern development workflows primarily rely on a few key ones for safe and efficient database interaction. We will use an exception-based error model, which is cleaner than checking return values after every call.

Method	Description
1	public SQLite3::__construct(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = '') Opens or creates an SQLite database. Throws an Exception on failure.
2	public SQLite3::enableExceptions(bool $enable) Crucial for modern error handling. When set to true, the SQLite3 object will throw exceptions on errors instead of returning false, simplifying your code.
3	public SQLite3::prepare(string $query): SQLite3Stmt|false Prepares an SQL statement for execution. This is the first step in using prepared statements to prevent SQL injection. Returns a SQLite3Stmt object.
4	public SQLite3Statement::bindValue(string|int $param, mixed $value, int $type): bool Binds a value to a parameter in a prepared statement. Parameters can be named (e.g., :name) or positional (?).
5	public SQLite3Statement::execute(): SQLite3Result|false Executes a prepared statement. For SELECT queries, it returns an SQLite3Result object. For other queries, it returns true on success.
6	public SQLite3Result::fetchArray(int $mode = SQLITE3_BOTH): array|false Fetches one row from a result set. SQLITE3_ASSOC returns an associative array, SQLITE3_NUM returns a numeric array.
7	public SQLite3::lastInsertRowID(): int Returns the row ID of the most recent INSERT statement.
8	public SQLite3::changes(): int Returns the number of rows affected by the most recent UPDATE or DELETE statement.
9	public SQLite3::close(): bool Closes the database connection. While not strictly necessary as PHP handles this on script exit, it’s good practice for long-running scripts.

Connecting to the Database

Here is a modern, robust way to connect to an SQLite database. We use a try...catch block to handle potential connection errors gracefully.

<?php
declare(strict_types=1);

$dbFile = __DIR__ . '/inventory.db';

try {
    // Open the database file. If it doesn't exist, it will be created.
    $db = new SQLite3($dbFile);

    // Enable exceptions for error handling
    $db->enableExceptions(true);

    echo "Connected to database 'inventory.db' successfully.\n";

} catch (Exception $e) {
    // For production, log the error instead of displaying it to the user.
    error_log('Database connection failed: ' . $e->getMessage());
    // Send a generic error response.
    http_response_code(500);
    echo "Error: Could not connect to the database.\n";
    exit; // Stop script execution
}

Running this script creates a file named inventory.db in the same directory and prints a success message. Note that the web server (e.g., Apache, Nginx) must have write permissions for the directory where the database file is stored.

Create a Table

We’ll use the exec() method for simple, one-off queries that don’t return results and don’t involve external data. It’s suitable for schema setup.

<?php
// (Include the connection code from the previous step)

try {
    $sql = 'CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL,
        last_updated TEXT NOT NULL
    );';

    $db->exec($sql);
    echo "Table 'products' created or already exists.\n";

} catch (Exception $e) {
    error_log('Table creation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not create table.\n";
    exit;
}

INSERT Operation (with Prepared Statements)

To insert data securely, we must use prepared statements. This practice prevents SQL injection attacks by separating the SQL command from the data. We use named parameters (e.g., :name) for clarity.

<?php
// (Include the connection code)

try {
    $sql = 'INSERT INTO products (name, quantity, price, last_updated)
            VALUES (:name, :quantity, :price, :last_updated)';

    $stmt = $db->prepare($sql);

    // Data for the first product
    $stmt->bindValue(':name', 'Laptop Pro', SQLITE3_TEXT);
    $stmt->bindValue(':quantity', 15, SQLITE3_INTEGER);
    $stmt->bindValue(':price', 1299.99, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->execute();
    echo "Record created successfully (ID: " . $db->lastInsertRowID() . ").\n";

    // Data for a second product
    $stmt->bindValue(':name', 'Wireless Mouse', SQLITE3_TEXT);
    $stmt->bindValue(':quantity', 200, SQLITE3_INTEGER);
    $stmt->bindValue(':price', 25.50, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->execute();
    echo "Record created successfully (ID: " . $db->lastInsertRowID() . ").\n";

} catch (Exception $e) {
    error_log('Insert operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not insert record.\n";
    exit;
}

SELECT Operation

To fetch data, we execute a SELECT query. The result is an SQLite3Result object, which we can iterate over to get each row.

<?php
// (Include the connection code)

try {
    $sql = 'SELECT id, name, quantity, price FROM products';
    $result = $db->query($sql);

    echo "--- Product Inventory ---\n";
    while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
        echo "ID: {$row['id']}\n";
        echo "Name: {$row['name']}\n";
        echo "Quantity: {$row['quantity']}\n";
        echo "Price: ${$row['price']}\n\n";
    }
    echo "Operation done successfully.\n";

} catch (Exception $e) {
    error_log('Select operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not fetch records.\n";
    exit;
}

When the above program is executed, it will produce the following result:

--- Product Inventory ---
ID: 1
Name: Laptop Pro
Quantity: 15
Price: $1299.99

ID: 2
Name: Wireless Mouse
Quantity: 200
Price: $25.5

Operation done successfully.

UPDATE Operation

Updating records also requires prepared statements to safely handle WHERE clause values. The changes() method tells us how many rows were affected.

<?php
// (Include the connection code)

try {
    $sql = 'UPDATE products
            SET price = :price, last_updated = :last_updated
            WHERE name = :name';

    $stmt = $db->prepare($sql);
    $stmt->bindValue(':price', 1250.00, SQLITE3_FLOAT);
    $stmt->bindValue(':last_updated', date('Y-m-d H:i:s'), SQLITE3_TEXT);
    $stmt->bindValue(':name', 'Laptop Pro', SQLITE3_TEXT);

    $stmt->execute();

    $changes = $db->changes();
    echo "{$changes} record(s) updated successfully.\n";

} catch (Exception $e) {
    error_log('Update operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not update record.\n";
    exit;
}

After running this, a subsequent SELECT would show the updated price for ‘Laptop Pro’.

DELETE Operation

Deleting follows the same secure pattern. Use a prepared statement to specify which record(s) to remove.

<?php
// (Include the connection code)

try {
    $sql = 'DELETE FROM products WHERE id = :id';

    $stmt = $db->prepare($sql);
    $stmt->bindValue(':id', 2, SQLITE3_INTEGER); // Delete 'Wireless Mouse'
    $stmt->execute();

    $changes = $db->changes();
    echo "{$changes} record(s) deleted successfully.\n";

} catch (Exception $e) {
    error_log('Delete operation failed: ' . $e->getMessage());
    http_response_code(500);
    echo "Error: Could not delete record.\n";
    exit;
} finally {
    // Always close the connection
    if (isset($db)) {
        $db->close();
        echo "Database connection closed.\n";
    }
}

Next Steps and Further Learning

You have now mastered the fundamentals of CRUD (Create, Read, Update, Delete) operations in PHP with SQLite using modern, secure practices. For larger applications, consider exploring:

• PDO (PHP Data Objects): A database abstraction layer built into PHP. Using PDO_SQLite provides an interface that makes it easy to switch to another database (like MySQL or PostgreSQL) in the future with minimal code changes.
• Object-Relational Mappers (ORMs): Libraries like Doctrine or Eloquent (from the Laravel framework) allow you to interact with your database using PHP objects, abstracting away the SQL entirely. This can significantly speed up development for complex applications.