We've spent the last few lessons talking about the individual components—how to connect to a database, how to handle POST requests, and how to write SQL. Now, it's time to actually glue it all together. We're going to build a simple Personal Book Collection Manager. It's a classic CRUD (Create, Read, Update, Delete) app, which is the backbone of almost every web application you'll ever build.

Setting up the Library Table

I don't like over-engineering things early on, so we'll keep the schema lean. We just need a table to hold our books. I'm using an auto-incrementing ID because trying to manage unique identifiers manually is a nightmare you don't want.

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    year INT
);

Getting Books into the Database

For the "Create" part, we need a simple HTML form and a PHP script to process it. I usually put my logic at the top of the page to keep the HTML clean. Notice I'm using prepared statements here—I can't stress enough how important this is for security. Never, ever trust user input.

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['add_book'])) {
    $stmt = $pdo->prepare("INSERT INTO books (title, author, year) VALUES (?, ?, ?)");
    $stmt->execute([$_POST['title'], $_POST['author'], $_POST['year']]);
}

The form is straightforward: three inputs and a submit button. I'll name the button add_book so I can distinguish this request from others on the same page.

Displaying the Collection

Now for the "Read" part. This is the easiest bit. I'll fetch everything from the books table and loop through it. I prefer using a while loop with fetch() because it's more memory-efficient than pulling a massive array into memory if your library grows to thousands of books.

$stmt = $pdo->query("SELECT * FROM books");
while ($row = $stmt->fetch()) {
    echo "<div>";
    echo "<strong>" . htmlspecialchars($row['title']) . "</strong> by " . htmlspecialchars($row['author']);
    echo " <a href='edit.php?id=" . $row['id'] . "'>Edit</a>";
    echo " <a href='delete.php?id=" . $row['id'] . "'>Delete</a>";
    echo "</div>";
}

Tweaking the Update Logic and Fixing a Refresh Bug

Updating a record requires two steps: first, we fetch the existing data to fill a form, and then we save the changes. Here is where I actually tripped up when I first wrote this. I wrote the update logic, tested it, and it worked. But then I hit "Refresh" in my browser after submitting the form, and I realized it sent the POST request again. In a more complex app, this could cause duplicate entries or weird state issues.

To fix this, I implemented the Post-Redirect-Get (PRG) pattern. Instead of just letting the page render after the update, I'll send a header('Location: ...') redirect. This clears the POST data and sends the user back to the list.

// Inside edit.php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare("UPDATE books SET title = ?, author = ?, year = ? WHERE id = ?");
    $stmt->execute([$_POST['title'], $_POST['author'], $_POST['year'], $_POST['id']]);
    
    // My mistake was omitting this line originally:
    header("Location: index.php"); 
    exit;
}

Clearing out the Old Stuff
Finally, we have "Delete". I'm using a simple GET request for this example because it's a small internal tool. In a production environment, you'd probably want a confirmation dialog or even a POST request to prevent accidental deletions from search engine crawlers or mistaken clicks.
if (isset($_GET['id'])) {
    $stmt = $pdo->prepare("DELETE FROM books WHERE id = ?");
    $stmt->execute([$_GET['id']]);
    header("Location: index.php");
    exit;
}
And that's it. We've built a full loop: adding data, viewing it, modifying it, and removing it. It's not fancy, but it's the foundation of almost everything you'll do in PHP web development.

📋 Practical Task

Adding a "Genre" Field to the Library Manager

Now it's your turn to extend the application. The current Book Collection Manager is too simple; we need to know what kind of books we have.

• Modify your books table to add a genre column (VARCHAR).
• Update the "Add Book" form and the corresponding INSERT logic to handle the new genre field.
• Update the "Edit" page so you can change the genre of an existing book.
• Display the genre next to the author in the main book list.