PHP PDOStatement::setFetchMode Tutorial

last modified April 19, 2025

The PDOStatement::setFetchMode method controls how PDO returns rows from a database query. It determines the format of fetched data.

Basic Definition

PDOStatement::setFetchMode sets the fetch mode for a statement object. It affects how rows are returned when calling fetch() or fetchAll().

Syntax: public PDOStatement::setFetchMode(int $mode, mixed ...$args): bool. The mode parameter specifies the fetch style. Optional args provide additional parameters for some modes.

FETCH_ASSOC

This example demonstrates fetching rows as associative arrays.

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT id, name, email FROM users');
    
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    while ($row = $stmt->fetch()) {
        echo "ID: {$row['id']}, Name: {$row['name']}, Email: {$row['email']}\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_ASSOC returns each row as an array indexed by column name. This is useful when you need to access columns by their database names.

FETCH_OBJ

This shows how to fetch rows as stdClass objects.

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT id, name FROM users');
    
    $stmt->setFetchMode(PDO::FETCH_OBJ);
    
    while ($user = $stmt->fetch()) {
        echo "ID: {$user->id}, Name: {$user->name}\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_OBJ returns each row as a stdClass object. Properties correspond to column names. This provides object-oriented access to result data.

FETCH_CLASS

This demonstrates fetching rows into instances of a specific class.

<?php

declare(strict_types=1);

class User {
    public int $id;
    public string $name;
    public string $email;
}

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT id, name, email FROM users');
    
    $stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
    
    while ($user = $stmt->fetch()) {
        echo "User: {$user->name} ({$user->email})\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_CLASS creates instances of the specified class. Column values are assigned to matching properties. The class must be defined before use.

FETCH_INTO

This shows how to fetch rows into an existing object instance.

<?php

declare(strict_types=1);

class User {
    public int $id;
    public string $name;
    public string $email;
}

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT id, name, email FROM users');
    
    $user = new User();
    $stmt->setFetchMode(PDO::FETCH_INTO, $user);
    
    while ($stmt->fetch()) {
        echo "User: {$user->name} ({$user->email})\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_INTO updates an existing object with each row's data. The same object is reused for each fetch. This can be more efficient than creating new objects.

FETCH_COLUMN

This demonstrates fetching a single column from the result set.

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT name FROM users');
    
    $stmt->setFetchMode(PDO::FETCH_COLUMN, 0);
    
    while ($name = $stmt->fetch()) {
        echo "Name: $name\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_COLUMN returns values from a single column. The second parameter specifies the column index (0-based). This is useful for simple lists.

FETCH_KEY_PAIR

This shows how to fetch rows as key-value pairs.

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT id, name FROM users');
    
    $stmt->setFetchMode(PDO::FETCH_KEY_PAIR);
    
    $users = $stmt->fetchAll();
    
    foreach ($users as $id => $name) {
        echo "ID $id: $name\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_KEY_PAIR returns rows as key-value pairs. The first column becomes the key, the second the value. This is useful for creating lookup arrays.

FETCH_GROUP

This demonstrates grouping rows by a column value.

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT role, name, email FROM users ORDER BY role');
    
    $stmt->setFetchMode(PDO::FETCH_GROUP|PDO::FETCH_ASSOC);
    
    $groupedUsers = $stmt->fetchAll();
    
    foreach ($groupedUsers as $role => $users) {
        echo "Role: $role\n";
        foreach ($users as $user) {
            echo "  - {$user['name']} ({$user['email']})\n";
        }
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

FETCH_GROUP groups rows by the first column's value. Combined with FETCH_ASSOC, it creates a nested array structure. This is useful for hierarchical data.

Best Practices

• Choose appropriate mode: Match fetch mode to your data needs.
• Consistency: Use same mode throughout application.
• Performance: FETCH_COLUMN for single column data.
• Type safety: Use FETCH_CLASS with typed properties.
• Memory: Use fetch for large result sets.

Source

PHP PDOStatement::setFetchMode Documentation

This tutorial covered the PDOStatement::setFetchMode method with practical examples showing different ways to fetch database results in PHP.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all PHP PDO Functions.