PHP
Fetch mode constants
Learn fetch modes like PDO::FETCH_ASSOC, PDO::FETCH_OBJ and PDO::FETCH_NUM.
Fetch mode constants
fetch() and fetchAll() take a mode argument. The three most common modes are PDO::FETCH_ASSOC, PDO::FETCH_NUM and PDO::FETCH_OBJ.
FETCH_ASSOC gives an associative array keyed by column name: $row['name']. FETCH_NUM gives a numeric array keyed by column order: $row[0].
FETCH_OBJ returns each row as a stdClass object; columns are accessed as $row->name.
FETCH_BOTH and the default mode
PDO::FETCH_BOTH allows access by both column name and index; $row['name'] and $row[0] work at the same time. This is the default mode in PDO.
Still, the best practice is to write FETCH_ASSOC explicitly; FETCH_BOTH doubles the access and adds confusion.
In later groups we will also see FETCH_CLASS to map rows to your own class; it returns an object with filled properties.
Exercise Object access with FETCH_OBJ
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 as an object with while ($row = $stmt->fetch(PDO::FETCH_OBJ)) and print the name with $row->name.
Show example solution
:FETCH_OBJ)) { 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_OBJ)) {
echo $row->name . "\n";
}
Test yourself
-
In which mode is $row->name access possible?
- PDO::FETCH_ASSOC
- PDO::FETCH_NUM
- PDO::FETCH_OBJ
- PDO::FETCH_BOTH
Show answer
PDO::FETCH_OBJ — Correct. FETCH_OBJ gives each row as an object; access with $row->name.
-
Match the fetch modes
- PDO::FETCH_ASSOC
- Array by column name
- PDO::FETCH_NUM
- Array by index
- PDO::FETCH_OBJ
- Row as object
-
Complete column access with FETCH_OBJ
<?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_OBJ);
echo $row->____ . "\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_OBJ);
echo $row->name . "\n";
?>
Next lesson: Summary - PDO basics
