Fetching results (fetch, fetchAll) in PHP - Step-by-Step Execution

Concept Flow - Fetching results (fetch, fetchAll)

Execute Query

↓

Fetch One Row?

No→Exit

Yes↓

Return Row Data

↓

Fetch Next Row or fetchAll

↩Back to Fetch One Row or Return All Rows

This flow shows how PHP fetches data from a database query, either one row at a time or all rows at once.

Execution Sample

PHP

<?php
$stmt = $pdo->query('SELECT id, name FROM users');
$row = $stmt->fetch();
$allRows = $stmt->fetchAll();
?>

This code runs a query, fetches one row, then fetches all remaining rows.

Execution Table

Step	Action	Method Called	Result	Notes
1	Run query 'SELECT id, name FROM users'	query()	PDOStatement object	Query executed, ready to fetch
2	Fetch first row	fetch()	Array with first row data	Returns one row as associative array
3	Fetch all remaining rows	fetchAll()	Array of arrays with remaining rows	Returns all rows left in result set
4	Try fetch again	fetch()	false	No more rows, fetch returns false
5	Try fetchAll again	fetchAll()	Empty array	No rows left, returns empty array

💡 No more rows to fetch, fetch returns false and fetchAll returns empty array

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5
$stmt	null	PDOStatement object	PDOStatement object	PDOStatement object	PDOStatement object
$row	null	First row array	First row array	false	false
$allRows	null	null	Array of remaining rows	Array of remaining rows	Empty array

Key Moments - 3 Insights

Why does fetch() return false after fetching all rows?

Because fetch() moves forward one row at a time and returns false when no rows remain, as shown in step 4 of the execution_table.

What does fetchAll() return if called after all rows are fetched?

It returns an empty array since there are no rows left to fetch, as shown in step 5 of the execution_table.

Does fetchAll() include the row already fetched by fetch()?

No, fetchAll() returns only the rows remaining after fetch() has moved the pointer, so the first row is not included, as seen between steps 2 and 3.

Visual Quiz - 3 Questions

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

A An empty array

B false

C An array with the first row data

D PDOStatement object

💡 Hint

Check the 'Result' column in step 2 of execution_table.

At which step does fetch() return false?

A Step 3

B Step 4

C Step 5

D Step 2

💡 Hint

Look at the 'Result' column for fetch() in execution_table steps.

If fetch() was not called before fetchAll(), what would fetchAll() return at step 3?

A All rows from the query

B Only the first row

C An empty array

D false

💡 Hint

Recall that fetchAll() returns all rows left; if none fetched yet, it returns all rows.

Concept Snapshot

PHP fetch() gets one row from query result as an array.
fetchAll() gets all remaining rows as an array of arrays.
fetch() returns false when no rows left.
fetchAll() returns empty array if no rows left.
Use fetch() to process rows one by one.
Use fetchAll() to get all rows at once.

Full Transcript

This visual trace shows how PHP fetches data from a database query using fetch() and fetchAll(). First, the query runs and returns a PDOStatement object. Then fetch() gets the first row as an array. Next, fetchAll() gets all remaining rows as an array of arrays. If fetch() is called again after all rows are fetched, it returns false. If fetchAll() is called again, it returns an empty array. This helps understand how to retrieve data step-by-step or all at once from a database in PHP.

Practice

1. What does the fetch() method do when used with a PDO statement in PHP?

easy

A. Retrieves one row from the query result

B. Retrieves all rows from the query result

C. Executes the SQL query

D. Closes the database connection

Solution

1. Step 1: Understand the purpose of fetch()

   The fetch() method is designed to get a single row from the result set of a database query.

2. Step 2: Compare with other methods

   Unlike fetchAll(), which retrieves all rows, fetch() only returns one row at a time.

3. Final Answer:

   Retrieves one row from the query result -> Option A

4. Quick Check:

   fetch() = one row [OK]

Hint: fetch() gets one row, fetchAll() gets all rows [OK]

Common Mistakes:

• Confusing fetch() with fetchAll()
• Thinking fetch() executes the query
• Assuming fetch() closes the connection

2. Which of the following is the correct syntax to fetch all rows from a PDO statement $stmt in PHP?

