Editing and deleting posts

Edit and delete existing posts with UPDATE and DELETE.

Fetching a single post

To fetch a post by its id you prepare a SELECT ... WHERE id = :id statement. You build the statement with prepare, bind the parameter with execute([":id" => $id]) and fetch one row with $stmt->fetch(PDO::FETCH_ASSOC). fetch returns one row, or false when no row matches.

For a safe default you use the ?: [] operator: if no row is found an empty array is returned, so the calling code does not break.

Updating and deleting

To update a post you prepare an UPDATE posts SET title = :title, content = :content, author = :author WHERE id = :id statement and run it with execute. To delete you use DELETE FROM posts WHERE id = :id. In both cases $stmt->rowCount() tells you how many rows were affected, which is handy for verification.

Always bind parameters with execute; never interpolate values into the string. That way even user-supplied data stays safe.

Exercise Write the getPost, updatePost and deletePost functions

Write three functions. getPost(PDO $pdo, int $id): array: call prepare("SELECT id, title, content, author FROM posts WHERE id = :id"), run with execute([":id" => $id]), return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];. updatePost(PDO $pdo, int $id, string $title, string $content, string $author): int: prepare("UPDATE posts SET title = :title, content = :content, author = :author WHERE id = :id"), run with execute([...]), return $stmt->rowCount();. deletePost(PDO $pdo, int $id): int: prepare("DELETE FROM posts WHERE id = :id"), run with execute([":id" => $id]), return $stmt->rowCount();. Produce the demo output.

Show example solution

array { ... }. Inside, write $stmt = $pdo->prepare("SELECT id, title, content, author FROM posts WHERE id = :id");, run it with $stmt->execute([":id" => $id]); and return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];. Write function updatePost(PDO $pdo, int $id, string $title, string $content, string $author): int { ... }. Inside, call $pdo->prepare("UPDATE posts SET title = :title, content = :content, author = :author WHERE id = :id");, run it with execute([...]) and return $stmt->rowCount();. Write function deletePost(PDO $pdo, int $id): int { ... }. Inside, call $pdo->prepare("DELETE FROM posts WHERE id = :id");, run it with execute([":id" => $id]); and return $stmt->rowCount();.
Example solution:
<?php
// Blog project: BlogPost + setupDatabase + createPost + getAllPosts + getPost + updatePost + deletePost

class BlogPost {
    public string $title = "";
    public string $content = "";
    public string $author = "";
}

function setupDatabase(): PDO {
    $pdo = new PDO("sqlite::memory:");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->exec("CREATE TABLE posts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        author TEXT NOT NULL
    )");
    return $pdo;
}

function createPost(PDO $pdo, string $title, string $content, string $author): int {
    $stmt = $pdo->prepare("INSERT INTO posts (title, content, author) VALUES (:title, :content, :author)");
    $stmt->execute([":title" => $title, ":content" => $content, ":author" => $author]);
    return (int) $pdo->lastInsertId();
}

function getAllPosts(PDO $pdo): array {
    $stmt = $pdo->query("SELECT id, title, content, author FROM posts ORDER BY id");
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

function getPost(PDO $pdo, int $id): array {
    $stmt = $pdo->prepare("SELECT id, title, content, author FROM posts WHERE id = :id");
    $stmt->execute([":id" => $id]);
    return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
}

function updatePost(PDO $pdo, int $id, string $title, string $content, string $author): int {
    $stmt = $pdo->prepare("UPDATE posts SET title = :title, content = :content, author = :author WHERE id = :id");
    $stmt->execute([":title" => $title, ":content" => $content, ":author" => $author, ":id" => $id]);
    return $stmt->rowCount();
}

function deletePost(PDO $pdo, int $id): int {
    $stmt = $pdo->prepare("DELETE FROM posts WHERE id = :id");
    $stmt->execute([":id" => $id]);
    return $stmt->rowCount();
}

// Demo:
$pdo = setupDatabase();
createPost($pdo, "First post", "Hello", "Alex");
createPost($pdo, "Second post", "Hi", "Mary");

$post = getPost($pdo, 1);
echo $post['title'] . " - " . $post['author'] . "\n";

updatePost($pdo, 1, "Updated post", "New content", "Alex");
echo "Updated\n";

$deleted = deletePost($pdo, 2);
echo "Deleted: " . $deleted . "\n";

$posts = getAllPosts($pdo);
foreach ($posts as $p) {
    echo $p['title'] . " - " . $p['author'] . "\n";
}

Test yourself

Match the UPDATE and DELETE concepts

UPDATE ... SET

Updates a row
DELETE FROM

Deletes a row
WHERE id = :id

Targets a specific row
->rowCount

Affected row count
Why is WHERE important in UPDATE and DELETE queries?
- To affect all rows
- To speed up the query
- To specify the table name
- To target only a specific row
Show answer

To target only a specific row — Correct. Without WHERE, UPDATE/DELETE affect every row, which is rarely intended.

Complete the UPDATE query

$stmt = $pdo->prepare("____");

Show answer

$stmt = $pdo->prepare("UPDATE posts SET title = :title WHERE id = :id");

Next lesson: Project final - Blog with PDO

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies