PHP PDO SELECT Queries

PHP’s PDO (PHP Data Objects) extension is the standard way to talk to a relational database from PHP, and pulling data back out with a SELECT query is the single most common thing you’ll do with it. PDO gives you two ways to run a SELECT: a quick one-shot query() call for fixed SQL, or a prepare() plus execute() pair that lets you pass in variables safely without ever building SQL strings by hand. This lesson covers both approaches, how PDO turns database rows into PHP values, the fetch modes you can choose from, and the mistakes that trip up almost everyone the first time.

Overview / How PDO SELECT Queries Work

PDO is a thin, driver-agnostic layer. When you write new PDO('mysql:host=...;dbname=...', $user, $pass), PDO parses the DSN (data source name), loads the matching driver extension — pdo_mysql for MySQL/MariaDB — and opens a connection through it. Every subsequent call goes through that same driver, which is why the same PDO code mostly works against PostgreSQL or SQLite just by changing the DSN.

There are two ways to issue a SELECT:

Direct execution with PDO::query($sql) — the SQL string is sent to the server immediately and a PDOStatement is returned, representing a cursor over the result set. Use this only when the SQL contains no variable data.
Prepared execution with PDO::prepare($sql) followed by PDOStatement::execute() — the SQL template (with placeholders) is compiled first, and the actual values are bound in a separate step. This is what keeps user-supplied values from ever being interpreted as SQL syntax.

In both cases, the result comes back as a PDOStatement object. Nothing about the rows themselves becomes a PHP array until you call one of the fetch methods — fetch(), fetchAll(), or fetchColumn() — which is what converts each row’s raw column data into PHP strings, ints, floats, or objects depending on the fetch mode.

Syntax

The core methods you’ll use for reading data look like this:

Method	Purpose
`PDO::query(string $sql): PDOStatement`	Runs a fixed SQL string immediately and returns a statement over the results.
`PDO::prepare(string $sql): PDOStatement`	Compiles a SQL template containing `?` or `:name` placeholders, without running it.
`PDOStatement::execute(?array $params = null): bool`	Binds any given parameters and runs the prepared statement.
`PDOStatement::fetch(int $mode = PDO::FETCH_ASSOC)`	Returns the next row, or `false` when there are no more rows.
`PDOStatement::fetchAll(int $mode = PDO::FETCH_ASSOC)`	Returns every remaining row at once, as an array.
`PDOStatement::fetchColumn(int $column = 0)`	Returns a single column’s value from the next row — ideal for `COUNT(*)` or single-value lookups.
`PDOStatement::bindValue($param, $value, int $type)`	Binds one fixed value to a named or positional placeholder with an explicit PDO type.

The fetch mode controls the shape of each row you get back:

PDO::FETCH_ASSOC — associative array keyed by column name.
PDO::FETCH_NUM — array keyed by column position (0, 1, 2, …).
PDO::FETCH_BOTH — both keyed forms at once (the default if you never configure one — wastes memory, so avoid it).
PDO::FETCH_OBJ — an anonymous stdClass object with one property per column.
PDO::FETCH_CLASS — populates a new instance of a class you specify.
PDO::FETCH_COLUMN — a flat array containing just one column’s values from every row.

Examples

Example 1: A simple SELECT with query()

When the SQL has no variable parts, query() is the shortest path from SQL to PHP array:

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

$stmt = $pdo->query('SELECT id, name, price FROM products ORDER BY name');
$products = $stmt->fetchAll();

foreach ($products as $product) {
    echo $product['name'] . ': $' . $product['price'] . "\n";
}

Output:

Keyboard: $29.99
Monitor: $199.99
Mouse: $14.99

Assuming the products table holds a keyboard at 29.99, a monitor at 199.99, and a mouse at 14.99, fetchAll() pulls every row back as an associative array (because PDO::ATTR_DEFAULT_FETCH_MODE was set to PDO::FETCH_ASSOC on the connection), and ORDER BY name returns them alphabetically.

Example 2: A prepared statement with a positional placeholder

