Reading records from a MySQL database in PHP uses SELECT queries executed through PDO statements, followed by fetch(), fetchAll(), or fetchColumn() to retrieve rows into PHP arrays. Picking the right fetch method keeps memory usage low and makes template rendering straightforward.
fetch(), fetchAll(), and fetchColumn()Match the PDO fetch helper to the exact shape of data your query returns.
| Method | Return Value | Best Use Case |
|---|---|---|
$stmt->fetch() |
Single associative array or false |
Fetching one record by id or email, or streaming row-by-row |
$stmt->fetchAll() |
Array of associative arrays ([] if empty) |
Small paginated lists (e.g., 20 rows per page) |
$stmt->fetchColumn() |
Single scalar value or false |
SELECT COUNT(*), SUM(...), or single-column lookup |
<?php
declare(strict_types=1);
function getUserSummary(PDO $pdo, int $userId): ?array
{
$stmt = $pdo->prepare(
'SELECT id, full_name, email, created_at FROM users WHERE id = :id LIMIT 1'
);
$stmt->execute([':id' => $userId]);
$user = $stmt->fetch();
return $user !== false ? $user : null;
}
function listRecentOrders(PDO $pdo, int $userId): array
{
$stmt = $pdo->prepare(
'SELECT id, total_amount, created_at FROM orders WHERE user_id = :user_id ORDER BY id DESC LIMIT 20'
);
$stmt->execute([':user_id' => $userId]);
return $stmt->fetchAll();
}
Even if records came from your own database, treat every string column as untrusted when outputting HTML. Escape text with htmlspecialchars((string) $row['full_name'], ENT_QUOTES, 'UTF-8') to block stored XSS attacks.
SELECT * in production queries: Fetching unused TEXT or BLOB columns wastes database memory and network bandwidth. Always list the exact columns you need.fetchAll() on a table with 100,000 rows: Loading massive result sets into an array exhausts PHP's memory_limit. Paginate with LIMIT / OFFSET (or keyset pagination) or iterate row by row with while ($row = $stmt->fetch()).false return from fetch(): Accessing $user['email'] when fetch() returned false (no matching ID) triggers a PHP warning. Check if ($user === false) first.PDO::FETCH_ASSOC do?It configures fetch() and fetchAll() to return clean associative arrays keyed by column names ($row['email']), avoiding duplicated numeric indices from PDO::FETCH_BOTH.
Run SELECT COUNT(*) FROM orders WHERE user_id = :id and read the integer via (int) $stmt->fetchColumn(), rather than calling count($stmt->fetchAll()).
Yes. Use $stmt->fetchObject(UserDto::class) or PDO::FETCH_CLASS when mapping results directly onto typed objects.
Sign in to track your learning journey, earn industry-recognized certificates, and join our elite developer community.
Quick Access With
Experiment with the code from this lesson in our interactive playground.