Challenge 2 — Solution

Task: Identify the N+1 query problem in this code, and rewrite it using
a single JOIN query instead:
$comments = $pdo->query("SELECT * FROM comments")->fetchAll();
foreach ($comments as $c) { $stmt = $pdo->prepare("SELECT name FROM
users WHERE id = :id"); $stmt->execute(['id' => $c['user_id']]);
$author = $stmt->fetch(); }

// The N+1 problem: this code runs ONE query to fetch every comment,
// then runs ONE MORE query per comment inside the foreach loop to look
// up that comment's own author - for 50 comments, that's 1 + 50 = 51
// total database round-trips, instead of just 1.

<?php
    // BAD — the original N+1 version:
    $comments = $pdo->query("SELECT * FROM comments")->fetchAll();
    foreach ($comments as $c) {
        $stmt = $pdo->prepare("SELECT name FROM users WHERE id = :id");
        $stmt->execute(['id' => $c['user_id']]);
        $author = $stmt->fetch();
    }

    // GOOD — a single JOIN query instead:
    $comments = $pdo->query("
        SELECT comments.*, users.name AS author_name
        FROM comments
        JOIN users ON users.id = comments.user_id
    ")->fetchAll();

    foreach ($comments as $comment) {
        echo $comment['author_name'] . ": " . $comment['body'] . "<br>";
    }
?>


Notes:
- The JOIN version runs exactly ONE query total, regardless of how many
  comments exist - fetching every comment together with its own
  author's name in a single database round-trip, using an "author_name"
  alias to avoid a naming collision with any "name" column that might
  also exist on the comments table itself.
- The rewritten foreach loop no longer needs any database calls inside
  it at all - $comment['author_name'] is already present on each row
  directly from the JOIN, exactly matching the chapter's own worked
  example with posts and their authors.
- This is a structural fix (combining the two separate queries into
  one), not a "make PHP run faster" fix - the chapter's own point that
  N+1 is one of the single most common real-world PHP performance
  problems, and the fix is almost always at the SQL level, not the PHP
  level.