Skip to content
Tutorials Center
My learning

Topics

Lesson 90 of 147 ยท Databases

CRUD with PDO

Build complete CRUD with PHP and PDO: insert rows and get lastInsertId, select one or many, update and delete with rowCount, and wrap it all in a repository class.

CRUD stands for the four things almost every application does with its data: Create, Read, Update and Delete. A to-do app creates tasks, lists them, marks them done and removes them. A shop creates orders, shows them, changes their status and cancels them. In SQL, the four operations are INSERT, SELECT, UPDATE and DELETE.

You already know the tools: PDO Basics showed how to connect and fetch, and Prepared Statements and SQL Injection showed how to pass values safely. This lesson combines them into the everyday patterns: getting the ID of a new row, loading one record or a filtered list, updating only the fields that changed, finding out how many rows a statement affected, and turning a missing row into a proper "not found". At the end, you'll wrap everything in a small repository class, the structure most real applications use.

All examples use a tasks table in an in-memory SQLite database, with the same PDO code you'd write for MySQL or PostgreSQL. Each example creates the table itself, so you can run them in any order.

The table

Here's the schema used throughout the lesson. If the SQL is new to you, CREATE TABLE and Constraints in the SQL tutorial explain each part:

SQL
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,                 -- MySQL: INT AUTO_INCREMENT PRIMARY KEY
    title TEXT NOT NULL,
    priority INTEGER NOT NULL DEFAULT 2,     -- 1 = high, 2 = normal, 3 = low
    done INTEGER NOT NULL DEFAULT 0,         -- 0 or 1 (MySQL: TINYINT(1) or BOOLEAN)
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

In SQLite, an INTEGER PRIMARY KEY column gets a new unique number automatically when you insert a row without it. MySQL uses AUTO_INCREMENT and PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or SERIAL) for the same job; see Auto Increment.

Create: INSERT and lastInsertId()

To insert a row, prepare an INSERT with placeholders and execute it with the values. The database assigns the ID, and $pdo->lastInsertId() tells you which one it picked:

PHP Inserting rows and reading their IDs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Note that lastInsertId() returns a string (string(1) "1"), because some databases use IDs that don't fit PHP's integer type or aren't numbers at all. Cast it with (int) when your IDs are integers. The value belongs to your connection, so other users inserting rows at the same moment can't mix it up.

The last query puts $id straight into the SQL. That's safe only because $id is an integer your own code produced from the database; anything from outside goes through a placeholder.

Getting the whole row back with RETURNING

Often you want more than the ID: the values the database filled in from defaults, like priority and done here, or a created_at timestamp. SQLite (3.35+), PostgreSQL and MariaDB support a RETURNING clause that gives you the inserted row as a result, saving a second query:

PHP INSERT ... RETURNING

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

MySQL doesn't support RETURNING, so portable code uses lastInsertId() and, if needed, a follow-up SELECT.

Read: one row, many rows, or none

Reading comes in two shapes. Find one loads a record by its ID (or another unique column) and must handle the case where it doesn't exist. Find many returns a list, filtered and sorted, which may be empty:

PHP Finding one task and listing many

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Two patterns are worth copying:

  • findTask() converts PDO's false into null and declares the return type ?array. Callers check === null and answer with a 404 in a web app (Building a JSON API). Keeping PDO's false out of your own functions makes their contracts clearer.
  • openTasks() builds the SQL from fixed pieces depending on which filters are set, and collects the matching values in $params. The SQL text never contains user input; only placeholders. This is how search forms with optional filters are built.

Select only the columns you need instead of SELECT *. It sends less data, keeps working when someone adds a large column later, and makes the code show exactly what it uses.

Update: UPDATE and rowCount()

An UPDATE changes rows that match its WHERE clause. $statement->rowCount() returns how many rows it affected, which tells you whether the record existed:

PHP Updating rows and checking rowCount()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

rowCount() is reliable for INSERT, UPDATE and DELETE. Don't use it to count the rows of a SELECT: some drivers (including SQLite) return 0 there. Use COUNT(*) or count($rows) instead.

MySQL counts only rows that actually changed. Setting done = 1 on a task that's already done gives rowCount() === 0 in MySQL, but 1 in SQLite and PostgreSQL, which count every row the WHERE matched. If you need MySQL to report matched rows, connect with the option Pdo\Mysql::ATTR_FOUND_ROWS => true (written PDO::MYSQL_ATTR_FOUND_ROWS before PHP 8.4). Or check existence with a SELECT first.

Updating only some fields

