CRUD with PHP and MySQL: Build, Query and Protect a Real Database

<?php
// db_connection.php — reusable PDO connection factory
// Call getPDO() anywhere in your app to get a safe, configured connection.

declare(strict_types=1);

function getPDO(): PDO
{
    // Store credentials in environment variables, never hard-coded in production.
    // For local development a constants file is acceptable.
    $host     = 'localhost';
    $dbName   = 'forge_blog';   // The database we created in MySQL
    $username = 'root';         // Replace with a least-privilege DB user in prod
    $password = '';             // Never commit real passwords to version control

    // utf8mb4 supports the full Unicode range including emoji — utf8 does not.
    $dsn = "mysql:host={$host};dbname={$dbName};charset=utf8mb4";

    $options = [
        // Throw exceptions on any DB error — no silent failures.
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

        // Return rows as associative arrays by default — e.g. $row['title']
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

        // Disable emulated prepares so MySQL handles parameter binding natively.
        // This closes an entire class of edge-case injection vulnerabilities.
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        $pdo = new PDO($dsn, $username, $password, $options);
        return $pdo;
    } catch (PDOException $e) {
        // In production, log $e->getMessage() and show a generic error page.
        // Never expose raw database errors to the browser.
        throw new RuntimeException('Database connection failed. Check credentials.');
    }
}

// --- Companion SQL to create the table (run once in MySQL) ---
// CREATE DATABASE forge_blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
// USE forge_blog;
// CREATE TABLE articles (
//     id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
//     title      VARCHAR(255)  NOT NULL,
//     body       TEXT          NOT NULL,
//     author     VARCHAR(100)  NOT NULL,
//     created_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
//     updated_at TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
// );

<?php
// articles_create_read.php — Create and Read operations for the articles table

declare(strict_types=1);

require_once 'db_connection.php';

$pdo = getPDO();

// ─────────────────────────────────────────────
// CREATE — Insert a new article
// ─────────────────────────────────────────────
function createArticle(PDO $pdo, string $title, string $body, string $author): int
{
    // Named placeholders (:title, :body, :author) keep the query readable
    // and completely separate structure from data — SQL injection impossible.
    $sql = 'INSERT INTO articles (title, body, author) VALUES (:title, :body, :author)';

    $statement = $pdo->prepare($sql);  // Parse the query structure once

    // bindValue sends the actual value to the driver with its type declared.
    // PDO::PARAM_STR tells the driver to treat it as a string, not an integer.
    $statement->bindValue(':title',  $title,  PDO::PARAM_STR);
    $statement->bindValue(':body',   $body,   PDO::PARAM_STR);
    $statement->bindValue(':author', $author, PDO::PARAM_STR);

    $statement->execute();  // Runs the query with the bound values

    // lastInsertId() returns the auto-incremented ID of the row just created.
    // Cast to int because it comes back as a string from the driver.
    return (int) $pdo->lastInsertId();
}

// ─────────────────────────────────────────────
// READ ONE — Fetch a single article by its ID
// ─────────────────────────────────────────────
function getArticleById(PDO $pdo, int $articleId): array|false
{
    $sql = 'SELECT id, title, body, author, created_at FROM articles WHERE id = :id LIMIT 1';

    $statement = $pdo->prepare($sql);
    $statement->bindValue(':id', $articleId, PDO::PARAM_INT); // PARAM_INT — type matters
    $statement->execute();

    // fetch() returns one row as an associative array, or false if no row matched.
    // Always check for false before using the result.
    return $statement->fetch();
}

// ─────────────────────────────────────────────
// READ MANY — Fetch all articles by a given author
// ─────────────────────────────────────────────
function getArticlesByAuthor(PDO $pdo, string $author): array
{
    $sql = 'SELECT id, title, author, created_at FROM articles WHERE author = :author ORDER BY created_at DESC';

    $statement = $pdo->prepare($sql);
    $statement->bindValue(':author', $author, PDO::PARAM_STR);
    $statement->execute();

    // fetchAll() returns every matching row — an empty array if none matched, never false.
    return $statement->fetchAll();
}

// ─────────────────────────────────────────────
// --- Demo execution ---
// ─────────────────────────────────────────────

// Create two articles
$firstId  = createArticle($pdo, 'Getting Started with PHP', 'PHP is a server-side language...', 'Ada Lovelace');
$secondId = createArticle($pdo, 'MySQL Indexing Deep Dive', 'Indexes speed up reads dramatically...', 'Ada Lovelace');

echo "Created articles with IDs: {$firstId} and {$secondId}\n\n";

