CRUD PHP MySQL — WHERE Clause Disasters & Safe Deletes

<?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";

<?php
// transaction_example.php — Safe multi-table insert with rollback

declare(strict_types=1);

require_once 'db_connection.php';

function createOrder(PDO $pdo, int $userId, array $items): int
{
    // $items = [['product_id' => 1, 'quantity' => 2, 'price' => 19.99], ...]
    try {
        $pdo->beginTransaction();

        // Insert order header
        $orderSql = 'INSERT INTO orders (user_id, total) VALUES (:user_id, :total)';
        $orderStmt = $pdo->prepare($orderSql);
        $orderStmt->execute([
            ':user_id' => $userId,
            ':total'   => array_sum(array_column($items, 'price')),
        ]);
        $orderId = (int) $pdo->lastInsertId();

        // Insert each order item
        $itemSql = 'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (:order_id, :product_id, :quantity, :price)';
        $itemStmt = $pdo->prepare($itemSql);
        foreach ($items as $item) {
            $itemStmt->execute([
                ':order_id'   => $orderId,
                ':product_id' => $item['product_id'],
                ':quantity'   => $item['quantity'],
                ':price'      => $item['price'],
            ]);
        }

        $pdo->commit();
        return $orderId;

    } catch (PDOException $e) {
        $pdo->rollBack();
        // Log $e->getMessage() to monitoring system
        throw new RuntimeException('Order creation failed. Please try again.');
    }
}

// Usage
$items = [
    ['product_id' => 4, 'quantity' => 1, 'price' => 24.99],
    ['product_id' => 7, 'quantity' => 3, 'price' => 9.99],
];
try {
    $orderId = createOrder($pdo, 42, $items);
    echo "Order created with ID: $orderId\n";
} catch (RuntimeException $e) {
    echo "Failed to create order.\n";
    // Log actual error internally
}

<?php
// secure_crud.php — Defensive patterns for CRUD in production

declare(strict_types=1);

// ─────────────────────────────────────────────
// Output escaping — use this everywhere you display user-generated content
// ─────────────────────────────────────────────
function e(string $value): string
{
    return htmlspecialchars($value, ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8');
}

// ─────────────────────────────────────────────
// Authorization check — example for delete
// ─────────────────────────────────────────────
function canUserDeleteArticle(PDO $pdo, int $userId, int $articleId): bool
{
    $sql = 'SELECT COUNT(*) FROM articles WHERE id = :id AND author_id = :user_id';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([':id' => $articleId, ':user_id' => $userId]);
    return $stmt->fetchColumn() > 0;
}

// ─────────────────────────────────────────────
// Example secure delete endpoint logic
// ─────────────────────────────────────────────
function handleDeleteRequest(PDO $pdo, int $currentUserId, int $articleId): string
{
    if (!canUserDeleteArticle($pdo, $currentUserId, $articleId)) {
        return json_encode(['error' => 'Not authorized to delete this article.']);
    }

    try {
        $pdo->beginTransaction();
        // Assuming a deleteArticle function from earlier that returns bool
        $deleted = deleteArticle($pdo, $articleId);
        if (!$deleted) {
            $pdo->rollBack();
            return json_encode(['error' => 'Article not found.']);
        }
        $pdo->commit();
        return json_encode(['success' => true]);
    } catch (PDOException $e) {
        $pdo->rollBack();
        // Log error
        return json_encode(['error' => 'Deletion failed.']);
    }
}

// ─────────────────────────────────────────────
// Using output escaping in a view template (pseudo PHP)
// ─────────────────────────────────────────────
// <!-- article.php -->
// <h1><?= e($article['title']) ?></h1>
// <p><?= nl2br(e($article['body'])) ?></p>
// <small>By <?= e($article['author']) ?></small>

// io.thecodeforge — php tutorial
// Verify your PHP environment before trusting a single line

// 1. Check PDO driver availability
echo 'PDO drivers: ' . implode(', ', PDO::getAvailableDrivers()) . PHP_EOL;
// Output: PDO drivers: mysql, sqlite, pgsql

// 2. Test raw socket connection (will explode if wrong)
try {
    $pdo = new PDO('mysql:host=127.0.0.1;port=3306;dbname=crud_demo', 'root', '');
    echo 'Connection OK' . PHP_EOL;
    // Output: Connection OK
} catch (PDOException $e) {
    echo 'FAIL: ' . $e->getMessage() . PHP_EOL;
    // Output: FAIL: SQLSTATE[HY000] [1049] Unknown database 'crud_demo'
    exit(1);
}

// 3. Verify database exists
$stmt = $pdo->query('SELECT DATABASE()');
echo 'Active DB: ' . $stmt->fetchColumn() . PHP_EOL;
// Output: Active DB: crud_demo

// io.thecodeforge — php tutorial
// Clean separation — Database does one thing

class Database {
    private PDO $connection;

    public function __construct(string $dsn, string $user, string $pass) {
        $this->connection = new PDO($dsn, $user, $pass, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    }

    public function query(string $sql, array $params = []): PDOStatement {
        $stmt = $this->connection->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }
}

class UserRepository {
    public function __construct(private Database $db) {}

    public function findById(int $id): array {
        $stmt = $this->db->query('SELECT * FROM users WHERE id = ?', [$id]);
        $user = $stmt->fetch();
        return $user ?: [];
    }
}

// Usage — no HTML, no validation, just data
$db = new Database('mysql:host=127.0.0.1;dbname=crud_demo', 'root', '');
$repo = new UserRepository($db);
print_r($repo->findById(1));
// Output: Array ( [id] => 1 [name] => Alice [email] => alice@example.com )