Working With Databases

Course 2 · Ch 4

Working With Databases: PDO, Prepared Statements, and Basic CRUD

Connecting PHP to MySQL safely, and the four operations every data-driven page needs

Every example so far has worked with data that disappears the moment the script finishes. Real applications store data persistently in a database. This chapter introduces PDO (PHP Data Objects) — PHP's standard way of talking to a database — and CRUD: Create, Read, Update, Delete, the four operations behind almost every data-driven feature.

Connecting With PDO

<?php
  $host = "localhost";
  $db   = "my_database";
  $user = "db_user";
  $pass = "secret_password";

  try {
    $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  } catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
  }
?>

A PDO connection failure throws a PDOException — exactly the exception handling from Chapter 3, applied immediately to something genuinely likely to fail in the real world (wrong credentials, database server down). ERRMODE_EXCEPTION makes every later database error throw an exception too, rather than failing silently.

Never write a database password directly in a file inside a public web folder

The example above uses a plain variable for clarity. In a real project, credentials belong in a separate config file stored outside the publicly accessible web root, or in environment variables — never committed to git, and never reachable by a direct URL request.

The SQL Injection Problem

<?php
  // DO NOT DO THIS — directly inserting user input into SQL text
  $username = $_GET['username'];
  $sql = "SELECT * FROM users WHERE username = '$username'";
  $pdo->query($sql);   // dangerously vulnerable
?>

Concatenating user input straight into SQL text lets an attacker change the query's actual meaning

SQL injection is one of the most serious, well-known web vulnerabilities — and it's avoided entirely with the technique below

By submitting carefully crafted text instead of a normal username, an attacker can change what the SQL statement actually does — bypassing a login check, or reading data they shouldn't see. This is exactly why raw string concatenation into SQL must never be done with any value that came from user input.

Prepared Statements — The Fix

<?php
  $username = $_GET['username'] ?? '';

  $stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username");
  $stmt->execute(['username' => $username]);
  $user = $stmt->fetch();
?>

:username is a placeholder — the actual value is sent to the database completely separately from the SQL text itself, in a second step (execute()). The database treats the placeholder's value strictly as data, never as part of the SQL command's structure — meaning the attacker's text from above could never be reinterpreted as part of the query's logic. This single habit eliminates SQL injection entirely.

CRUD — The Four Core Operations

Create

INSERT INTO ...

Read

SELECT ... FROM ...

Update

UPDATE ... SET ...

Delete

DELETE FROM ...

Create — INSERT

<?php
  $stmt = $pdo->prepare("INSERT INTO posts (title, body) VALUES (:title, :body)");
  $stmt->execute(['title' => "My First Post", 'body' => "Hello, world!"]);
  echo "New post ID: " . $pdo->lastInsertId();
?>

Read — SELECT

<?php
  $stmt = $pdo->prepare("SELECT * FROM posts WHERE id = :id");
  $stmt->execute(['id' => 1]);
  $post = $stmt->fetch();         // one row, as an associative array (Chapter 6)

  $allPosts = $pdo->query("SELECT * FROM posts")->fetchAll();   // every row, as an array of arrays
  foreach ($allPosts as $post) {
    echo $post['title'] . "<br>";
  }
?>

Update — UPDATE

<?php
  $stmt = $pdo->prepare("UPDATE posts SET title = :title WHERE id = :id");
  $stmt->execute(['title' => "Updated Title", 'id' => 1]);
?>

Delete — DELETE

<?php
  $stmt = $pdo->prepare("DELETE FROM posts WHERE id = :id");
  $stmt->execute(['id' => 1]);
?>

UPDATE and DELETE without a WHERE clause affect EVERY row in the table

Forgetting the WHERE id = :id condition on an UPDATE or DELETE statement is a genuinely common, genuinely serious mistake — it silently applies the change to the entire table rather than the one row intended. Always double-check a WHERE clause is present before running either statement against a real database.

fetchAll() Fetch Modes

By default, PDO returns rows as both numeric AND associative keys combined — usually more than needed. Setting the fetch mode once after connecting keeps results clean and predictable:

<?php
  $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
  // from now on, every fetch()/fetchAll() returns clean associative arrays only
?>

Coding Challenges

Challenge 1

Write the PDO connection code for a database called "blog_db" on localhost, wrapped in a try/catch that dies with a friendly message on failure. Set both ERRMODE_EXCEPTION and FETCH_ASSOC as attributes.

📄 View solution

Challenge 2

Assuming a "products" table with columns id, name, and price, write prepared statements (with placeholders) for: inserting a new product, selecting all products with a price above a given value, and updating a specific product's price by id.

📄 View solution

Challenge 3

Write a function deleteProductById($pdo, $id) that uses a prepared DELETE statement, wrapped in a try/catch for PDOException, echoing a success or failure message. Explain in a comment why this function would be unsafe if $id were inserted directly into the SQL string instead of used as a placeholder.

📄 View solution

Chapter 4 Quick Reference

new PDO("mysql:host=...;dbname=...", $user, $pass) — connect; wrap in try/catch for PDOException
ATTR_ERRMODE / ERRMODE_EXCEPTION — makes database errors throw exceptions instead of failing silently
Never concatenate user input into SQL strings — this is SQL injection, a serious vulnerability
prepare() + execute([...]) — prepared statements with named (:placeholder) parameters fix this entirely
CRUD: INSERT (Create), SELECT (Read), UPDATE, DELETE
fetch() / fetchAll() — one row / all rows, as associative arrays with FETCH_ASSOC set
lastInsertId() — gets the auto-increment ID of a just-inserted row
Always include a WHERE clause on UPDATE/DELETE, or risk affecting every row
Next chapter: sessions and cookies — login systems, authentication basics