Adding and listing posts
Add and list posts with INSERT and SELECT.
Adding posts with prepare and execute
To add a new blog post you prepare an INSERT statement. First you build a statement object with $pdo->prepare("INSERT INTO posts ..."), then bind named parameters and run the query with $stmt->execute([...]). This is safe against SQL injection because values are sent as parameters, not interpolated strings.
To get the new row's id you call $pdo->lastInsertId(); the value is cast to int and returned.
Listing all posts
To fetch all blog posts you run a SELECT query. For queries without parameters, $pdo->query() returns a statement object directly; you then call $stmt->fetchAll(PDO::FETCH_ASSOC) to get each row as an associative array keyed by column name. The result is an array you can loop over with foreach.
Add ORDER BY id to sort, so posts appear in insertion order.
Exercise Write the createPost and getAllPosts functions
Write createPost(PDO $pdo, string $title, string $content, string $author): int. Inside, call prepare("INSERT INTO posts (title, content, author) VALUES (:title, :content, :author)"), run it with execute([":title" => $title, ...]) and return (int) $pdo->lastInsertId();. Write getAllPosts(PDO $pdo): array: call query("SELECT id, title, content, author FROM posts ORDER BY id") and return $stmt->fetchAll(PDO::FETCH_ASSOC);. Produce the demo output.
Show example solution
int { ... }. Inside, write $stmt = $pdo->prepare("INSERT INTO posts (title, content, author) VALUES (:title, :content, :author)");, run it with $stmt->execute([":title" => $title, ":content" => $content, ":author" => $author]); and return (int) $pdo->lastInsertId();. Write function getAllPosts(PDO $pdo): array { ... }. Inside, write $stmt = $pdo->query("SELECT id, title, content, author FROM posts ORDER BY id"); and return $stmt->fetchAll(PDO::FETCH_ASSOC);.
Example solution:
<?php
// Blog project: BlogPost + setupDatabase + createPost + getAllPosts
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);
}
// Demo:
$pdo = setupDatabase();
createPost($pdo, "First post", "Hello world", "Alex");
createPost($pdo, "Second post", "Hi there", "Mary");
$posts = getAllPosts($pdo);
foreach ($posts as $p) {
echo $p['title'] . " - " . $p['author'] . "\n";
}Test yourself
-
Complete the fetchAll call
return $stmt->fetchAll(____);Show answer
return $stmt->fetchAll(PDO::FETCH_ASSOC); -
Why do we use prepare + execute for INSERT?
- It makes the query faster
- It creates the table automatically
- It provides secure parameter binding against SQL injection
- It prints results automatically
Show answer
It provides secure parameter binding against SQL injection — Correct. prepare + execute sends values as parameters, preventing SQL injection.
-
Match the INSERT and SELECT concepts
- INSERT INTO
- Adds a new row
- SELECT ... FROM
- Reads rows
- ->execute
- Runs the query
- :title
- Named parameter