Fetch modes and styles in PHP - Step-by-Step Execution

Concept Flow - Fetch modes and styles

Connect to DB

↓

Execute Query

↓

Fetch Data

↓

Fetch as Associative Array

↓

Fetch as Numeric Array

↓

Fetch as Object

↓

Fetch Both

↓

Fetch into Class

↓

Use Data

This flow shows how after running a query, PHP fetches data in different styles or modes, like arrays or objects, to use in your program.

Execution Sample

PHP

<?php
$stmt = $pdo->query('SELECT id, name FROM users');
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'];
?>

This code fetches one row from the database as an associative array and prints the 'name' field.

Execution Table

Step	Action	Fetch Mode	Data Returned	Output
1	Execute query	-	-	-
2	Fetch first row	PDO::FETCH_ASSOC	{'id' => 1, 'name' => 'Alice'}	Alice
3	Fetch second row	PDO::FETCH_ASSOC	{'id' => 2, 'name' => 'Bob'}	-
4	Fetch third row	PDO::FETCH_ASSOC	false (no more rows)	-

💡 No more rows to fetch, fetch() returns false and loop ends

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4
$row	null	{'id' => 1, 'name' => 'Alice'}	{'id' => 2, 'name' => 'Bob'}	false

Key Moments - 3 Insights

Why does fetch() return false at the end?

fetch() returns false when there are no more rows to get from the query result, as shown in step 4 of the execution_table.

What is the difference between FETCH_ASSOC and FETCH_NUM?

FETCH_ASSOC returns data as an associative array with column names as keys, while FETCH_NUM returns a numeric array with column indexes, shown by the 'Fetch Mode' column in the execution_table.

Can I fetch data as an object?

Yes, using FETCH_OBJ mode, fetch() returns each row as an object with properties matching column names.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of $row after step 3?

A {'id' => 2, 'name' => 'Bob'}

B {'id' => 1, 'name' => 'Alice'}

C false

D null

💡 Hint

Check the 'Data Returned' column for step 3 in execution_table.

At which step does fetch() return false indicating no more rows?

A Step 2

B Step 3

C Step 4

D Never

💡 Hint

Look at the 'Data Returned' column in execution_table for the step where fetch() returns false.

If we change fetch mode to PDO::FETCH_NUM, how would the data look?

A Associative array with column names as keys

B Numeric array with column indexes as keys

C Object with properties

D Boolean false

💡 Hint

Recall the difference between FETCH_ASSOC and FETCH_NUM explained in key_moments.

Concept Snapshot

PHP fetch modes control how query results are returned.
Common modes:
- FETCH_ASSOC: associative array (column names)
- FETCH_NUM: numeric array (indexes)
- FETCH_OBJ: object with properties
fetch() returns false when no rows left.

Full Transcript

This visual execution shows how PHP fetch modes work when getting data from a database. After running a query, fetch() gets one row at a time. Using FETCH_ASSOC mode, each row is an associative array with column names as keys. The variable $row changes each fetch call. When no rows remain, fetch() returns false, ending the loop. Different fetch modes change the data format: associative arrays, numeric arrays, or objects. Understanding these helps you use database results easily in your PHP code.

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