The Library Page — Browse What You Own

Now we make a web page that lists everything in your library. Filter by type, search by title, paginate so you don't blow up the browser with 5000 entries at once. Plus the moment where you click a track and it opens the player — which we'll build in the next chapter, but we'll wire up the link now.

One PHP page that reads from the media table, filters by query params, paginates with LIMIT/OFFSET, escapes everything on output. Standard CRUD-list pattern that every web app reuses forever.

The plan for the library page

Before we write code, let's sketch what we're building. The page needs to show a list of media items, with each row showing the title, artist if known, duration, type, and a play button. It needs filter chips at the top (All / Audio / Video). It needs a search box that filters by title. It needs pagination at the bottom because if you have 10,000 tracks, loading all of them in one shot is bad for everyone.

All filtering and pagination happens through URL query parameters: ?type=audio&q=daft&page=2. Why? Because then every state of the page is bookmarkable. You can share a URL with someone — well, someone on your LAN — and they'll see the same view. Refresh-safe, back-button-safe, deeply linkable. It's the unfashionable old way of doing things and it's still the best way for content browse pages.

This page is mostly the array/SQL skills you already have from Part 2. The new pieces are LIMIT/OFFSET for pagination, and the gentle art of dynamically building a WHERE clause from optional filters without falling into SQL injection traps. Both are useful patterns way beyond HomeStream.

Building a dynamic WHERE clause safely

Here's the challenge: the user might be filtering by type, OR by search query, OR by both, OR by neither. We can't write one fixed SQL string because the conditions change. But we ALSO can't string-concatenate user input into SQL — that's SQL injection. So how do we build a flexible query safely?

The trick is to build two parallel arrays. One holds SQL fragments like "type = :type". The other holds the values to bind, like ['type' => 'audio']. We add to both arrays conditionally. Then we glue the SQL fragments together with AND and pass the value array to execute(). The query is dynamic but the values still go through prepared statements. Best of both worlds.

$where = [];
$bind  = [];

if ($type !== 'all') {
    $where[] = "type = :type";
    $bind['type'] = $type;
}

if ($q !== '') {
    $where[] = "title LIKE :q";
    $bind['q'] = "%$q%";   // LIKE pattern wildcards
}

$sql = "SELECT * FROM media";
if ($where) {
    $sql .= " WHERE " . implode(" AND ", $where);
}
$sql .= " ORDER BY added_at DESC LIMIT :limit OFFSET :offset";

This pattern shows up in basically every list page in every database-backed app. Get comfortable with it; you'll write some variation of it dozens of times.

One gotcha worth flagging: LIMIT and OFFSET can't be regular bound placeholders in some MySQL/PDO setups because they're not "values" in the SQL sense. We'll handle them with named placeholders here and explicitly bind them as integers (PDO::PARAM_INT) to keep things safe. Watch for that in the code below.

LIMIT and OFFSET — the simplest pagination

Pagination at this scale is delightfully simple. If you want page 1, you SELECT with LIMIT 50 OFFSET 0. Page 2 is LIMIT 50 OFFSET 50. Page 3 is LIMIT 50 OFFSET 100. General formula: OFFSET = (page - 1) * pageSize. The database does the slicing for you.

For tiny libraries (say, fewer than 10,000 items), this is fine forever. For massive datasets you eventually need "cursor pagination" because OFFSET gets slower the deeper into the results you go. But seriously — you're not going to outscale OFFSET pagination for a personal media library. Don't sweat it.

Total count for "Page X of Y"

To show "Page 2 of 47" we need to know the total count. Quick second query:

$count_sql = "SELECT COUNT(*) FROM media";
if ($where) $count_sql .= " WHERE " . implode(" AND ", $where);
$total = (int) db()->prepare($count_sql)->execute($bind)->fetchColumn();

Wait — that's not quite right. execute() returns a boolean, you can't chain fetchColumn off it. Let me show the actual code below where it's broken into two lines. Real talk: this is the kind of dumb mistake every PHP developer makes once a week, and the error message ("Call to a member function fetchColumn() on bool") is your friend pointing you back to it.

Putting it all together

Let's build the real page. Going to give you the full code in the mini-project below since it's all the new pattern in one piece. Read it top to bottom; the chunks should feel familiar at this point.

Build: The Library Page

Time for the big payoff of this chapter — a real browsable library. After this, you can refresh and see your actual files listed in a real UI. It feels small but it's the first thing that looks like "an app." Let's go.

Create /home/erictey/server/homestream/public/index.php.

Paste this in:

<?php
declare(strict_types=1);
require __DIR__ . '/../lib/db.php';

// ---- read & validate query params ----
$type = $_GET['type'] ?? 'all';
if (!in_array($type, ['all','audio','video'], true)) $type = 'all';

$q = trim((string)($_GET['q'] ?? ''));
$page = max(1, (int)($_GET['page'] ?? 1));
$per  = 50;
$offset = ($page - 1) * $per;

// ---- build dynamic WHERE ----
$where = [];
$bind  = [];

if ($type !== 'all') {
    $where[] = "type = :type";
    $bind['type'] = $type;
}
if ($q !== '') {
    $where[] = "title LIKE :q";
    $bind['q'] = "%$q%";
}

$where_sql = $where ? " WHERE " . implode(" AND ", $where) : "";

// ---- total count ----
$count_stmt = db()->prepare("SELECT COUNT(*) FROM media$where_sql");
$count_stmt->execute($bind);
$total = (int) $count_stmt->fetchColumn();
$pages = max(1, (int) ceil($total / $per));

