Fetch modes and styles in PHP

Introduction

Fetch modes and styles tell PHP how to get data from a database query result. They help you choose the format of the data you want to work with.

When you want to get data as an array with column names as keys.

When you want to get data as an object to access properties easily.

When you want to get data as a numeric array for simple loops.

When you want to fetch only one column from the result.

When you want to customize how data is fetched for special needs.

Syntax

PHP

$stmt->fetch(PDO::FETCH_MODE);

PDO::FETCH_MODE is a constant that defines how data is returned.

You can use different fetch modes like FETCH_ASSOC, FETCH_OBJ, FETCH_NUM, etc.

Examples

Fetches one row as an associative array with column names as keys.

PHP

$stmt->fetch(PDO::FETCH_ASSOC);

Fetches one row as an object where columns are properties.

PHP

$stmt->fetch(PDO::FETCH_OBJ);

Fetches one row as a numeric array with column numbers as keys.

PHP

$stmt->fetch(PDO::FETCH_NUM);

Fetches a single column from the next row in the result set.

PHP

$stmt->fetchColumn();

Sample Program

This program shows different fetch modes in PHP using PDO. It creates a small table, inserts two users, and fetches data in four ways: associative array, object, numeric array, and single column.

PHP

<?php
// Connect to SQLite in memory
$pdo = new PDO('sqlite::memory:');
// Create a table
$pdo->exec("CREATE TABLE users (id INTEGER, name TEXT)");
// Insert data
$pdo->exec("INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob')");

// Prepare a query
$stmt = $pdo->query("SELECT * FROM users");

// Fetch as associative array
$rowAssoc = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($rowAssoc);

// Fetch next row as object
$rowObj = $stmt->fetch(PDO::FETCH_OBJ);
print_r($rowObj);

// Reset query
$stmt = $pdo->query("SELECT * FROM users");
// Fetch first row as numeric array
$rowNum = $stmt->fetch(PDO::FETCH_NUM);
print_r($rowNum);

// Reset query
$stmt = $pdo->query("SELECT name FROM users");
// Fetch single column
$name = $stmt->fetchColumn();
echo $name . "\n";
?>

Important Notes

Using PDO::FETCH_ASSOC is common because it gives easy access to columns by name.

PDO::FETCH_OBJ lets you use $row->columnName which can be cleaner in some cases.

Always reset or re-run the query if you want to fetch rows again from the start.

Summary

Fetch modes control how database rows are returned in PHP.

Common modes are associative array, object, numeric array, and single column.

Choose the mode that fits how you want to use the data in your program.