Edit forms and PATCH requests often change just a few fields. You can build the SET clause dynamically, as long as the column names come from an allow-list in your code and the values go through placeholders:

PHP A partial update with an allow-list

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

array_intersect_key() with array_flip(UPDATABLE) keeps only the keys that appear in the allow-list, so the attempts to change id or to sneak SQL in through a key name are dropped before any SQL is built. $statement->queryString holds the SQL text of a prepared statement, which is handy for logging and debugging. The placeholders are named after the columns, and $changes + ['id' => $id] adds the ID for the WHERE clause.

Delete: DELETE and its alternatives

Deleting works the same way as updating, and rowCount() again tells you whether anything was there to delete:

PHP Deleting rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

A DELETE or UPDATE without a WHERE clause affects every row in the table. Double-check the WHERE in every such statement, and be especially careful when it's built dynamically: if all filters are optional and none is set, you may end up with no condition at all.

Many applications avoid real deletes for important data. A soft delete adds a deleted_at column and sets it instead of removing the row; every query then adds WHERE deleted_at IS NULL. Deleted records can be restored, audited, and still satisfy foreign keys from old orders or invoices. In tables linked by foreign keys, the database decides what happens to related rows on delete (ON DELETE CASCADE, RESTRICT, SET NULL).

Putting it together: a repository class

Scattering SQL across controllers and templates makes code hard to change and hard to test. A repository is a class that owns all queries for one kind of record. The rest of the application calls methods like find() and save() and works with typed objects, never with SQL or PDO directly:

PHP A TaskRepository with typed Task objects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

What the repository gives you:

  • One place for SQL. If the table changes, or you move from SQLite to MySQL, you edit one class.
  • Typed results. Callers get Task objects with int, string and bool properties, not loosely typed arrays. Task::fromRow() converts the database's 0/1 into a real bool.
  • Clear failures. A missing task throws a domain exception, TaskNotFound, which a controller can turn into a 404 (Custom Exceptions and Error Handling Strategy). throw works as an expression since PHP 8.0, which keeps find() to one line.
  • Testability. The repository receives its PDO through the constructor (dependency injection), so tests can pass an in-memory SQLite connection, exactly like this demo. OOP Design Principles and Design Patterns in PHP discuss the pattern further.

array_map(Task::fromRow(...), $rows) uses PHP 8.1's first-class callable syntax to pass the static method as a function. Because Task is a readonly class, "completing" a task means saving a new Task with done: true; the Readonly Properties lesson shows how a withDone() method can make that nicer.

ORMs such as Doctrine and Laravel's Eloquent automate exactly this mapping between rows and objects, with relationships, change tracking and migrations on top. Knowing how to write it by hand helps you understand what they do and debug the SQL they generate.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does $pdo->lastInsertId() return after inserting a row whose new ID is 5?

    lastInsertId() always returns a string, because some databases use IDs that aren't PHP integers. Cast it with (int) for integer keys.
  2. 2. UPDATE tasks SET done = 1 WHERE id = 42 runs, but no task 42 exists. What does rowCount() return?

    The statement succeeds but matches no rows, so rowCount() is 0. That's how you detect a missing record after an update or delete.
  3. 3. Why shouldn't you use rowCount() to count the results of a SELECT?

    rowCount() is defined for INSERT, UPDATE and DELETE. For selects, use COUNT(*) in SQL or count() on the fetched array.
  4. 4. A PATCH request may change any of title, priority or done. What's the safe way to build the UPDATE?

    Column names can't be bound, so they must come from your own allow-list. Values still go through placeholders.
  5. 5. What happens when you run DELETE FROM tasks without a WHERE clause?

    Without a WHERE clause, DELETE (and UPDATE) affects every row. Check the condition carefully, especially when it's built dynamically.

Summary

  • CRUD maps to INSERT, SELECT, UPDATE and DELETE, each run as a prepared statement with placeholders.
  • After an INSERT, lastInsertId() returns the new ID as a string; RETURNING (SQLite, PostgreSQL, MariaDB) gives back the whole row.
  • For reads, turn fetch()'s false into null or an exception, build optional filters from fixed SQL pieces plus a parameter array, and select only the columns you need.
  • rowCount() tells you how many rows an UPDATE or DELETE affected (0 means nothing matched); MySQL counts only rows that actually changed. Don't use it for SELECT.
  • Build partial updates from an allow-list of columns, never from user-supplied keys. Watch out for UPDATE/DELETE without WHERE, and consider soft deletes for important data.
  • A repository class keeps all SQL for one table in one place, returns typed objects, throws domain exceptions and receives its PDO through the constructor.

Next: Transactions