PHP PDO fetch(), fetchAll() and FETCH_ASSOC Explained (Tested)

To get one row as an associative array with PDO, run a prepared statement and call $stmt->fetch(PDO::FETCH_ASSOC). It returns an array such as ['id' => 1, 'name' => 'Priya Shah'], or false when there’s no matching row. To get every row, call $stmt->fetchAll(PDO::FETCH_ASSOC), which returns a list of those arrays (an empty array if nothing matched). For a single value, such as a COUNT(*), use $stmt->fetchColumn(). Set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC when you connect and you can drop the argument from every call.

I ran every example below on PHP 8.4.26 with MySQL 8.4.6 on 8 October 2026, with full error reporting on, and pasted the real output under each one. If you’re new to PDO, start with my guide to PHP PDO prepared statements, which covers connecting and binding values safely. This post picks up where that one stops: getting the data back out.

The sample database and connection

The examples use a tiny shop database with two tables: customers (4 people in Leeds, Cardiff, Belfast and Glasgow) and orders (5 orders with a total, a status and a date).

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  city VARCHAR(60) NOT NULL
);
CREATE TABLE orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id INT UNSIGNED NOT NULL,
  total DECIMAL(8,2) NOT NULL,
  status ENUM('paid','shipped','refunded') NOT NULL,
  placed_on DATE NOT NULL
);

Here’s the connection every example uses. The three options matter. ERRMODE_EXCEPTION makes failed queries throw instead of failing silently. DEFAULT_FETCH_MODE sets associative arrays as the default. Turning off emulated prepares makes MySQL do the real prepare, which is the safer default.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', getenv('DB_USER'), getenv('DB_PASS'), [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

The username and password come from environment variables, so they never end up in your code or in Git.

fetch() with FETCH_ASSOC: one row as an array

fetch() returns the next row from a result set. For a query that should match one row, such as looking up a record by its ID, that’s all you need.

$stmt = $pdo->prepare('SELECT id, name, city FROM customers WHERE id = ?');
$stmt->execute([1]);
$customer = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($customer);

$stmt->execute([99]);
var_dump($stmt->fetch(PDO::FETCH_ASSOC));

Output:

Array
(
    [id] => 1
    [name] => Priya Shah
    [city] => Leeds
)
bool(false)

The keys are the column names (or aliases) from your SELECT. When no row matches, fetch() returns false, not null and not an empty array. So the usual pattern is:

$customer = $stmt->fetch();
if ($customer === false) {
    http_response_code(404);
    exit('Customer not found');
}

This is the same pattern you’d use for a login form: fetch the user row by email, check it isn’t false, then check the password hash. The full list of fetch() options is in the PHP manual for PDOStatement::fetch.

Why you want FETCH_ASSOC rather than the default

If you don’t set a fetch mode, PDO uses PDO::FETCH_BOTH. That gives you every column twice: once by name and once by number.

$stmt = $pdo->prepare('SELECT id, name FROM customers WHERE id = ?');
$stmt->execute([2]);
print_r($stmt->fetch(PDO::FETCH_BOTH));

Output:

Array
(
    [id] => 2
    [0] => 2
    [name] => Tom Evans
    [1] => Tom Evans
)

That doubles the memory for big result sets and makes json_encode() output messy. FETCH_ASSOC gives you only the named keys, which is almost always what you want.

fetchAll(): every row at once

fetchAll() returns all remaining rows as an array. With FETCH_ASSOC, that’s a list of associative arrays, the classic “multidimensional array” you can loop over, sort or turn into JSON.

$stmt = $pdo->prepare('SELECT id, total, status FROM orders WHERE status = ? ORDER BY placed_on');
$stmt->execute(['paid']);
$orders = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo count($orders), " paid orders\n";
print_r($orders);

$stmt->execute(['cancelled']);
var_dump($stmt->fetchAll());

Output:

2 paid orders
Array
(
    [0] => Array
        (
            [id] => 2
            [total] => 8.50
            [status] => paid
        )

    [1] => Array
        (
            [id] => 3
            [total] => 61.20
            [status] => paid
        )

)
array(0) {
}

Two things to notice. First, an empty result is an empty array, so if (!$orders) and count($orders) === 0 both work. Second, I reused the same prepared statement with a different value. You only prepare once, then call execute() as often as you like.

When you print these rows into a page, escape every value. My post on htmlspecialchars() and XSS shows the helper I use.

Looping without fetchAll()

A PDOStatement is iterable, so you can loop over it directly. PHP then works through one row at a time instead of building the whole array first.

