PHP MySQL Cheat Sheet: PDO, Prepared Statements, CRUD & Transactions

PHP talks to MySQL through PDO (or mysqli) using ERRMODE_EXCEPTION, prepared statements, and bound parameters. CRUD is INSERT, SELECT, UPDATE, and DELETE, with transactions around multi-step writes. Never build SQL by interpolating user input into the query string.

This PHP cheat sheet is written for people searching for a fast, accurate reference: students, junior developers, and teams shipping production software. Pin it, copy the snippets, and come back when syntax slips.

Quick reference

• DSN: mysql:host=127.0.0.1;dbname=app;charset=utf8mb4.
• Connect: new PDO($dsn, $user, $pass, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]).
• Prepare: $pdo->prepare("... WHERE id = ?") then execute([$id]).
• Fetch: fetch(PDO::FETCH_ASSOC) one row, fetchAll many.
• Insert id: $pdo->lastInsertId() after INSERT on the same connection.
• Update/Delete: Check $stmt->rowCount() when you care if a row matched.
• Transactions: beginTransaction, commit, rollBack on exceptions.
• Charset: utf8mb4 in DSN and tables so emoji and 4-byte UTF-8 work.

Copy-paste examples

PDO CRUD core

One connection, reused. Catch PDOException at a boundary, not around every line if you prefer to fail fast.

<?php
function pdo(): PDO
{
    static $pdo = null;
    if ($pdo === null) {
        $pdo = new PDO(
            'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
            'app',
            'secret',
            [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
        );
    }
    return $pdo;
}
function createPost(PDO $pdo, string $title): int
{
    $stmt = $pdo->prepare('INSERT INTO posts (title) VALUES (:title)');
    $stmt->execute(['title' => $title]);
    return (int) $pdo->lastInsertId();
}

Common mistakes

• Embedding $_POST in "SELECT * FROM users WHERE id = $id".
• Using the root MySQL user from the web app.
• Forgetting charset=utf8mb4 and corrupting emoji.
• Catching exceptions and returning 200 OK with an empty page.

FAQ

1. PDO or mysqli? PDO is database-agnostic and is the usual teaching default. mysqli is MySQL-only. Both can be safe with prepared statements.

1. Does Eloquent replace this? Laravel’s Eloquent uses PDO underneath. You still need to understand SQL and N+1 queries.

1. How do I avoid N+1 in PHP loops? One query with WHERE id IN (...) or a JOIN, not a query per row in foreach.

Related PHP cheat sheets

• Php Cheat Sheet 2026
• Php Arrays Functions Cheat Sheet
• Php Oop Cheat Sheet

Build with this stack

When a cheat sheet is not enough — you need a production app, a student FYP, or a custom dashboard — ArpaNeuro builds custom web development and also sells ready-made source code. Request a quote and tell us the stack.

Browse software development services or the source code marketplace if you want a working codebase instead of starting from a blank file.