Fetch Associative Array with PDO in PHP

This article explains how to retrieve database query results as an associative array using PHP Data Objects (PDO). You will learn how to use the PDO::FETCH_ASSOC mode to fetch both single and multiple rows, along with practical code examples to implement this in your PHP applications.

To fetch data as an associative array in PDO, you must specify the fetch mode PDO::FETCH_ASSOC. This tells PDO to return each row as an array indexed by column names.

Fetching a Single Row

When you expect only one row from your query (for example, looking up a user by their ID), use the fetch() method.

// Prepare the SQL statement
$stmt = $pdo->prepare("SELECT id, username, email FROM users WHERE id = :id");

// Execute the statement with parameters
$stmt->execute(['id' => 1]);

// Fetch the result as an associative array
$user = $stmt->fetch(PDO::FETCH_ASSOC);

if ($user) {
    echo $user['username']; // Access via column name
    echo $user['email'];
}

Fetching Multiple Rows

When you want to retrieve all rows returned by a query, use the fetchAll() method.

// Prepare and execute the SQL statement
$stmt = $pdo->prepare("SELECT id, username, email FROM users");
$stmt->execute();

// Fetch all results as an array of associative arrays
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Loop through the results
foreach ($users as $user) {
    echo $user['username'] . '<br>';
}

Setting a Default Fetch Mode

If you want PDO to return associative arrays by default for all queries, you can set the fetch mode globally when initializing your PDO connection.

$options = [
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
];

$pdo = new PDO($dsn, $username, $password, $options);

// Now you can fetch without explicitly passing PDO::FETCH_ASSOC
$stmt = $pdo->query("SELECT * FROM users");
$user = $stmt->fetch(); // Returns an associative array automatically