As soon as a value comes from outside your script — a URL, a form, a session — switch to a prepared statement:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$stmt = $pdo->prepare('SELECT id, username, email FROM users WHERE id = ?');
$stmt->execute([42]);

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

if ($user === false) {
    echo "No user found.\n";
} else {
    echo "User #{$user['id']}: {$user['username']} ({$user['email']})\n";
}

Output:

User #42: jchen (jchen@example.com)

The ? is a positional placeholder; the array passed to execute() fills it in order. fetch() returns exactly one row — or false if nothing matched — so checking for false with === before touching $user['email'] avoids an undefined-array-key warning when the id doesn’t exist.

Example 3: Named placeholders with bindValue()

Named placeholders keep multi-parameter queries readable, and bindValue() lets you say exactly what PDO type each value is:

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

$sql = 'SELECT id, customer_name, total
        FROM orders
        WHERE status = :status AND total >= :min_total
        ORDER BY total DESC';

$stmt = $pdo->prepare($sql);
$stmt->bindValue(':status', 'shipped', PDO::PARAM_STR);
$stmt->bindValue(':min_total', 50, PDO::PARAM_INT);
$stmt->execute();

$orders = $stmt->fetchAll();

echo "Found " . count($orders) . " orders:\n";
foreach ($orders as $order) {
    printf("#%d %s - $%.2f\n", $order['id'], $order['customer_name'], $order['total']);
}

Output:

Found 2 orders:
#103 Dana Lee - $120.50
#98 Marco Reyes - $75.00

Named placeholders (:status, :min_total) don’t depend on argument order, which matters once a query has several of them. bindValue() binds a fixed value right away — unlike bindParam(), which binds a variable reference that’s re-read at execute time — and passing PDO::PARAM_INT tells the driver to treat 50 as an integer rather than a string.

Under the Hood: Step by Step

Here’s what actually happens between calling prepare() and reading your last row:

Constructing new PDO(...) loads the driver extension named in the DSN (pdo_mysql, pdo_pgsql, pdo_sqlite, …) and opens a connection through it.
prepare() creates a PDOStatement holding the SQL template. By default, PDO::ATTR_EMULATE_PREPARES is true for the MySQL driver, so PDO substitutes the placeholders itself, quoting and escaping each value, and sends one complete statement — it isn’t using MySQL’s native prepared-statement protocol unless you turn emulation off.
execute() sends the statement to the server (plus the bound values separately, if emulation is off), which parses, plans, and runs it.
For MySQL, PDO uses buffered queries by default, so the entire result set is transferred into PHP’s memory as soon as execute() returns — before you’ve called fetch() even once.
Each call to fetch() just advances an internal cursor over that buffer and converts one row’s raw bytes into PHP values according to the fetch mode you asked for.
When the cursor passes the last row, fetch() returns false. fetchAll() repeats that process internally and hands back every row as one array in a single call.
The statement and connection stay open until the objects go out of scope (or you call closeCursor() / unset them), at which point PHP releases the underlying resources.

Common Mistakes

Mistake 1: Interpolating variables directly into the query string

It’s tempting to build the SQL string by hand, especially in a quick script:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop', 'root', 'secret');

$username = $_GET['username'];
$stmt = $pdo->query("SELECT id, email FROM users WHERE username = '$username'");
$user = $stmt->fetch(PDO::FETCH_ASSOC);

echo $user['email'];

Output (assuming ?username=alice and a matching row):

alice@example.com

This works right up until someone requests ?username=' OR '1'='1, at which point the WHERE clause stops filtering anything and the query can return — or expose — rows it was never meant to. Any value that reaches the SQL string through concatenation or interpolation is a SQL injection risk, no matter how unlikely the “bad” input seems.

The fix is always the same: put the variable in a placeholder, not in the string.