// Read one back
$article = getArticleById($pdo, $firstId);

if ($article === false) {
    echo "Article not found.\n";
} else {
    echo "Title:  {$article['title']}\n";
    echo "Author: {$article['author']}\n";
    echo "Posted: {$article['created_at']}\n\n";
}

// Read all by that author
$authorArticles = getArticlesByAuthor($pdo, 'Ada Lovelace');

echo "Articles by Ada Lovelace:\n";
foreach ($authorArticles as $row) {
    echo "  [{$row['id']}] {$row['title']}\n";
}

<?php
// articles_update_delete.php — Update and Delete operations for the articles table

declare(strict_types=1);

require_once 'db_connection.php';

$pdo = getPDO();

// ─────────────────────────────────────────────
// UPDATE — Change the title and body of an article by ID
// ─────────────────────────────────────────────
function updateArticle(PDO $pdo, int $articleId, string $newTitle, string $newBody): bool
{
    // The WHERE id = :id clause is non-negotiable.
    // Without it, EVERY row in the table would be overwritten.
    $sql = 'UPDATE articles SET title = :title, body = :body WHERE id = :id';

    $statement = $pdo->prepare($sql);
    $statement->bindValue(':title', $newTitle,   PDO::PARAM_STR);
    $statement->bindValue(':body',  $newBody,    PDO::PARAM_STR);
    $statement->bindValue(':id',    $articleId,  PDO::PARAM_INT);

    $statement->execute();

    // rowCount() tells us how many rows were actually modified.
    // 0 can mean the ID didn't exist OR the data was identical — both are valid.
    // We return true if at least one row changed, false otherwise.
    return $statement->rowCount() > 0;
}

// ─────────────────────────────────────────────
// DELETE — Remove an article permanently by ID
// ─────────────────────────────────────────────
function deleteArticle(PDO $pdo, int $articleId): bool
{
    // Again, WHERE id = :id is the critical guard.
    // Omitting WHERE on a DELETE clears the entire table — instant data loss.
    $sql = 'DELETE FROM articles WHERE id = :id';

    $statement = $pdo->prepare($sql);
    $statement->bindValue(':id', $articleId, PDO::PARAM_INT);
    $statement->execute();

    // If rowCount() is 1, the row was found and deleted.
    // If it's 0, no row with that ID existed — caller can handle this case.
    return $statement->rowCount() === 1;
}

// ─────────────────────────────────────────────
// TRANSACTION EXAMPLE — Delete with safety net
// Use transactions when multiple queries must all succeed or all fail together.
// ─────────────────────────────────────────────
function deleteArticleWithAudit(PDO $pdo, int $articleId, string $deletedBy): bool
{
    try {
        $pdo->beginTransaction(); // Start — nothing commits until we say so

        // Step 1: Log the deletion intent before removing the data
        $logSql = 'INSERT INTO audit_log (action, target_id, performed_by) VALUES (:action, :target, :user)';
        $logStmt = $pdo->prepare($logSql);
        $logStmt->execute([
            ':action' => 'delete_article',
            ':target' => $articleId,
            ':user'   => $deletedBy,
        ]);

        // Step 2: Remove the article itself
        $deleted = deleteArticle($pdo, $articleId);

        if (!$deleted) {
            // Article didn't exist — roll back the audit log entry too
            $pdo->rollBack();
            return false;
        }

        $pdo->commit(); // Both steps succeeded — make it permanent
        return true;

    } catch (PDOException $e) {
        $pdo->rollBack(); // Something went wrong — undo everything
        throw $e;         // Re-throw so the caller can log or handle it
    }
}

// ─────────────────────────────────────────────
// --- Demo execution ---
// (Assumes articles with ID 1 and 2 exist from the previous example)
// ─────────────────────────────────────────────

// Update article 1
$wasUpdated = updateArticle($pdo, 1, 'Getting Started with PHP — Revised Edition', 'Updated body content here...');
echo $wasUpdated
    ? "Article 1 updated successfully.\n"
    : "Article 1: no changes made (identical data or ID not found).\n";

// Try updating an ID that doesn't exist
$missingUpdate = updateArticle($pdo, 9999, 'Ghost Article', 'This should not work.');
echo $missingUpdate
    ? "Unexpected: ghost article updated.\n"
    : "Article 9999 not found — nothing updated.\n";

// Delete article 2
$wasDeleted = deleteArticle($pdo, 2);
echo $wasDeleted
    ? "Article 2 deleted successfully.\n"
    : "Article 2 not found — nothing deleted.\n";