MySQL ORDER BY
ORDER BY sorts the result. Without it, MySQL is free to return rows in any order it likes — even if it looks stable on small data.
Single column
PHP
$rows = $pdo->query(
'SELECT id, name FROM customers ORDER BY name ASC'
)->fetchAll();
Multi-column
PHP
$rows = $pdo->query(
'SELECT * FROM customers ORDER BY country ASC, name ASC'
)->fetchAll();
Dynamic sort — allow-list the column
Placeholders bind values, not identifiers. To accept a column name from the user, allow-list it first:
PHP
$allowed = ['id', 'name', 'created_at']; $sort = in_array($_GET['sort'] ?? '', $allowed, true) ? $_GET['sort'] : 'id'; $dir = ($_GET['dir'] ?? '') === 'desc' ? 'DESC' : 'ASC'; $sql = "SELECT * FROM customers ORDER BY $sort $dir"; $rows = $pdo->query($sql)->fetchAll();
The allow-list is your defence. Don't trust an attacker-controlled string in an SQL clause.
Paging — LIMIT / OFFSET
PHP
$page = max(1, (int) ($_GET['page'] ?? 1));
$perPage = 20;
$offset = ($page - 1) * $perPage;
$stmt = $pdo->prepare(
'SELECT * FROM customers ORDER BY id ASC LIMIT ? OFFSET ?'
);
$stmt->bindValue(1, $perPage, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();
Keyset paging — fast on huge tables
OFFSET on millions of rows is slow because MySQL still reads them. Switch to a "where the last id ended" cursor:
PHP
$stmt = $pdo->prepare(
'SELECT * FROM customers WHERE id > ? ORDER BY id ASC LIMIT ?'
);
$stmt->bindValue(1, $lastSeenId, PDO::PARAM_INT);
$stmt->bindValue(2, $perPage, PDO::PARAM_INT);
$stmt->execute();
Tip: Don't rely on row order without ORDER BY — it works in development, then breaks the day an index gets dropped or rebuilt.
Example
Example
<?php
$rows = $pdo->query('SELECT * FROM customers ORDER BY name ASC')->fetchAll();
Try it Yourself »
Exercise
For pagination prefer this attribute on bindings.
$stmt->bindValue(1, $lim, PDO::PARAM
_);
Three letters.
Discussion
Loading…