Project Overview & PHP/MySQL Setup

Personal Catalogue: PHP & MySQL

Chapter 1 · Project Overview & PHP/MySQL Setup

This is one of four courses building the exact same personal cataloguing tool in four genuinely different architectures — Personal Catalogue (React + Express + MongoDB), Personal Catalogue (React & Firebase), and Personal Catalogue (Django & PostgreSQL) are its siblings. This course's own answer is deliberately the simplest, fastest one to stand up: classic LAMP, chosen specifically because this project has a real deadline.

What the App Actually Does

The shared spec every course in the quartet builds toward:

Catalogue four kinds of item — books, CDs, DVDs, and Blu-rays — so it's possible to quickly check "do I already own this CD?" or "what Python books do I have?"
Tag books with free-form labels (a FastAPI book might get Python, Web Development, Programming, Web Frameworks) so related books can be found by topic later.
Add items manually for the first release — title, author/artist, format-specific details, tags where relevant.
Search and browse the collection to answer the "do I have this?" question quickly.

Barcode scanning is explicitly out of scope for this first release — a real, obvious way to add items even faster, but deliberately deferred rather than allowed to hold up a release that needs to ship quickly. This course closes with a real, concrete plan for adding it later (Chapter 9), rather than pretending it isn't wanted at all.

Why PHP + MySQL for This One

Of the four variants in this set, PHP + MySQL is the one built around genuine speed of delivery. No build step, no bundler, no separate frontend framework to stand up before a single item can be added — a PHP file saved to a folder and opened in a browser is already a running page. MySQL's own real relational structure is also a natural fit for this project's own data: books, CDs, DVDs, and Blu-rays share several real fields (title, format, year) while differing in others, and tags need a genuine many-to-many relationship against books specifically — both comfortably expressed as real, normalized MySQL tables, covered in full in Chapter 2.

This Course Assumes Real PHP/MySQL Groundwork Already Covered Elsewhere

This site's own PHP Intermediate course already covers PDO, prepared statements, and basic CRUD in real depth (Chapter 4 there). This course doesn't re-teach that material — it assumes it, and moves straight into this catalogue's own real schema and features.

A Real Deadline, Not Just a Framing Device

This project genuinely needs a working first release quickly. That constraint shapes real decisions throughout this course — plain PHP over a framework, MySQL's own native tooling over an ORM, and a deliberately un-fancy add-item form before any polish — not because those are always the "correct" choices, but because they're the right choices given this specific, real time pressure. Chapter 8 covers where the corners were deliberately cut, and what a slower, more careful version might have done differently.

Setting Up: PHP, MySQL & a Working Connection

A local PHP + MySQL environment (XAMPP, MAMP, or a native install of PHP and MySQL Server) is assumed from here on. Confirm both are working before continuing:

# Confirm PHP is installed and check the version
php -v

# Start MySQL's own command-line client
mysql -u root -p

Create the database this whole course builds on:

CREATE DATABASE catalogue CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

utf8mb4 is a deliberate choice, not the MySQL default — it correctly stores genuinely any real title or author name a personal library might contain, including accented characters and beyond, which the older utf8 encoding in MySQL doesn't fully cover.

A Single Config File for the Database Connection

Every later chapter's own PHP code connects through one shared file, kept outside the public web root where possible:

<?php
// config.php
$host = 'localhost';
$db   = 'catalogue';
$user = 'root';
$pass = ''; // set a real password in production
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}

PDO::ATTR_EMULATE_PREPARES => false is worth calling out specifically: it makes PDO use MySQL's own real, native prepared statements rather than PHP-side emulation — the genuinely correct setting for real SQL-injection protection, and one that's easy to leave at its default (true) without realizing the difference matters.

Nothing to build before the first page loads

Unlike the Django and React-based siblings in this set, there's no project scaffolding step here at all — config.php plus a working MySQL connection is the entire "setup," and the very next chapter starts writing real schema. This is exactly the kind of speed this variant was chosen for.

Don't skip the charset

Creating the database without an explicit utf8mb4 charset silently falls back to whatever the local MySQL install's own default happens to be — sometimes still latin1 on older setups. A book title or author name containing a character outside that older encoding's own range will either fail to insert or, worse, get silently mangled. Setting the charset explicitly, as above, avoids the problem outright rather than discovering it later.

Where This Course Is Headed

A real MySQL schema for items, item types, and a tags join table (Chapter 2); the CRUD pages for adding, editing, and deleting an item (Chapter 3); one form handling several genuinely different item shapes (Chapter 4); tags — attaching, managing, and filtering by tag on books (Chapter 5); real search across the whole catalogue (Chapter 6); the list and detail pages (Chapter 7); styling (Chapter 8); deployment, plus a real, concrete plan for barcode scanning as the next real phase (Chapter 9); and a capstone on integrating this catalogue into the existing Astro-based site (Chapter 10).

Hands-On Exercises

Exercise 1

Explain why barcode scanning was deliberately deferred rather than built into this first release, and what real project constraint drove that decision.

📄 View solution

Exercise 2

Explain what PDO::ATTR_EMULATE_PREPARES => false actually changes, and why it matters for this project's own real security.

📄 View solution

Exercise 3

Create the catalogue database yourself with the correct utf8mb4 charset, and write out a one-sentence explanation of why the charset was set explicitly rather than left at MySQL's own default.

📄 View solution

Chapter 1 Quick Reference

The shared app — a personal catalogue for books/CDs/DVDs/Blu-rays, tags on books, manual entry first, barcode scanning deferred
Why this variant — classic LAMP, chosen for genuine speed of delivery given the project's own real deadline
Database — MySQL, created with an explicit utf8mb4 charset
Connection — one shared config.php using PDO, with ATTR_EMULATE_PREPARES set to false
Assumed groundwork — PDO/prepared statements/CRUD, already covered in PHP Intermediate Chapter 4
Next chapter: Data Modeling — a real MySQL schema for items, item types, and a tags join table