Challenge - PDO and API
Build a secure product API with PDO, prepared statements and JSON.
Project: Product API
In this five-lesson final project you'll build a mini application that combines every advanced topic. Each lesson adds one piece, and by the final lesson you'll complete a working core.
In this lesson you'll design a ProductRepository class: it creates the products table with PDO, inserts two products and returns them all through an all() method. Then a handleGet function prints the products as structured data with json_encode. PDO::FETCH_ASSOC returns each row as an associative array keyed by column name.
Structured data response
You use json_encode to send API responses as structured data. Pass JSON_UNESCAPED_UNICODE as the second argument and non-ASCII letters stay readable; "é" remains "é" instead of "\u00e9".
The handleGet(PDO $pdo): string signature takes a PDO connection and returns a structured data string. Inside, you create a ProductRepository instance and wrap the result of all() with json_encode. The sqlite::memory: DSN produces the same output on every run with no file on disk.
Exercise Write ProductRepository and handleGet
Write the first piece of this project. Define a class named ProductRepository. Give it a private PDO $pdo property and a constructor that takes a PDO; inside the constructor create the products table (id, name, price columns) and insert two products: Kale price 5, Defter price 15. An all(): array method returns products keyed by id, name, price (id and price must be integers).
Then write handleGet(PDO $pdo): string: create a ProductRepository instance, wrap the all() result as ["products" => ...] and return it with json_encode using the JSON_UNESCAPED_UNICODE flag. In the demo connect with sqlite::memory: and print the handleGet output.
Show example solution
:FETCH_ASSOC) and array_map to cast id and price with (int). In handleGet(PDO $pdo): string return json_encode(["products" => $repo->all()], JSON_UNESCAPED_UNICODE). Finally $pdo = new PDO("sqlite::memory:"); echo handleGet($pdo);
Example solution:
<?php
// Product API: ProductRepository + handleGet
class ProductRepository {
private PDO $pdo;
public function __construct(PDO $pdo) {
$this->pdo = $pdo;
$this->pdo->exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER)");
$stmt = $this->pdo->prepare("INSERT INTO products (name, price) VALUES (?, ?)");
$stmt->execute(["Kale", 5]);
$stmt->execute(["Defter", 15]);
}
public function all(): array {
$rows = $this->pdo->query("SELECT id, name, price FROM products ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
return array_map(fn($r) => [
"id" => (int)$r["id"],
"name" => $r["name"],
"price" => (int)$r["price"]
], $rows);
}
}
function handleGet(PDO $pdo): string {
$repo = new ProductRepository($pdo);
return json_encode(["products" => $repo->all()], JSON_UNESCAPED_UNICODE);
}
$pdo = new PDO("sqlite::memory:");
echo handleGet($pdo);Test yourself
-
How does PDO::FETCH_ASSOC return each row?
- Only with column indexes (0, 1, 2...)
- With both column names and indexes
- As an associative array with column names
- As an object (same as PDO::FETCH_OBJ)
Show answer
As an associative array with column names — Correct. FETCH_ASSOC returns each row as an associative array keyed by column name.
-
Complete the json_encode flag
echo json_encode(["products" => $rows], ____);Show answer
echo json_encode(["products" => $rows], JSON_UNESCAPED_UNICODE); -
Match PDO and structured data concepts
- PDO
- Database abstraction layer
- prepare
- Prepared statement
- fetchAll
- Returns all rows
- json_encode
- Encodes array as structured data