[php] PDO fetch modes

18 Dec 2023

In PHP, PDO (PHP Data Objects) is a database access abstraction layer that provides a uniform method of access to multiple databases. When working with PDO, fetching data from a database is a common task and can be done in different ways. The fetch mode determines how the next row of a result set will be returned to the caller.

In PDO, there are several fetch modes that can be used with the fetch and fetchAll methods to retrieve data from the database result set. Each fetch mode has its own characteristics and is used in different scenarios.

Fetch Modes

Fetch Styles

PDO::FETCH_ASSOC: Returns an array indexed by column name as returned in the corresponding result set.
PDO::FETCH_OBJ: Returns an anonymous object with property names that correspond to the column names returned in the result set.

$stmt = $pdo->prepare('SELECT name, age FROM users');
$stmt->execute();

// Fetch a row as an associative array
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo $row['name'];

// Fetch a row as an object
$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->name;

Fetch Specific Class

PDO::FETCH_CLASS: Returns a new instance of the specified class, mapping the columns of the result set to named properties in the class.

class User {
    public $name;
    public $age;
}

$stmt = $pdo->prepare('SELECT name, age FROM users');
$stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
$stmt->execute();
$user = $stmt->fetch();
echo $user->name;

Fetch Custom

PDO::FETCH_FUNC: Returns the result set rows as an array, with each column value converted to another type or value that you define using a callback function.

function customMapping($name, $age) {
    return "Name: $name, Age: $age";
}

$stmt = $pdo->prepare('SELECT name, age FROM users');
$stmt->setFetchMode(PDO::FETCH_FUNC, 'customMapping');
$stmt->execute();
$row = $stmt->fetch();
echo $row;

Conclusion

PDO fetch modes allow PHP developers to work with database result sets in a flexible manner. By choosing the appropriate fetch mode, developers can easily retrieve and manipulate data from the database according to their specific requirements.

For more details, please refer to the PHP Manual.

By understanding the different fetch modes provided by PDO, PHP developers can effectively handle the result sets obtained from database queries in a way that best suits their individual application needs.