PROJECT 4 — SQLITE TASK MANAGER WITH PDO
Starter files for PHP the TPRM Way, Chapter 14


WHAT IS IN THIS FOLDER

  tasks.php            The page. It lists tasks and holds the POST forms for
                       creating, completing, and deleting one. Every action is a
                       TODO.
  task_functions.php   The connection, the schema, and the four database
                       operations. Every body is a stub.
  schema.sql           The empty schema: one CREATE TABLE statement and no rows.
                       Read it before you write any PHP.
  README.txt           This file.

There is no database file in this folder, and there should not be. SQLite keeps a
whole database in one ordinary file, and app.db appears next to your script the
first time your connection code runs.

These are starter files, not a solution. Start the server in this folder with

  php -S localhost:8000

and open http://localhost:8000/tasks.php. You get the page and an empty list,
because listTasks() is still a stub that returns nothing.

Running php tasks.php from the terminal works too and behaves as a first visit.


WHAT TO BUILD

A small CRUD task manager on top of SQLite and PDO. This is the capstone. It
brings in durable database persistence while keeping the database local and the
schema small.

One row looks like this:

  id          1
  title       Practice prepared statements
  done        0
  created_at  2026-01-06T09:15:02+00:00

The id is generated by SQLite, not by the request. The created_at timestamp is
generated by the server, not by the request. Both are server facts, for the same
reason the price is in Project 3.


BUILD MILESTONES

  1. Create a SQLite database and a tasks table with id, title, done, and
     created_at fields. Let SQLite generate the id with INTEGER PRIMARY KEY and
     read it back with $pdo->lastInsertId(). The statement is in schema.sql.
  2. Configure PDO to throw exceptions and initialize the schema if it does not
     yet exist.
  3. Write functions or a small repository class for create, list, update
     completion, and delete operations.
  4. Use prepared statements for every external data value. Never concatenate
     request data into SQL.
  5. Build a page that lists tasks and contains a POST form for creating a task.
  6. Validate ids and titles before calling persistence code.
  7. Escape task titles when rendering HTML even though SQL used parameters.
  8. Use POST actions for changes, and redirect after successful changes if you
     implement headers correctly.
  9. Exercise the failure paths with missing ids, missing records, invalid
     titles, and a deliberately inaccessible database location, in a disposable
     copy of the folder.

Milestone 2 is worth doing before anything else, because
PDO::ERRMODE_EXCEPTION is what turns a silent wrong answer into a message that
names the problem. The statement in schema.sql is written with IF NOT EXISTS, so
it is safe to run on every request and the first run needs no separate setup
step.


ACCEPTANCE TESTS

  Create            A valid title inserts exactly one row.
  List              Rows render with escaped titles and stable ids.
  Update            A known id toggles or sets completion using a parameterized
                    statement.
  Delete            A known id is removed; a missing id produces a controlled
                    result.
  Injection attempt A SQL-looking title remains data and does not alter the
                    query structure.

For the injection attempt, create a task titled

  '; DROP TABLE tasks; --

and then reload the page. The task appears in the list, spelled exactly like
that, and the table is still there. That is what a prepared statement is for. If
you have built the insert by concatenating the title into the SQL string
instead, this test is how you find out.

For Delete and Update, use rowCount() to tell a real change from a request for a
record that does not exist. Without it, deleting a task that was never there
looks exactly like deleting one that was, and the page reports a success that did
not happen.


TWO BOUNDARIES

Prepared statements protect SQL data values. Escaping with htmlspecialchars()
protects HTML output. Neither one is a replacement for the other.

A title of <b>Read Appendix A</b> goes into the database faithfully, because
that is what the user meant and the database should hold it. That same title
then needs escaping on the way into the page. If you escape on the way in
instead, the stored data carries HTML entities around with it forever, and it is
wrong the first time you need it anywhere but a web page.


STRETCH GOALS

  - Add due dates with validation.
  - Add filtering for open and completed tasks using a controlled allow-list. A
    column name is not a value, so it cannot be a placeholder, which is exactly
    why the allow-list is needed.
  - Wrap related multi-step writes in a transaction with beginTransaction(),
    commit(), and rollBack().


THE CAPSTONE REVIEW

Once it works, take away one assumption. Delete app.db and reload. Point the
database path at a folder that does not exist, and read the PDOException.
Submit a form with the id field removed. Predict the path first, run it, then
strengthen the program.
