PHP & SQLite CRUD — The Right Way Without a Framework

CRUD (Create, Read, Update, Delete) is the foundation of nearly every web app. Here's how to do it right with PHP and SQLite — including security patterns most tutorials skip.

Database Setup

CREATE TABLE items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    status TEXT DEFAULT 'draft',
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

In PHP:

function getDB() {
    static $db = null;
    if ($db) return $db;
    $db = new PDO('sqlite:' . __DIR__ . '/data/app.sqlite');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    return $db;
}

CREATE

function createItem($title, $body, $status = 'draft') {
    $db = getDB();
    $stmt = $db->prepare(
        "INSERT INTO items (title, body, status) VALUES (:title, :body, :status)"
    );
    $stmt->execute([
        ':title' => $title,
        ':body' => $body,
        ':status' => $status,
    ]);
    return $db->lastInsertId();
}

Always use prepared statements. Never interpolate values into SQL strings.

READ (Single)

function getItem($id) {
    $db = getDB();
    $stmt = $db->prepare("SELECT * FROM items WHERE id = :id");
    $stmt->execute([':id' => $id]);
    return $stmt->fetch(); // Returns false if not found
}

READ (List with Pagination)

function getItems($page = 1, $perPage = 10) {
    $db = getDB();
    $offset = ($page - 1) * $perPage;
    $stmt = $db->prepare("SELECT * FROM items ORDER BY created_at DESC LIMIT :limit OFFSET :offset");
    $stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();
    $items = $stmt->fetchAll();
    $total = $db->query("SELECT COUNT(*) FROM items")->fetchColumn();
    return [
        'items' => $items,
        'total' => $total,
        'pages' => max(1, ceil($total / $perPage)),
        'page' => $page,
    ];
}

UPDATE

function updateItem($id, $title, $body, $status) {
    $db = getDB();
    $stmt = $db->prepare(
        "UPDATE items SET title = :title, body = :body, 
         status = :status, updated_at = datetime('now')
         WHERE id = :id"
    );
    $stmt->execute([
        ':id' => $id,
        ':title' => $title,
        ':body' => $body,
        ':status' => $status,
    ]);
    return $stmt->rowCount() > 0;
}

DELETE

function deleteItem($id) {
    $db = getDB();
    $stmt = $db->prepare("DELETE FROM items WHERE id = :id");
    $stmt->execute([':id' => $id]);
    return $stmt->rowCount() > 0;
}

Input Validation

Never trust user input:

function validateItemInput($data) {
    $errors = [];
    if (empty(trim($data['title']))) {
        $errors[] = 'Title is required';
    } elseif (strlen($data['title']) > 200) {
        $errors[] = 'Title must be under 200 characters';
    }
    if (empty(trim($data['body']))) {
        $errors[] = 'Body is required';
    }
    $allowed = ['draft', 'published', 'archived'];
    if (!in_array($data['status'], $allowed)) {
        $errors[] = 'Invalid status';
    }
    return $errors;
}

Form Handler Pattern

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $errors = validateItemInput($_POST);
    if (empty($errors)) {
        $id = createItem(
            trim($_POST['title']),
            trim($_POST['body']),
            $_POST['status'] ?? 'draft'
        );
        header('Location: /item.php?id=' . $id);
        exit;
    }
}

Always redirect after POST to prevent duplicate submissions on page refresh (PRG pattern).

Summary

The pattern repeats for any CRUD app: validate → prepared statement → return result. SQLite handles concurrency fine for small-to-medium traffic. For write-heavy apps, add PRAGMA journal_mode=WAL for better performance.