PHP
Single row with fetch()
Learn to fetch a single row from a query result with fetch().
A single row with fetch()
fetch() reads one row from a PDOStatement and moves the cursor forward. Each call returns the next row; when the table ends it returns false.
A single fetch() call is enough to get the first row: $row = $stmt->fetch(PDO::FETCH_ASSOC). In the returned associative array the column names are keys: $row['name'].
To get all rows at once you use fetchAll(); that comes in the next lesson.
A while loop with fetch()
To read many rows one by one, use the pattern while ($row = $stmt->fetch(PDO::FETCH_ASSOC)). The loop reads one row each step; it ends when fetch() returns false.
This pattern saves memory on large tables; it does not load every row into memory at once. It processes rows one at a time.
If the row count is small, fetchAll() is shorter; for many rows the while loop is preferred.
Exercise Print all names with a while loop
Create a users table in in-memory SQLite and insert three users. Run SELECT name FROM users ORDER BY id with query(). Read each row with the pattern while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) and print the name field.
Show example solution
:FETCH_ASSOC)) { echo $row['name'] . "\n"; }.
Example solution:
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$pdo->exec("INSERT INTO users (name) VALUES ('Sam')");
$pdo->exec("INSERT INTO users (name) VALUES ('Dana')");
$stmt = $pdo->query("SELECT name FROM users ORDER BY id");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['name'] . "\n";
}
Test yourself
-
Match the fetch concepts
- fetch()
- Reads a single row
- PDO::FETCH_ASSOC
- Associative array mode
- false
- Returned when table ends
- $row['name']
- Access to column value
-
What does fetch() return at the end of the table?
Show answer
false — Correct. fetch() returns false when the table ends; this is handy in a while loop.
-
Pick the right method for one row
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$stmt = $pdo->query("SELECT name FROM users");
$row = $stmt->____(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
?>
Show answer
<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
$pdo->exec("INSERT INTO users (name) VALUES ('Alex')");
$stmt = $pdo->query("SELECT name FROM users");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'] . "\n";
?>
Next lesson: All rows with fetchAll()
