Book Swap: Your First Database Website

In one lesson you will build a MySQL database yourself in phpMyAdmin, then connect a two-page website to it: a list of books and a form that adds new books.

Form 6 students want to swap their old textbooks. You are building the Book Swap site: anyone can see what books are on offer, and add a book they are willing to swap.

Hand in

1. A screenshot of your books table’s Structure tab in phpMyAdmin.
2. Your three SQL queries from Part B, each with a screenshot of its result.
3. A screenshot of your website listing your books, including one you added through the form.
4. Your answers to the five check-up questions at the end.

Part A · Create the database (15 min)

1. Start WampServer. Wait until its tray icon is green.
2. Open http://localhost/phpmyadmin. Log in as root with no password and choose the MySQL server.
3. Click the Databases tab. Under Create database, type bookswap, choose the collation utf8mb4_unicode_ci, and click Create.
4. phpMyAdmin opens the new database and asks you to create a table. Name it books, set Number of columns to 6, and click Create (or Go).
5. Fill in the six columns exactly as shown, then click Save.

Check: open the table’s Structure tab. You should see six columns, a key icon beside book_id, and AUTO_INCREMENT in its Extra column. Take your screenshot.

Part B · Add data and ask questions (10 min)

1. Click the Insert tab. Add three books of your own. Leave book_id empty, because MySQL numbers it. available is already filled in with 1 (its default), so leave that too. Click Go after each book.
2. Click Browse to see your rows. Each book should have its own book_id and available = 1.
3. Open the SQL tab and write a query for each task below. Record each query and screenshot its result.

Part C · Connect the website (25 min)

1. Unzip the starter kit into C:\wamp64\www\, so that you have C:\wamp64\www\bookswap\index.php.
2. Open http://localhost/bookswap/. The page loads but says 0 book(s) listed. It is connected, but not reading anything yet. (A connection error? Check WAMP is green and the database is named bookswap. If you used MariaDB, change the port in includes/db.php to 3307.)
3. Open the bookswap folder in your editor and complete the three TODOs.

How the files fit together: includes/db.php opens the connection to MySQL (it is finished; read it). index.php lists the books. add.php shows the form and saves new books. style.css styles both pages.

Two patterns you will need:

// Reading rows: run a query, then fetch every row into an array
$rows = $pdo->query('SELECT title FROM books')->fetchAll();
foreach ($rows as $r) { echo e($r['title']); }

// Using anything the visitor typed: a prepared statement with ? placeholders
$stmt = $pdo->prepare('SELECT * FROM books WHERE owner = ?');
$stmt->execute([$owner]);

Hint: copy the reading pattern for the “all books” case, and use ORDER BY book_id DESC for newest first. For a chosen subject, use the prepared-statement pattern with WHERE subject = ?.

Hint: use <?php foreach ($books as $b): ?> … <?php endforeach; ?> around one <tr>. Print each value with <?= e($b['title']) ?>.

Hint: INSERT INTO books (title, author, subject, owner) VALUES (?, ?, ?, ?), then $stmt->execute([...]). You don’t insert book_id or available, because the database fills them in. After saving, send the visitor back with header('Location: index.php?added=1'); exit;

Part D · Test it (5 min)

Extension · Mark a book as swapped

Then add this button to the last cell of each row in TODO 2, so every available book can be marked as swapped:

<form method="post" action="swap.php" style="display:inline">
  <input type="hidden" name="book_id" value="<?= e($b['book_id']) ?>">
  <button class="small" type="submit">Mark swapped</button>
</form>

Check-up questions

1. Why is book_id the primary key, and why is it set to AUTO_INCREMENT?
2. What is the difference between MySQL and phpMyAdmin?
3. Why does add.php use a prepared statement instead of putting the visitor’s text straight into the SQL?
4. Why is the connection code kept in one file, includes/db.php, which every page includes?
5. What do the letters W, A, M and P in WAMP stand for, and what does each one do when you open index.php?

Finished early? Try the full practical, Sweet Escape Goes Live, with a menu, an order form with validation and a staff report.