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

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

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

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

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.

Practice

1. Which PHP PDO fetch mode returns each row as an associative array with column names as keys?

easy

A. PDO::FETCH_OBJ

B. PDO::FETCH_NUM

C. PDO::FETCH_ASSOC

D. PDO::FETCH_COLUMN

Solution

1. Step 1: Understand fetch modes

   PDO::FETCH_ASSOC returns rows as associative arrays with column names as keys.

2. Step 2: Compare with other modes

   PDO::FETCH_NUM returns numeric arrays, PDO::FETCH_OBJ returns objects, PDO::FETCH_COLUMN returns a single column value.

3. Final Answer:

   PDO::FETCH_ASSOC -> Option C

4. Quick Check:

   Associative array = PDO::FETCH_ASSOC [OK]

Hint: Associative arrays use PDO::FETCH_ASSOC for column-name keys [OK]

Common Mistakes:

• Confusing PDO::FETCH_NUM with associative arrays
• Using PDO::FETCH_OBJ when array is needed
• Thinking PDO::FETCH_COLUMN returns full rows

2. Which of the following is the correct syntax to fetch a single row as an object using PDO?

easy

A. $stmt->fetch(PDO::FETCH_OBJ);

B. $stmt->fetch(PDO::FETCH_ASSOC);

C. $stmt->fetchAll(PDO::FETCH_OBJ);

D. $stmt->fetchColumn();

Solution

1. Step 1: Identify fetch method for single row as object

   fetch() returns one row; PDO::FETCH_OBJ returns it as an object.

2. Step 2: Check other options

   fetchAll() returns all rows, fetchColumn() returns a single column value, fetch(PDO::FETCH_ASSOC) returns an associative array.

3. Final Answer:

   $stmt->fetch(PDO::FETCH_OBJ); -> Option A

4. Quick Check:

   Single row object = fetch(PDO::FETCH_OBJ) [OK]

Hint: Use fetch(PDO::FETCH_OBJ) for one row as object [OK]

Common Mistakes:

• Using fetchAll() when only one row is needed
• Using fetchColumn() to get full row
• Confusing FETCH_ASSOC with FETCH_OBJ

3. What will be the output of the following PHP code?

$stmt = $pdo->query('SELECT id, name FROM users LIMIT 1');
$row = $stmt->fetch(PDO::FETCH_NUM);
echo $row[0] . ', ' . $row[1];

medium

A. id, name

B. 1, Alice

C. Array, Array

D. Error: Undefined index

Solution

1. Step 1: Understand PDO::FETCH_NUM

   fetch(PDO::FETCH_NUM) returns a numeric array with column values indexed 0,1,...

2. Step 2: Analyze echo statement

   $row[0] is the first column (id), $row[1] is the second column (name). Assuming first user has id=1 and name='Alice', output is '1, Alice'.

3. Final Answer:

   1, Alice -> Option B

4. Quick Check:

   Numeric array indexes = 0,1 values [OK]

Hint: FETCH_NUM returns numeric keys, use indexes 0,1 for columns [OK]

Common Mistakes:

• Expecting column names as keys with FETCH_NUM
• Using associative keys with numeric array
• Assuming fetchColumn returns full row

4. Identify the error in this PHP code snippet that fetches all user names as a single column array:

$stmt = $pdo->query('SELECT name FROM users');
$names = $stmt->fetch(PDO::FETCH_COLUMN);
print_r($names);

medium

A. print_r cannot print arrays

B. PDO::FETCH_COLUMN is invalid fetch mode

C. Query should select id, not name

D. fetch() returns only one value, should use fetchAll()

Solution

1. Step 1: Understand fetch() vs fetchAll()

   fetch() returns a single row or value, fetchAll() returns all rows.

2. Step 2: Check usage of FETCH_COLUMN

   FETCH_COLUMN fetches a single column value per row; to get all names, fetchAll(PDO::FETCH_COLUMN) is needed.

3. Final Answer:

   fetch() returns only one value, should use fetchAll() -> Option D

4. Quick Check:

   fetchAll(PDO::FETCH_COLUMN) gets all column values [OK]

Hint: Use fetchAll(PDO::FETCH_COLUMN) to get all values of one column [OK]

Common Mistakes:

• Using fetch() instead of fetchAll() for multiple rows
• Thinking FETCH_COLUMN fetches all rows automatically
• Misunderstanding print_r usage

5. You want to fetch all rows from a database as objects but only include rows where the 'active' column is true. Which PHP code snippet correctly applies this filter using fetch modes and styles?

hard

A. $rows = $stmt->fetchAll(PDO::FETCH_OBJ); $activeRows = array_filter($rows, fn($r) => $r->active);

B. $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $r) { if ($r['active']) echo $r; }

C. $rows = $stmt->fetchAll(PDO::FETCH_COLUMN); $activeRows = array_filter($rows, fn($r) => $r->active);

D. $rows = $stmt->fetch(PDO::FETCH_OBJ); while ($rows->active) { echo $rows; }

Solution

1. Step 1: Fetch all rows as objects

   fetchAll(PDO::FETCH_OBJ) returns an array of objects, each representing a row.

2. Step 2: Filter rows where 'active' is true

   array_filter with a callback checks the 'active' property on each object, keeping only those with true.

3. Step 3: Check other options

   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach ($rows as $r) { if ($r['active']) echo $r; } uses associative arrays but does not collect filtered rows properly; $rows = $stmt->fetchAll(PDO::FETCH_COLUMN); $activeRows = array_filter($rows, fn($r) => $r->active); uses FETCH_COLUMN which returns single column values, not objects; $rows = $stmt->fetch(PDO::FETCH_OBJ); while ($rows->active) { echo $rows; } misuses fetch() and while loop.

4. Final Answer:

   $rows = $stmt->fetchAll(PDO::FETCH_OBJ); $activeRows = array_filter($rows, fn($r) => $r->active); -> Option A

5. Quick Check:

   fetchAll(PDO::FETCH_OBJ) + array_filter for property check [OK]

Hint: Use fetchAll(PDO::FETCH_OBJ) then array_filter by property [OK]

Common Mistakes:

• Using fetch() instead of fetchAll() for multiple rows
• Using FETCH_COLUMN when objects are needed
• Trying to filter before fetching all rows