Database structure
Create the posts table and the PDO connection.
Starting with an in-memory database
In the blog project you'll store data in an in-memory SQLite database. You open a PDO connection with new PDO("sqlite::memory:"). The ::memory: suffix tells PDO to keep data in process memory instead of a file, so every test run starts with a clean table and no file is created.
After connecting you run a CREATE TABLE statement with $pdo->exec() to create the posts table. Each post has id, title, content and author columns.
Creating the posts table
The setupDatabase() function opens the connection and prepares the table. The table structure: id as an auto-incrementing primary key, plus title, content and author text columns. You run DDL statements with $pdo->exec(); it is less flexible than prepare but fits table definitions.
To catch errors early you set $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION), so a faulty query throws an exception instead of failing silently.
Exercise Write the setupDatabase function
Write a setupDatabase(): PDO function. Inside, connect with new PDO("sqlite::memory:"), set the error mode with setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION), create the posts table with $pdo->exec("CREATE TABLE posts (...)") and return $pdo;. The table should have id (primary key), title, content and author columns. Produce the demo output.
Show example solution
PDO { ... }. Inside, connect with $pdo = new PDO("sqlite::memory:");, set the error mode with $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);, create the table with $pdo->exec("CREATE TABLE posts (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, content TEXT NOT NULL, author TEXT NOT NULL)"); and return $pdo;.
Example solution:
<?php
// Blog project: BlogPost class + setupDatabase
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;
}
// Demo:
$pdo = setupDatabase();
$count = $pdo->query("SELECT COUNT(*) FROM posts")->fetchColumn();
echo "Records: " . $count;Test yourself
-
When is sqlite::memory: useful?
- To test quickly without creating a file
- To start with a clean table on every test run
- To set up a persistent file database
- To connect to a remote server
Show answer
To start with a clean table on every test run — Correct. An in-memory database starts fresh on every run, so tests are deterministic.
-
Complete the DSN string
$pdo = new PDO("____");Show answer
$pdo = new PDO("sqlite::memory:"); -
Match the PDO methods
- new PDO
- Creates connection
- ->exec
- Runs DDL
- ->prepare
- Prepares query
- ->fetchAll
- Fetches all rows