<?php
$pdo = new PDO('mysql:host=localhost;dbname=shop', 'root', 'secret', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

$username = $_GET['username'];
$stmt = $pdo->prepare('SELECT id, email FROM users WHERE username = :username');
$stmt->execute(['username' => $username]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);

echo $user !== false ? $user['email'] : 'Not found';

Output (same assumption):

alice@example.com

PDO sends the placeholder and the value to the driver separately, so the value can never be parsed as part of the SQL — no matter what characters it contains.

Mistake 2: Binding LIMIT/OFFSET values without an integer type

Pagination queries commonly bind a page size and offset — and this one looks reasonable:

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

$page = 2;
$perPage = 10;

$stmt = $pdo->prepare('SELECT id, name FROM products ORDER BY id LIMIT ? OFFSET ?');
$stmt->execute([$perPage, $page * $perPage]);

Output:

Throws a PDOException at execute() — MySQL rejects a quoted string where LIMIT/OFFSET expects an integer literal.

Passing an array straight to execute() binds every value as PDO::PARAM_STR by default. With emulated prepares (the default) MySQL is usually forgiving, but as soon as you turn emulation off — which you should, for real prepared-statement protection — the server receives LIMIT '10' OFFSET '20' and rejects it, because LIMIT/OFFSET require true integer literals at the protocol level, not strings.

Bind those two values explicitly as integers instead:

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

$page = 2;
$perPage = 10;

$stmt = $pdo->prepare('SELECT id, name FROM products ORDER BY id LIMIT :limit OFFSET :offset');
$stmt->bindValue(':limit', $perPage, PDO::PARAM_INT);
$stmt->bindValue(':offset', $page * $perPage, PDO::PARAM_INT);
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo count($rows) . " rows returned.\n";

Output:

10 rows returned.

bindValue(..., PDO::PARAM_INT) tells the driver to send a true integer, so LIMIT/OFFSET parse correctly whether or not emulation is enabled.

Best Practices

Always set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so a failed query throws instead of failing silently.
Use prepare() / execute() for every query that includes a variable — no exceptions, even for values you “trust”.
Set PDO::ATTR_EMULATE_PREPARES to false in production for real server-side prepares, and bind numeric placeholders with an explicit PDO::PARAM_INT type.
Configure a default fetch mode once (e.g. PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC) instead of repeating it on every call.
Select only the columns you actually need instead of SELECT *, so a schema change doesn’t silently change your array shape.
Use fetchColumn() for a single scalar result — like a count or an existence check — instead of building a whole array to read one value.
Call $stmt->closeCursor() before re-running a statement object you plan to reuse with different parameters.
Add a LIMIT clause and paginate queries against large tables instead of fetching every row into memory.

Practice Exercises

Given a table articles(id, title, published_at), write a prepared SELECT that returns the id and title of every article published after a given date, ordered by published_at descending. Bind the date with a named placeholder.
Write a query that uses fetchColumn() to return just the number of rows in a customers table where country = 'US'. (Hint: use SELECT COUNT(*) — don’t rely on rowCount().)
Take the Example 3 query above and change it to fetch rows as objects using PDO::FETCH_OBJ instead of an associative array, then rewrite the loop to read properties with -> instead of array keys.

Summary

PDO::query() runs fixed SQL immediately; prepare() plus execute() safely runs SQL that includes variable data.
Always use placeholders (? or :name) for any value that isn’t a hard-coded literal — this is what prevents SQL injection.
fetch() returns one row (or false); fetchAll() returns every row as an array; fetchColumn() returns a single scalar value.
The fetch mode (PDO::FETCH_ASSOC, PDO::FETCH_OBJ, PDO::FETCH_CLASS, …) controls the shape of the data you get back.
Turn off emulated prepares and bind numeric placeholders as PDO::PARAM_INT to avoid type-related SQL errors, especially with LIMIT/OFFSET.
Set PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION so database errors surface as catchable exceptions instead of silent failures.

PHP PDO SELECT Queries

Overview / How PDO SELECT Queries Work

Syntax

Examples

Example 1: A simple SELECT with query()

Example 2: A prepared statement with a positional placeholder

Example 3: Named placeholders with bindValue()

Under the Hood: Step by Step

Common Mistakes

Mistake 1: Interpolating variables directly into the query string

Mistake 2: Binding LIMIT/OFFSET values without an integer type

Best Practices

Practice Exercises

Summary

Table of Contents