Alphabetical A–Z Listings from MySQL in PHP (Without 27 Queries)

An A–Z index — designers, brands, authors, glossary terms — is a common listing page. The obvious implementation runs one query per letter, which means 27 round trips to the database to render a single page. There is a much better way, and along the way you have to make a decision about collation that most tutorials never mention.

The problem with a query per letter

The per-letter version has other problems beyond speed. The original of this article contained this array:

$a_z = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','U','V','W','X','Y','Z','2','&');

Look between 'S' and 'U'. T is missing. Every record beginning with T simply never appeared on the page, and because each letter is its own query there is nothing to make that obvious — the section is absent rather than empty. Hard-coding the alphabet invites exactly this class of silent bug.

The single-query version

<?php
declare(strict_types=1);

$pdo = new PDO('mysql:host=localhost;dbname=shop;charset=utf8mb4', $user, $pass, [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$rows = $pdo->query(
    'SELECT cat_title, cat_slug FROM designers ORDER BY cat_title'
)->fetchAll();

// Group by first character. Anything not A–Z lands under "#".
$grouped = [];
foreach ($rows as $row) {
    $first = strtoupper(mb_substr($row['cat_title'], 0, 1, 'UTF-8'));
    $key = preg_match('/^[A-Z]$/', $first) === 1 ? $first : '#';
    $grouped[$key][] = $row;
}

// Sort so "#" appears last rather than first.
uksort($grouped, static fn ($a, $b) => [$a === '#', $a] <=> [$b === '#', $b]);

Note mb_substr() rather than substr(). In UTF-8, substr($name, 0, 1) returns the first byte, which for a name beginning with Ö or Ł is half a character — you get a broken glyph as your heading.

Deriving the letters from the data rather than hard-coding them means no letter can go missing, and empty letters simply do not render.

The markup

<nav class="az-nav" aria-label="Jump to letter">
  <?php foreach (array_keys($grouped) as $letter): ?>
    <a href="#letter-<?= htmlspecialchars($letter) ?>"><?= htmlspecialchars($letter) ?></a>
  <?php endforeach; ?>
</nav>

<?php foreach ($grouped as $letter => $items): ?>
  <section aria-labelledby="letter-<?= htmlspecialchars($letter) ?>">
    <h2 id="letter-<?= htmlspecialchars($letter) ?>"><?= htmlspecialchars($letter) ?></h2>

    <ul class="az-list">
      <?php foreach ($items as $item): ?>
        <li>
          <a href="/by-designer/<?= rawurlencode($item['cat_slug']) ?>">
            <?= htmlspecialchars($item['cat_title'], ENT_QUOTES, 'UTF-8') ?>
          </a>
        </li>
      <?php endforeach; ?>
    </ul>
  </section>
<?php endforeach; ?>

Two corrections to the original markup. It emitted a series of <td> elements inside a single <tr>, producing one enormously wide row — this is a list, not tabular data, so a <ul> is both correct semantically and trivial to lay out in columns with CSS. And every output value needs htmlspecialchars(); the original interpolated cat_title straight into an HTML attribute, which is stored XSS if anyone can edit those names.

Multi-column layout without touching the HTML:

.az-list {
  columns: 4 200px;
  column-gap: 2rem;
  list-style: none;
  padding: 0;
}
.az-list li { break-inside: avoid; }

Collation: how sorting actually behaves

This is the part that decides whether your alphabetical list looks right, and it is set on the column, not in your PHP.

The original table was declared CHARSET=latin1. That cannot store most non-Western characters at all, and its sorting rules are wrong for anything but plain English. Use utf8mb4.

With a binary or case-sensitive collation you get the classic complaint that "sorting is broken" because every capitalised name sorts before every lowercase one. Use an _ai_ci collation for human-facing lists:

ALTER TABLE designers
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

You can also override per query without changing the schema, though this prevents index use and should be a last resort:

SELECT cat_title FROM designers ORDER BY cat_title COLLATE utf8mb4_0900_ai_ci;

Keep it fast with an index

CREATE INDEX idx_designers_title ON designers (cat_title);

With this, ORDER BY cat_title is served directly from the index — no filesort. Confirm with EXPLAIN: you want to see the index named under key and no "Using filesort" in Extra.

If you do keep a per-letter query for a filtered view, write it so the index still applies:

-- Uses the index: a prefix match
WHERE cat_title LIKE 'T%'

-- Cannot use the index: wrapping the column in a function
WHERE LEFT(cat_title, 1) = 'T'
WHERE UPPER(cat_title) LIKE 'T%'

Any function applied to the indexed column forces a full table scan. This is one of the most common causes of a slow listing page.

Names with numbers

Standard collation sorts strings character by character, so Item 10 comes before Item 9. If that matters, sort naturally in PHP after fetching:

usort($rows, static fn ($a, $b) => strnatcasecmp($a['cat_title'], $b['cat_title']));

Only reach for this when you actually need it — sorting in PHP means fetching every row, so it does not scale to large tables the way an indexed ORDER BY does.

Summary

• One ordered query, grouped in PHP — not one query per letter.
• Derive letters from the data so none can go missing.
• Use mb_substr() for the first character, or multi-byte names break.
• Store as utf8mb4 with an _ai_ci collation for natural human sorting.
• Index the sort column and keep functions off it in WHERE clauses.
• Escape every value on output with htmlspecialchars().