PHP PDO INSERT, UPDATE, DELETE

Once you can connect to a database with PDO, the next step is changing data: adding new rows, editing existing ones, and removing rows you no longer need. PDO handles all three with the same core tool — prepared statements — which keep your queries safe from SQL injection and let the database engine reuse the same execution plan across repeated calls. This lesson walks through INSERT, UPDATE, and DELETE in PDO from the ground up, including transactions, error handling, and the mistakes that trip up almost every beginner.

Overview / How PDO Writes to a Database

PDO (PHP Data Objects) is a database access layer that gives you a consistent, object-oriented API regardless of which database driver sits underneath (MySQL, PostgreSQL, SQLite, and so on). For write operations — INSERT, UPDATE, DELETE — you have two main options on a PDO object:

PDO::exec() — runs a SQL statement immediately and returns the number of affected rows. Use it only for queries that contain no user-supplied data, because it does not support placeholders.
PDO::prepare() + PDOStatement::execute() — compiles the SQL once with placeholders, then sends the actual values separately. This is the correct, safe way to run any query that includes variable data.

When you call prepare(), PDO (or the underlying driver, depending on emulation settings) parses the SQL and replaces each placeholder — ? or :name — with a slot the database understands. When you call execute(), the actual values are sent as pure data, never as part of the SQL text. That separation is what stops SQL injection: even if a value contains something like ' OR '1'='1, the database treats it as a literal string value to insert or compare, not as SQL syntax to execute.

Under the hood, MySQL’s INSERT assigns a new auto-increment ID (if the table has one), which PDO exposes via lastInsertId(). UPDATE and DELETE don’t create IDs, but PDO exposes how many rows were affected via PDOStatement::rowCount() — critical for confirming a write actually did something, since a query with zero matching rows still “succeeds” without error.

Syntax

$stmt = $pdo->prepare("INSERT INTO table_name (col1, col2) VALUES (:col1, :col2)");
$stmt->execute([':col1' => $value1, ':col2' => $value2]);
$newId = $pdo->lastInsertId();

$stmt = $pdo->prepare("UPDATE table_name SET col1 = :col1 WHERE id = :id");
$stmt->execute([':col1' => $value1, ':id' => $id]);
$affected = $stmt->rowCount();

$stmt = $pdo->prepare("DELETE FROM table_name WHERE id = :id");
$stmt->execute([':id' => $id]);
$deleted = $stmt->rowCount();

Part	Meaning
`prepare(sql)`	Compiles the SQL with placeholders; returns a `PDOStatement` object.
`:name`	A named placeholder. Bound values are passed by key in an associative array, or via `bindValue()`.
`?`	A positional placeholder. Bound values are passed in order in an indexed array.
`execute(array)`	Binds the given values to the placeholders and runs the statement.
`lastInsertId()`	Returns the auto-increment ID generated by the most recent `INSERT` on this connection.
`rowCount()`	Returns the number of rows affected by the last `INSERT`, `UPDATE`, or `DELETE`.

Examples

Example 1: Inserting a new row

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('INSERT INTO students (name, email, grade) VALUES (:name, :email, :grade)');
$stmt->execute([
    ':name' => 'Ava Thompson',
    ':email' => 'ava@example.com',
    ':grade' => 9,
]);

echo "New student ID: " . $pdo->lastInsertId();

Output:
New student ID: 42

The :name, :email, and :grade placeholders keep the SQL text fixed while the values are supplied separately in the array passed to execute(). After the insert, lastInsertId() retrieves the auto-increment value MySQL just assigned to the new row.

Example 2: Updating a row and checking rowCount()

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('UPDATE students SET grade = :grade WHERE id = :id');
$stmt->execute([':grade' => 10, ':id' => 42]);

echo "Rows updated: " . $stmt->rowCount();

Output:
Rows updated: 1