// ---- the page itself ----
$stmt = db()->prepare("SELECT * FROM media$where_sql ORDER BY added_at DESC LIMIT :limit OFFSET :offset");
foreach ($bind as $k => $v) {
    $stmt->bindValue($k, $v);
}
$stmt->bindValue('limit', $per, PDO::PARAM_INT);
$stmt->bindValue('offset', $offset, PDO::PARAM_INT);
$stmt->execute();
$items = $stmt->fetchAll();

function e(string $s): string {
    return htmlspecialchars($s, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

function format_duration(?int $s): string {
    if ($s === null) return '—';
    $m = intdiv($s, 60);
    $sec = $s % 60;
    return sprintf('%d:%02d', $m, $sec);
}

function chip_href(string $type, string $q, int $page = 1): string {
    return '?' . http_build_query(['type' => $type, 'q' => $q, 'page' => $page]);
}
?>
<!DOCTYPE html>
<html>
<head>
  <title>HomeStream</title>
  <style>
    body { background:#07050d; color:#f0e9ff; font-family:monospace; padding:30px; max-width:900px; margin:auto; }
    h1 { color:#ff2e88; }
    .filters { margin: 20px 0; display:flex; gap:10px; flex-wrap:wrap; align-items:center; }
    .filters a { color:#5bf0ff; text-decoration:none; padding:4px 12px; border:1px solid #5bf0ff55; }
    .filters a.active { background:#5bf0ff22; border-color:#5bf0ff; }
    input { background:#110a1c; border:1px solid #ff2e88; color:#f0e9ff; padding:6px 10px; font-family:monospace; }
    .item { display:grid; grid-template-columns:60px 1fr 80px 80px; gap:14px; padding:8px 12px; border-bottom:1px solid #ff2e8822; align-items:center; }
    .item a { color:#5bf0ff; text-decoration:none; }
    .item a:hover { color:#ff2e88; }
    .type { font-size:12px; color:#b9adcf; }
    .duration { color:#b9adcf; text-align:right; }
    .pager { margin:20px 0; display:flex; gap:10px; }
    .pager a, .pager span { padding:4px 10px; border:1px solid #ff2e8855; color:#5bf0ff; text-decoration:none; }
    .pager .current { background:#ff2e8822; color:#ff2e88; border-color:#ff2e88; }
  </style>
</head>
<body>
  <h1>HomeStream</h1>
  <p style="color:#b9adcf"><?= $total ?> items in your library</p>

  <form class="filters">
    <a class="<?= $type === 'all' ? 'active' : '' ?>" href="<?= e(chip_href('all', $q)) ?>">All</a>
    <a class="<?= $type === 'audio' ? 'active' : '' ?>" href="<?= e(chip_href('audio', $q)) ?>">Audio</a>
    <a class="<?= $type === 'video' ? 'active' : '' ?>" href="<?= e(chip_href('video', $q)) ?>">Video</a>
    <input name="q" placeholder="search title..." value="<?= e($q) ?>">
    <input type="hidden" name="type" value="<?= e($type) ?>">
    <button>Search</button>
  </form>

  <?php if (!$items): ?>
    <p style="color:#ffa64e">Nothing matches. Try a different search or run the scanner.</p>
  <?php else: foreach ($items as $item): ?>
    <div class="item">
      <span class="type"><?= e(strtoupper($item['type'])) ?></span>
      <a href="play.php?id=<?= (int)$item['id'] ?>"><?= e($item['title']) ?></a>
      <span class="duration"><?= format_duration($item['duration_s']) ?></span>
      <span class="duration"><?= number_format((int)$item['size_b'] / 1e6, 1) ?> MB</span>
    </div>
  <?php endforeach; endif; ?>

  <?php if ($pages > 1): ?>
    <div class="pager">
      <?php if ($page > 1): ?>
        <a href="<?= e(chip_href($type, $q, $page - 1)) ?>">← prev</a>
      <?php endif; ?>
      <span class="current">Page <?= $page ?> of <?= $pages ?></span>
      <?php if ($page < $pages): ?>
        <a href="<?= e(chip_href($type, $q, $page + 1)) ?>">next →</a>
      <?php endif; ?>
    </div>
  <?php endif; ?>
</body>
</html>

Visit http://192.168.0.19/server/homestream/public/index.php.
You should see your library! Each row is a file, click the title and it tries to navigate to the player page (which doesn't exist yet — that's chapter 6). For now we're just confirming the list view works.
Try filters: click "Audio", click "Video". Try searching. Try paginating (if you have enough files).

If the list is empty: you probably haven't run the scanner yet (or your media folder is empty). Drop in a few files and re-run bin/scan.php.

Stretch goals:

Add a sort dropdown — let the user sort by title, duration, or size. (Hint: another query param + ORDER BY swap.)
Show a count next to each filter chip ("Audio (1,231)") by running a quick aggregation query.
Make the "search" search artists and albums too once the metadata chapter lands.
Show the relative time of "added_at" instead of the raw timestamp ("3 days ago" via a small helper function).

What you flexed: Dynamic WHERE clause built with parallel arrays, prepared statements with named placeholders, explicit binding for LIMIT/OFFSET as integers, basic LIMIT/OFFSET pagination, query-param-driven state, and the e() escape helper everywhere on output. Bread-and-butter web dev. Beautiful when it works.