Fetch mode constants

PHP course · Advanced · Lesson 6/92

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";
?>

All lessons in Advanced

Fetch mode constants