The WHERE id = :id clause limits the update to a single row. rowCount() confirms exactly one row matched and changed — if the ID didn’t exist, this would print 0 even though no error was thrown, which is why checking it matters.

Example 3: Deleting rows with positional placeholders

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('DELETE FROM students WHERE grade < ? AND active = ?');
$stmt->execute([5, 0]);

echo "Rows deleted: " . $stmt->rowCount();

Output:
Rows deleted: 3

This uses ? placeholders instead of named ones. The values in the execute() array are bound strictly in order: the first ? gets 5, the second gets 0. Positional placeholders are slightly terser but easier to mix up in longer queries, so many developers prefer named placeholders once a query has more than two or three parameters.

Example 4: Combining INSERT and UPDATE in a transaction

<?php
$pdo = new PDO('mysql:host=localhost;dbname=school;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

try {
    $pdo->beginTransaction();

    $insert = $pdo->prepare('INSERT INTO orders (customer_id, total) VALUES (:customer_id, :total)');
    $insert->execute([':customer_id' => 7, ':total' => 149.99]);
    $orderId = $pdo->lastInsertId();

    $update = $pdo->prepare('UPDATE customers SET last_order_id = :order_id WHERE id = :customer_id');
    $update->execute([':order_id' => $orderId, ':customer_id' => 7]);

    $pdo->commit();
    echo "Order #$orderId created and customer updated.";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

Output:
Order #15 created and customer updated.

When two writes must succeed or fail together — here, creating an order and stamping the customer’s record with its ID — wrap them in a transaction. beginTransaction() tells MySQL to hold the changes uncommitted; if anything throws (because ERRMODE_EXCEPTION is set), the catch block calls rollBack() and neither write takes effect. If both succeed, commit() makes them permanent together.

How It Works Step by Step

1. Prepare — $pdo->prepare($sql) sends the SQL skeleton (with placeholders) to the driver. By default PDO emulates prepared statements client-side for MySQL, but it still separates SQL structure from data at the PHP level.
2. Bind — the array passed to execute() (or explicit bindValue() calls) maps each placeholder to a real value, along with an inferred or explicit type (string, integer, and so on).
3. Execute — the statement, now filled in with bound data, is sent to MySQL and run. MySQL parses it as one unit; the bound values can never be reinterpreted as SQL keywords or operators.
4. Result — for INSERT, the server returns the new auto-increment ID (retrievable via lastInsertId()). For UPDATE/DELETE, it returns the count of affected rows, retrievable via rowCount().
5. Commit or rollback — if you’re inside a transaction, nothing is durable until commit() runs; rollBack() discards every change made since beginTransaction().

Common Mistakes

Mistake 1: Concatenating user input into SQL

$name = $_POST['name'];
$email = $_POST['email'];

// DANGEROUS: raw user input concatenated into the SQL string
$pdo->exec("INSERT INTO students (name, email) VALUES ('$name', '$email')");
echo "Student added (unsafely).";

This builds the query by splicing untrusted input straight into the SQL text. Anyone who submits a name like '); DROP TABLE students; -- can alter the query’s meaning entirely. PDO::exec() also offers no placeholder mechanism at all, so this pattern has no safe form — it should never be used with variable data.

Corrected:

$name = $_POST['name'];
$email = $_POST['email'];

$stmt = $pdo->prepare('INSERT INTO students (name, email) VALUES (:name, :email)');
$stmt->execute([':name' => $name, ':email' => $email]);
echo "Student added safely.";

Now the values travel to MySQL as data, not as SQL text, regardless of what characters they contain.

Mistake 2: Forgetting the WHERE clause

$idToRemove = 12;

// Forgot the WHERE clause — this deletes EVERY row in the table
$pdo->exec('DELETE FROM students');
echo "Deleted student.";

Without a WHERE clause, DELETE FROM students removes every row in the table, not just the intended one — and UPDATE without WHERE is just as destructive, overwriting every row’s column. There is no error to warn you; the query is perfectly valid SQL, just not what you meant.