$stmt = $pdo->query('SELECT name, city FROM customers ORDER BY name');
foreach ($stmt as $row) {
    echo $row['name'], ' (', $row['city'], ")\n";
}

Output:

Aoife Byrne (Belfast)
Callum Reid (Glasgow)
Priya Shah (Leeds)
Tom Evans (Cardiff)

Use fetchAll() when you need the whole list (to count it, sort it or pass it to a template). Use the loop or while ($row = $stmt->fetch()) for big exports, where holding every row in memory would be wasteful.

fetchColumn(): one value, such as a COUNT

When a query returns a single value, fetchColumn() saves you from digging it out of an array. It returns the first column of the next row.

$stmt = $pdo->prepare('SELECT COUNT(*) FROM orders WHERE status = ?');
$stmt->execute(['shipped']);
$shipped = $stmt->fetchColumn();
var_dump($shipped);

$stmt = $pdo->prepare('SELECT name FROM customers WHERE city = ?');
$stmt->execute(['Belfast']);
var_dump($stmt->fetchColumn());

$stmt->execute(['York']);
var_dump($stmt->fetchColumn());

Output:

int(2)
string(11) "Aoife Byrne"
bool(false)

With PHP 8.4 and native prepares, the count comes back as a real int. Like fetch(), it returns false when there’s no row. That makes fetchColumn() a poor choice for columns that can legitimately hold false-like values, and you can’t get a second column from the same row afterwards. The PHP manual for fetchColumn() warns about that second point.

Don’t use rowCount() to count SELECT results

It’s tempting to call $stmt->rowCount() after a SELECT. Here’s what happened when I tried:

$stmt = $pdo->prepare('SELECT id FROM orders WHERE total > ?');
$stmt->execute([20]);
echo 'rowCount(): ', $stmt->rowCount(), "\n";
echo 'count(fetchAll()): ', count($stmt->fetchAll()), "\n";

$upd = $pdo->prepare('UPDATE orders SET status = ? WHERE status = ?');
$upd->execute(['shipped', 'paid']);
echo 'rows updated: ', $upd->rowCount(), "\n";

Output:

rowCount(): 3
count(fetchAll()): 3
rows updated: 2

It worked here, because MySQL buffers results by default. But the PHP manual says that for SELECT the behaviour is undefined and differs between drivers, so you shouldn’t rely on it in code that might run on another database. Use SELECT COUNT(*) with fetchColumn() for a count, or count() on the array from fetchAll(). rowCount() is for INSERT, UPDATE and DELETE, where it reliably tells you how many rows changed.

Handy fetchAll() modes: key pairs, columns, unique and groups

fetchAll() can reshape results for you, which saves a lot of foreach loops. These four modes are the ones I use most. The PDO fetch mode constants page lists the rest.

// FETCH_KEY_PAIR: first column becomes the key, second the value
$cities = $pdo->query('SELECT id, city FROM customers ORDER BY id')->fetchAll(PDO::FETCH_KEY_PAIR);
print_r($cities);

// FETCH_COLUMN: a flat list of one column
$names = $pdo->query('SELECT name FROM customers ORDER BY name')->fetchAll(PDO::FETCH_COLUMN);
print_r($names);

// FETCH_UNIQUE: rows keyed by the first column (the id)
$byId = $pdo->query('SELECT id, name, city FROM customers')->fetchAll(PDO::FETCH_UNIQUE);
print_r($byId[3]);

// FETCH_GROUP: rows grouped by the first column
$byStatus = $pdo->query('SELECT status, id, total FROM orders ORDER BY id')->fetchAll(PDO::FETCH_GROUP);
print_r($byStatus['paid']);
echo implode(', ', array_keys($byStatus)), "\n";

Output:

Array
(
    [1] => Leeds
    [2] => Cardiff
    [3] => Belfast
    [4] => Glasgow
)
Array
(
    [0] => Aoife Byrne
    [1] => Callum Reid
    [2] => Priya Shah
    [3] => Tom Evans
)
Array
(
    [name] => Aoife Byrne
    [city] => Belfast
)
Array
(
    [0] => Array
        (
            [id] => 2
            [total] => 8.50
        )

    [1] => Array
        (
            [id] => 3
            [total] => 61.20
        )

)
shipped, paid, refunded

• FETCH_KEY_PAIR is perfect for a <select> dropdown: id => label. The query must return exactly two columns.
• FETCH_COLUMN gives a plain list, handy for an IN (...) check or in_array().
• FETCH_UNIQUE keys each row by its first column, so you can look rows up by ID without a loop. Note that the ID is removed from the row itself.
• FETCH_GROUP groups rows under the value of the first column. The groups appear in the order their first row did.