easy

A. $rows = $stmt->fetchAll();

B. $rows = $stmt->fetch_all();

C. $rows = fetchAll($stmt);

D. $rows = $stmt->fetch();

Solution

1. Step 1: Identify the correct method name

   The PDOStatement object uses fetchAll() to retrieve all rows as an array.

2. Step 2: Check method call syntax

   The correct syntax is $stmt->fetchAll(); with camelCase and parentheses.

3. Final Answer:

   $rows = $stmt->fetchAll(); -> Option A

4. Quick Check:

   fetchAll() method syntax correct [OK]

Hint: Use fetchAll() with parentheses on the statement object [OK]

Common Mistakes:

• Using fetch_all() instead of fetchAll()
• Calling fetchAll() as a standalone function
• Using fetch() when fetchAll() is needed

3. Given the following PHP code, what will be the output?

$stmt = $pdo->query('SELECT id FROM users LIMIT 2');
$row1 = $stmt->fetch();
$row2 = $stmt->fetch();
$row3 = $stmt->fetch();
var_dump([$row1, $row2, $row3]);

medium

A. [false, false, false]

B. [array with first row, array with second row, array with third row]

C. [array with first row, array with second row, false]

D. Syntax error

Solution

1. Step 1: Understand fetch() behavior on limited rows

   The query returns 2 rows. Each fetch() call returns one row until no rows remain.

2. Step 2: Analyze each fetch() call

   First fetch() returns first row array, second fetch() returns second row array, third fetch() returns false (no more rows).

3. Final Answer:

   [array with first row, array with second row, false] -> Option C

4. Quick Check:

   fetch() returns false after last row [OK]

Hint: fetch() returns false when no rows left [OK]

Common Mistakes:

• Expecting fetch() to return null instead of false
• Assuming fetch() returns empty array after last row
• Thinking fetch() fetches all rows at once

4. What is wrong with this PHP code snippet?

$stmt = $pdo->query('SELECT name FROM users');
$rows = $stmt->fetch();
foreach ($rows as $row) {
    echo $row['name'];
}

medium

A. fetch() returns all rows, so foreach is unnecessary

B. The variable $rows is not defined

C. The query syntax is invalid

D. fetch() returns a single row, so foreach on it causes error

Solution

1. Step 1: Check what fetch() returns

   fetch() returns a single row as an array, not an array of rows.

2. Step 2: Understand foreach usage

   foreach expects an array of rows, but here it loops over columns of one row, causing errors when accessing $row['name'].

3. Final Answer:

   fetch() returns a single row, so foreach on it causes error -> Option D

4. Quick Check:

   fetch() = one row, foreach needs multiple rows [OK]

Hint: Use fetchAll() when looping over multiple rows [OK]

Common Mistakes:

• Using fetch() when fetchAll() is needed for loops
• Assuming fetch() returns all rows
• Not checking data structure before foreach

5. You want to fetch all users from the database but only keep those whose age is greater than 18. Which code snippet correctly uses fetchAll() with a filter in PHP?

hard

A. $adults = $stmt->fetchAll(fn($r) => $r['age'] > 18);

B. $rows = $stmt->fetchAll();
$adults = array_filter($rows, fn($r) => $r['age'] > 18);

C. $adults = $stmt->fetchAll();
foreach ($adults as $r) {
if ($r['age'] <= 18) unset($r);
}

D. $adults = $stmt->fetch();
while ($r['age'] > 18) { $adults[] = $r; }

Solution

1. Step 1: Fetch all rows first

   Use fetchAll() to get all rows as an array.

2. Step 2: Filter rows by age

   Use array_filter() with a callback to keep only rows where age > 18.

3. Final Answer:

   $rows = $stmt->fetchAll(); $adults = array_filter($rows, fn($r) => $r['age'] > 18); -> Option B

4. Quick Check:

   fetchAll() + array_filter() = filtered rows [OK]

Hint: fetchAll() then filter with array_filter() for conditions [OK]

Common Mistakes:

• Passing a callback directly to fetchAll() (not supported)
• Modifying array inside foreach without reassigning
• Using fetch() when multiple rows needed