Relational Databases, SQL & PDO — PHP Course

What you'll learn

Connect PHP to a relational database without mixing SQL, credentials, and HTML. You will establish a hardened PDO connection, execute parameterized statements, and shape returned rows intentionally.

By the end of this lesson, you'll be able to:

Create a safe PDO connection
Execute parameterized SELECT and INSERT statements
Choose appropriate fetch modes and data boundaries

Core mental model

Professional PHP is less about memorizing APIs and more about choosing a clear boundary for each responsibility. Use this table as a decision guide while reading the examples.

Concept	What it protects	Decision rule
Prepared statement	Code/data separation	Bind every external value; never concatenate it into SQL.
Relational key	Stable row identity	Use primary and foreign keys to enforce relationships.
Fetch mode	Predictable result shape	Select only needed columns and choose the shape explicitly.

Professional workflow

Build the feature in small, verifiable steps. Each step leaves the system in a state you can test.

Describe the database access boundary: its inputs, outputs, invariants, and expected failures.
Implement the smallest happy path behind an explicit contract.
Add validation and translate low-level failures into language the caller understands.
Exercise the boundary with realistic data, then inspect output, logs, and resource cleanup.
Refactor only after behavior is protected by a repeatable check.

Make the boundary visible

Name inputs, outputs, side effects, and failure cases before adding framework or infrastructure code. That habit keeps advanced PHP understandable as the application grows.

Guided code lab

Connect with failure enabled

Credentials arrive from configuration and PDO throws exceptions instead of returning ambiguous false values.

database.php

<?php
$pdo = new PDO(
    $_ENV['DB_DSN'],
    $_ENV['DB_USER'],
    $_ENV['DB_PASSWORD'],
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ],
);

Bind values, not SQL syntax

The placeholder represents data only; the database parses the statement separately.

find-courses.php

<?php
$statement = $pdo->prepare(
    'SELECT id, title, published_at
       FROM courses
      WHERE title LIKE :query AND published_at IS NOT NULL
      ORDER BY published_at DESC
      LIMIT :limit'
);
$statement->bindValue('query', '%' . $search . '%');
$statement->bindValue('limit', 20, PDO::PARAM_INT);
$statement->execute();
$courses = $statement->fetchAll();

Production practice

Contract

Repositories receive typed values and return deliberate row or domain shapes, never a shared global PDO handle.

Verification

Test against the real database engine for SQL behavior; include empty results, Unicode, and constraint failures.

Operations

Keep credentials outside source, set connection timeouts, and expose slow-query visibility.

Common failure mode

Placeholders cannot stand for identifiers such as column names. Allow-list sortable columns instead of binding or concatenating arbitrary input.

Independent workshop

Create a PDO-backed course catalog with create, exact lookup, and safe title search operations.

Your finished workshop must include:

Schema with constraints
Parameterized repository methods
A CLI script proving success and not-found paths

Definition of done

Run the happy path and at least two failure paths, explain one design tradeoff in a short README, and leave the code formatted and ready for review.

Recap & quick check

Key takeaways

PDO offers one consistent database API.
Prepared statements separate SQL from values.
Constraints protect data beyond PHP.
Fetch only the columns and rows you need.

Quick check

1. What prevents an external value becoming SQL code?

2. Should credentials be committed?

3. Can a placeholder bind a column name?

Keep the workshop: later phases deliberately build on these boundaries, so today's small example can become part of your portfolio architecture.