Fetch modes and styles in PHP - Mini Project: Build & Apply

Fetch modes and styles

📖 Scenario: You are working with a database of books in a library. You want to fetch data from the database using different fetch modes to see how the data is returned in PHP.

🎯 Goal: Learn how to use different PDO fetch modes to retrieve data from a database and understand how the data structure changes with each mode.

📋 What You'll Learn

Create a PDO connection to an SQLite in-memory database

Create a table called books with columns id, title, and author

Insert three books into the books table

Fetch data using different PDO fetch modes: PDO::FETCH_ASSOC, PDO::FETCH_OBJ, and PDO::FETCH_NUM

Print the fetched results to see the differences

💡 Why This Matters

🌍 Real World

Fetching data from databases in different formats is common when building web applications or APIs. Knowing fetch modes helps you choose the best way to work with data.

💼 Career

Database interaction is a key skill for backend developers. Understanding PDO fetch modes is important for writing clean, efficient, and readable PHP code.

1

Create the database and books table

Create a PDO connection called $pdo to an SQLite in-memory database using new PDO("sqlite::memory:"). Then create a table called books with columns id (integer primary key), title (text), and author (text).

PHP

<?php
// Create PDO connection and books table
# Your code here

Need a hint?

Use new PDO("sqlite::memory:") to create the database connection. Use exec method to run the CREATE TABLE SQL.

2

Insert three books into the books table

Insert these three books into the books table using $pdo->exec(): (1, '1984', 'George Orwell'), (2, 'To Kill a Mockingbird', 'Harper Lee'), and (3, 'The Great Gatsby', 'F. Scott Fitzgerald').

PHP

exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)");
// Insert three books into the books table
# Your code here

Need a hint?

Use three separate exec calls to insert each book with exact values.

3

Fetch data using PDO::FETCH_ASSOC

Write code to prepare and execute a query to select all columns from books. Then fetch all results using fetchAll(PDO::FETCH_ASSOC) into a variable called $booksAssoc.

PHP

exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (1, '1984', 'George Orwell')");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee')");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald')");
// Prepare and execute query, fetch all with PDO::FETCH_ASSOC
# Your code here

Need a hint?

Use prepare and execute methods on $pdo. Then use fetchAll(PDO::FETCH_ASSOC) to get associative arrays.

4

Fetch data using PDO::FETCH_OBJ and PDO::FETCH_NUM and print results

Fetch all books again using fetchAll(PDO::FETCH_OBJ) into $booksObj and using fetchAll(PDO::FETCH_NUM) into $booksNum. Then print $booksAssoc, $booksObj, and $booksNum using print_r() to see the differences.

PHP

exec("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT)");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (1, '1984', 'George Orwell')");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (2, 'To Kill a Mockingbird', 'Harper Lee')");
$pdo->exec("INSERT INTO books (id, title, author) VALUES (3, 'The Great Gatsby', 'F. Scott Fitzgerald')");
$stmt = $pdo->prepare("SELECT * FROM books");
$stmt->execute();
$booksAssoc = $stmt->fetchAll(PDO::FETCH_ASSOC);
// Fetch all books with PDO::FETCH_OBJ into $booksObj
// Fetch all books with PDO::FETCH_NUM into $booksNum
// Print $booksAssoc, $booksObj, and $booksNum
# Your code here

Need a hint?

Remember to call execute() again before each fetchAll call because the statement cursor moves after fetching. Use print_r() to display the arrays and objects.