Fetching rows for a list of IDs

A common follow-up is “fetch these five customers”. You can’t bind an array to one placeholder, so build one ? per value:

$ids = [1, 3, 4];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$stmt = $pdo->prepare("SELECT id, name FROM customers WHERE id IN ($placeholders) ORDER BY id");
$stmt->execute($ids);
print_r($stmt->fetchAll(PDO::FETCH_KEY_PAIR));

Output:

Array
(
    [1] => Priya Shah
    [3] => Aoife Byrne
    [4] => Callum Reid
)

The query string only ever contains question marks, never the values, so it stays safe from SQL injection.

FETCH_OBJ and FETCH_CLASS: rows as objects

If you prefer $row->name to $row['name'], use FETCH_OBJ. For real models, FETCH_CLASS creates an instance of your own class and fills its properties.

$row = $pdo->query('SELECT id, name, city FROM customers WHERE id = 4')->fetch(PDO::FETCH_OBJ);
echo $row->name, ' lives in ', $row->city, "\n";

final class Customer
{
    public int $id;
    public string $name;
    public string $city;

    public function label(): string
    {
        return "{$this->name} ({$this->city})";
    }
}

$stmt = $pdo->query('SELECT id, name, city FROM customers ORDER BY id');
$customers = $stmt->fetchAll(PDO::FETCH_CLASS, Customer::class);
echo $customers[0]->label(), "\n";
var_dump($customers[0]->id);

Output:

Callum Reid lives in Glasgow
Priya Shah (Leeds)
int(1)

The typed int $id property worked without a cast because PDO returned the ID as an integer. PDO sets the properties before the constructor runs. If your constructor sets defaults that would overwrite them, add PDO::FETCH_PROPS_LATE to run the constructor first.

What types do you get back?

I checked what MySQL sends back for an INT, a DECIMAL and a DATE, with emulated prepares off and on:

EMULATE_PREPARES false:
array(3) {
  ["id"]=>
  int(1)
  ["total"]=>
  string(5) "24.99"
  ["placed_on"]=>
  string(10) "2026-09-14"
}
EMULATE_PREPARES true:
array(3) {
  ["id"]=>
  int(1)
  ["total"]=>
  string(5) "24.99"
  ["placed_on"]=>
  string(10) "2026-09-14"
}

On PHP 8.4, integers come back as int either way. DECIMAL values come back as strings, deliberately, so you don’t lose pennies to floating-point rounding. Keep money as a string or convert it to pence (an integer) before you do arithmetic. If you’re formatting prices for a page in pounds, my guide to formatting a number as currency covers the front-end side.

Paginating results with LIMIT and fetchAll()

Most lists on a real site are paged: 20 posts per page, 50 orders per page. You get one page with LIMIT and OFFSET, then fetchAll() it. The values should come from placeholders like everything else, but there’s a catch with types, so bind them as integers:

$perPage = 2;
$page = 2;
$stmt = $pdo->prepare('SELECT id, total FROM orders ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', ($page - 1) * $perPage, PDO::PARAM_INT);
$stmt->execute();
print_r($stmt->fetchAll(PDO::FETCH_KEY_PAIR));

Output:

Array
(
    [3] => 61.20
    [4] => 15.00
)

Page 2 skips the first two orders and returns orders 3 and 4, exactly as expected. Always add an ORDER BY when you paginate. Without one, the database is free to return rows in any order, and the same row can turn up on two pages.

Here’s why bindValue() with PDO::PARAM_INT matters. Passing values to execute() sends every one of them as a string. With native prepares (my connection above), MySQL copes. But plenty of shared hosts and older tutorials switch emulated prepares on, and then PHP pastes the value into the SQL as a quoted string:

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stmt = $pdo->prepare('SELECT id FROM orders ORDER BY id LIMIT ?');
try {
    $stmt->execute([2]);
    echo "worked\n";
} catch (PDOException $e) {
    echo $e->getMessage(), "\n";
}

Output:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2'' at line 1

The query became LIMIT '2', which MySQL rejects. Binding with PDO::PARAM_INT works in both modes, so it’s the habit to build. Work out the total number of pages with a separate SELECT COUNT(*) and fetchColumn(), as shown earlier, and cast the page number from the URL to an integer with a minimum of 1 before you use it.

Which fetch method should you use?

After testing all of these, here’s the short version I keep in my head when I write a query:

• Looking up one record (by ID, slug or email): fetch(), then check for === false. Return a 404 or an error message when it’s missing.
• Showing a list (a table, a page of results, a JSON array): fetchAll(PDO::FETCH_ASSOC). An empty result is an empty array, so your template can show “No orders yet” with a simple check.
• Getting one number or string (a count, a total, a single name): fetchColumn().
• Building a dropdown or lookup table: fetchAll(PDO::FETCH_KEY_PAIR), or FETCH_UNIQUE if you need the whole row by ID.
• Grouping a report (orders by status, posts by category): fetchAll(PDO::FETCH_GROUP).
• Working with model classes: FETCH_CLASS, with FETCH_PROPS_LATE if your constructor sets defaults.
• Exporting thousands of rows: loop with foreach or while ($row = $stmt->fetch()) so memory stays flat.

Whatever you pick, keep the query itself simple: list the columns you need instead of SELECT *. You transfer less data, aliases stay obvious, and adding a column to the table later can’t quietly change the shape of your arrays or overwrite a key in a JOIN.

Common mistakes when fetching with PDO

These are the bugs I’ve hit or seen in code reviews, each with the output that proves it.

Duplicate column names in a JOIN overwrite each other

This one is sneaky. Both tables have an id column, and an associative array can only hold one id key:

$sql = 'SELECT o.id, o.total, c.id, c.name
        FROM orders o JOIN customers c ON c.id = o.customer_id
        WHERE o.id = 3';
print_r($pdo->query($sql)->fetch());

Output:

Array
(
    [id] => 1
    [total] => 61.20
    [name] => Priya Shah
)

I asked for order 3, but id says 1. That’s the customer’s ID, which silently replaced the order’s. The fix is to alias every clashing column:

SELECT o.id AS order_id, o.total, c.id AS customer_id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
WHERE o.id = 3

That returns order_id => 3 and customer_id => 1, both intact.

Calling fetch() after fetchAll()

fetchAll() reads every remaining row. Anything you fetch afterwards gets nothing:

$stmt = $pdo->query('SELECT name FROM customers');
$all = $stmt->fetchAll();
var_dump($stmt->fetch());

Output:

bool(false)

If you need the first row as well as the full list, take it from the array: $first = $all[0] ?? null;.

Other mistakes to avoid

• Checking if ($row == null). fetch() returns false for no row. Use === false.
• Leaving the default FETCH_BOTH. You’ll get every column twice. Set FETCH_ASSOC once in the connection options.
• Putting variables straight into SQL. Always use placeholders with prepare() and execute(), even for “safe” values such as IDs.
• Using fetchAll() on huge tables. Loop with fetch() instead, or add LIMIT and paginate.
• Doing maths on DECIMAL strings with floats. Convert to pence first.

Once you can fetch rows reliably, you’re most of the way to a JSON API. My PHP REST API tutorial uses these same fetch() and fetchAll() calls to return JSON.

FAQ

What does PDO::FETCH_ASSOC return?

An array keyed by column name (or alias) for each row, for example ['id' => 1, 'name' => 'Priya Shah']. Unlike the default FETCH_BOTH, it doesn’t repeat each value under a numeric key.

What is the difference between fetch() and fetchAll() in PDO?

fetch() returns the next single row, or false when there are no more. fetchAll() returns every remaining row as an array, or an empty array if there are none.

Why does PDO fetch() return false?

Because there’s no row to return. Either the query matched nothing or you’ve already read every row, for example by calling fetchAll() first. With ERRMODE_EXCEPTION set, a real SQL error throws a PDOException instead.

How do I count rows with PDO?

Run SELECT COUNT(*) ... and read it with fetchColumn(), or call count() on the array from fetchAll(). Don’t rely on rowCount() for SELECT statements, because the PHP manual says its behaviour there differs between drivers.

How do I set FETCH_ASSOC as the default?

Pass PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC in the options array when you create the PDO object. Every fetch() and fetchAll() call then returns associative arrays unless you ask for something else.

How do I fetch results into an array of id => name pairs?

Select exactly two columns and use fetchAll(PDO::FETCH_KEY_PAIR). The first column becomes the key and the second becomes the value, which is ideal for dropdown menus.

Does PDO return numbers as strings?

On PHP 8.1 and later with MySQL, integer columns come back as int, as my test on PHP 8.4 showed. DECIMAL columns come back as strings on purpose, so prices keep their exact value.

Summary

fetch(PDO::FETCH_ASSOC) gets one row, fetchAll(PDO::FETCH_ASSOC) gets them all, and fetchColumn() gets one value. Set the fetch mode once in your connection, check for false with ===, alias clashing columns in JOINs, and count with COUNT(*) rather than rowCount(). Those habits cover nearly every PDO query you’ll write. To see fetch() and the === false check in a real feature, try my PHP login and session example, which looks up a user by email before checking the password.