MySQL #

MySQL is the most popular relational database in the PHP world — almost every shared host supports it, and it powers millions of web applications from WordPress blogs to large-scale e-commerce stores. PHP provides two ways to access MySQL: PDO (PHP Data Objects), which is database-agnostic, and MySQLi, which is MySQL-specific. PDO is almost always the better choice — the same code can run on MySQL, PostgreSQL, SQLite, or other databases by simply changing the connection DSN. This article covers all the important aspects: correct connections, prepared statements as the only safe way to send data to a database, all the fetch modes, transactions, and repository patterns that make PHP code easy to test.

PDO vs MySQLi — Choosing Correctly #

flowchart TD
    A{Only\nusing MySQL?} -- Yes --> B{Need\nMySQL-specific\nfeatures?}
    B -- Yes --> C[MySQLi\nExample: async queries,\nmulti-statement]
    B -- No --> D[PDO\nMore portable and\nconsistent]
    A -- No --> D

    style D fill:#dcfce7,stroke:#16a34a
    style C fill:#dbeafe

Aspect	PDO	MySQLi
Supported databases	13+ (MySQL, PostgreSQL, SQLite, etc.)	MySQL/MariaDB only
API	OOP	OOP + Procedural
Named parameters	`:name`	No (only `?`)
Prepared statements	✓	✓
Stored procedures	✓	✓
Multi-statement	✗	✓
Async queries	✗	✓

Use PDO as the default. Only choose MySQLi if you need genuinely MySQL-specific features like multi-statement or async queries.

Connecting with PDO #

<?php
declare(strict_types=1);

// DSN (Data Source Name) — format: driver:host=...;dbname=...;charset=...
$dsn = 'mysql:host=localhost;port=3306;dbname=myapp;charset=utf8mb4';

$options = [
    // Error mode: throw PDOException on errors (not return false)
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    // Default fetch mode: associative array
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    // Disable prepared statement emulation — use native MySQL prepared statements
    // Safer and more efficient
    PDO::ATTR_EMULATE_PREPARES   => false,

    // Persistent connection — reuse existing connections (simple connection pooling)
    // PDO::ATTR_PERSISTENT => true, // enable if needed

    // Connection timeout
    PDO::ATTR_TIMEOUT => 5,
];

try {
    $pdo = new PDO($dsn, username: 'root', password: 'secret', options: $options);
    echo "Connection successful\n";
} catch (PDOException $e) {
    // Don't show error details to users in production
    error_log("Database connection failed: " . $e->getMessage());
    throw new \RuntimeException("Service unavailable. Please try again later.");
}

Singleton Connection #

<?php
class Database
{
    private static ?PDO $instance = null;

    private function __construct() {}
    private function __clone() {}

    public static function connection(): PDO
    {
        if (self::$instance === null) {
            $dsn = sprintf(
                'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
                getenv('DB_HOST') ?: 'localhost',
                getenv('DB_PORT') ?: 3306,
                getenv('DB_NAME') ?: 'myapp',
            );

            self::$instance = new PDO(
                $dsn,
                getenv('DB_USER') ?: 'root',
                getenv('DB_PASS') ?: '',
                [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                ]
            );
        }

        return self::$instance;
    }
}

$pdo = Database::connection();

Prepared Statements — The Only Safe Way #

SQL injection is one of the most dangerous and easiest-to-prevent vulnerabilities. Prepared statements separate SQL from data — the database treats data as data, not as part of the query.

<?php
// ANTI-PATTERN: direct string interpolation — VERY DANGEROUS
$name = $_GET['name']; // e.g.: "' OR '1'='1"
$query = "SELECT * FROM users WHERE name = '$name'";
// The query becomes: SELECT * FROM users WHERE name = '' OR '1'='1'
// This fetches ALL users! SQL injection succeeded.
$pdo->query($query); // DON'T DO THIS

// CORRECT: prepared statement with named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :name AND active = :active");
$stmt->execute([':name' => $name, ':active' => true]);
$users = $stmt->fetchAll();

// Or with positional parameters (?)
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = ? AND active = ?");
$stmt->execute([$name, true]);

// Named parameters are clearer and can be reused within one query
$stmt = $pdo->prepare("
    UPDATE users
    SET name = :name, email = :email, updated_at = NOW()
    WHERE id = :id
");
$stmt->execute([
    ':name'  => 'Budi Santoso',
    ':email' => '[email protected]',
    ':id'    => 42,
]);
echo $stmt->rowCount(); // number of affected rows

Binding Parameters with Explicit Types #

<?php
// bindParam() — binds a variable by reference (evaluated at execute time)
// bindValue() — binds a value directly (safer for loops)

$stmt = $pdo->prepare("
    INSERT INTO products (name, price, stock, active)
    VALUES (:name, :price, :stock, :active)
");

// bindValue with explicit types
$stmt->bindValue(':name',   'Pro Laptop',     PDO::PARAM_STR);
$stmt->bindValue(':price',  15_000_000,       PDO::PARAM_INT);
$stmt->bindValue(':stock',  5,                PDO::PARAM_INT);
$stmt->bindValue(':active', true,             PDO::PARAM_BOOL);
$stmt->execute();

$insertId = (int) $pdo->lastInsertId();
echo "New product ID: $insertId\n";

// bindParam — variable by reference, useful for insert loops
$stmt = $pdo->prepare("INSERT INTO logs (message, level) VALUES (:message, :level)");
$stmt->bindParam(':message', $message, PDO::PARAM_STR);
$stmt->bindParam(':level', $level, PDO::PARAM_STR);

$logs = [
    ['message' => 'Server start', 'level' => 'info'],
    ['message' => 'Request received', 'level' => 'debug'],
];

foreach ($logs as $log) {
    $message = $log['message']; // bindParam captures the variable by reference
    $level   = $log['level'];
    $stmt->execute(); // uses the current values of $message and $level
}

Fetch Modes — Retrieving Data #

PDO provides various ways to retrieve query results:

<?php
$stmt = $pdo->prepare("SELECT id, name, email, price FROM products WHERE active = 1");
$stmt->execute();

// fetch() — one row at a time (memory-efficient for many rows)
while ($row = $stmt->fetch()) {
    echo "{$row['id']}: {$row['name']}\n";
}

// fetchAll() — all rows at once (easy but memory-heavy for large datasets)
$stmt->execute();
$all = $stmt->fetchAll();

// FETCH_ASSOC (default) — associative array
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// ['id' => 1, 'name' => 'Laptop', 'email' => '...', 'price' => 15000000]

// FETCH_NUM — numeric array
$row = $stmt->fetch(PDO::FETCH_NUM);
// [1, 'Laptop', '...', 15000000]

// FETCH_BOTH — both (old PDO default — avoid, wasteful)
$row = $stmt->fetch(PDO::FETCH_BOTH);

// FETCH_OBJ — stdClass object
$row = $stmt->fetch(PDO::FETCH_OBJ);
echo $row->name;   // Laptop
echo $row->price;  // 15000000

// FETCH_CLASS — directly into a specific class
class Product
{
    public int    $id;
    public string $name;
    public float  $price;

    public function formattedPrice(): string
    {
        return 'Rp ' . number_format($this->price, 0, ',', '.');
    }
}

$stmt->setFetchMode(PDO::FETCH_CLASS, Product::class);
$product = $stmt->fetch();
echo $product->formattedPrice(); // Rp 15.000.000

// fetchColumn() — one column from one row
$stmt = $pdo->query("SELECT COUNT(*) FROM products WHERE active = 1");
$total = (int) $stmt->fetchColumn();
echo "Total active products: $total\n";

// FETCH_KEY_PAIR — [column1 => column2] for two columns
$stmt = $pdo->query("SELECT id, name FROM products");
$lookup = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => 'Laptop', 2 => 'Monitor', ...]
echo $lookup[1]; // Laptop

// FETCH_COLUMN and FETCH_GROUP — grouping
$stmt = $pdo->query("SELECT category, name FROM products ORDER BY category");
$byCategory = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_GROUP);
// ['electronics' => ['Laptop', 'Monitor'], 'accessories' => ['Mouse', 'Keyboard']]

Transactions #

Transactions ensure a group of database operations either all succeed or all fail — no half-done state:

<?php
function transferBalance(PDO $pdo, int $fromId, int $toId, float $amount): void
{
    if ($amount <= 0) {
        throw new \InvalidArgumentException("Transfer amount must be positive");
    }

    $pdo->beginTransaction();

    try {
        // Check and reduce the sender's balance with SELECT FOR UPDATE (row lock)
        $stmt = $pdo->prepare(
            "SELECT balance FROM accounts WHERE id = :id FOR UPDATE"
        );
        $stmt->execute([':id' => $fromId]);
        $sender = $stmt->fetch();

        if (!$sender || $sender['balance'] < $amount) {
            throw new \DomainException("Insufficient balance");
        }

        // Reduce the sender's balance
        $pdo->prepare("UPDATE accounts SET balance = balance - :amount WHERE id = :id")
            ->execute([':amount' => $amount, ':id' => $fromId]);

        // Increase the recipient's balance
        $pdo->prepare("UPDATE accounts SET balance = balance + :amount WHERE id = :id")
            ->execute([':amount' => $amount, ':id' => $toId]);

        // Record the transfer history
        $pdo->prepare(
            "INSERT INTO transfer_history (from_id, to_id, amount, created_at)
             VALUES (:from, :to, :amount, NOW())"
        )->execute([':from' => $fromId, ':to' => $toId, ':amount' => $amount]);

        $pdo->commit();
        echo "Transfer of Rp " . number_format($amount) . " succeeded\n";

    } catch (\Throwable $e) {
        $pdo->rollBack(); // undo all changes
        throw $e;         // re-throw so the caller knows
    }
}

// Helper wrapper for transactions
function inTransaction(PDO $pdo, callable $operation): mixed
{
    $pdo->beginTransaction();
    try {
        $result = $operation($pdo);
        $pdo->commit();
        return $result;
    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// More concise usage
$orderId = inTransaction($pdo, function(PDO $db) use ($data): int {
    $db->prepare("INSERT INTO orders ...")->execute($data['order']);
    $id = (int) $db->lastInsertId();

    foreach ($data['items'] as $item) {
        $db->prepare("INSERT INTO order_items ...")->execute([...$item, ':order_id' => $id]);
        $db->prepare("UPDATE products SET stock = stock - :qty WHERE id = :id")
           ->execute([':qty' => $item['qty'], ':id' => $item['product_id']]);
    }

    return $id;
});

The Repository Pattern #

Repositories separate database access logic from business logic — making code easier to test and change:

<?php
interface UserRepositoryInterface
{
    public function findById(int $id): ?array;
    public function findByEmail(string $email): ?array;
    public function findAll(int $limit = 20, int $offset = 0): array;
    public function save(array $data): int;
    public function update(int $id, array $data): bool;
    public function delete(int $id): bool;
    public function count(array $filter = []): int;
}

class MySqlUserRepository implements UserRepositoryInterface
{
    public function __construct(private PDO $pdo) {}

    public function findById(int $id): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, email, role, created_at
             FROM users WHERE id = :id AND deleted_at IS NULL"
        );
        $stmt->execute([':id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function findByEmail(string $email): ?array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, email, password_hash, role
             FROM users WHERE email = :email AND deleted_at IS NULL"
        );
        $stmt->execute([':email' => $email]);
        return $stmt->fetch() ?: null;
    }

    public function findAll(int $limit = 20, int $offset = 0): array
    {
        $stmt = $this->pdo->prepare(
            "SELECT id, name, email, role, created_at
             FROM users WHERE deleted_at IS NULL
             ORDER BY created_at DESC
             LIMIT :limit OFFSET :offset"
        );
        $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
        $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function save(array $data): int
    {
        $stmt = $this->pdo->prepare(
            "INSERT INTO users (name, email, password_hash, role, created_at)
             VALUES (:name, :email, :password_hash, :role, NOW())"
        );
        $stmt->execute([
            ':name'          => $data['name'],
            ':email'         => $data['email'],
            ':password_hash' => password_hash($data['password'], PASSWORD_BCRYPT),
            ':role'          => $data['role'] ?? 'user',
        ]);
        return (int) $this->pdo->lastInsertId();
    }

    public function update(int $id, array $data): bool
    {
        // Build a dynamic SET clause from the provided data
        $allowed = ['name', 'email', 'role'];
        $sets    = [];
        $params  = [':id' => $id];

        foreach ($allowed as $field) {
            if (array_key_exists($field, $data)) {
                $sets[]            = "$field = :$field";
                $params[":$field"] = $data[$field];
            }
        }

        if (empty($sets)) return false;

        $stmt = $this->pdo->prepare(
            "UPDATE users SET " . implode(', ', $sets) . ", updated_at = NOW()
             WHERE id = :id AND deleted_at IS NULL"
        );
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }

    public function delete(int $id): bool
    {
        // Soft delete — set deleted_at, not a physical delete
        $stmt = $this->pdo->prepare(
            "UPDATE users SET deleted_at = NOW() WHERE id = :id AND deleted_at IS NULL"
        );
        $stmt->execute([':id' => $id]);
        return $stmt->rowCount() > 0;
    }

    public function count(array $filter = []): int
    {
        $where  = ['deleted_at IS NULL'];
        $params = [];

        if (!empty($filter['role'])) {
            $where[]         = 'role = :role';
            $params[':role'] = $filter['role'];
        }

        $sql  = "SELECT COUNT(*) FROM users WHERE " . implode(' AND ', $where);
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return (int) $stmt->fetchColumn();
    }
}

Safe Dynamic Queries #

Sometimes you need to build queries dynamically (filters, sorting). The key: values always go through prepared statements, column/table names are validated via a whitelist:

<?php
function searchProducts(PDO $pdo, array $filter): array
{
    $where  = ['deleted_at IS NULL', 'active = 1'];
    $params = [];

    // Filter values — always via parameters
    if (!empty($filter['category'])) {
        $where[]              = 'category = :category';
        $params[':category']  = $filter['category'];
    }

    if (!empty($filter['min_price'])) {
        $where[]               = 'price >= :min_price';
        $params[':min_price']  = (float) $filter['min_price'];
    }

    if (!empty($filter['max_price'])) {
        $where[]               = 'price <= :max_price';
        $params[':max_price']  = (float) $filter['max_price'];
    }

    if (!empty($filter['search'])) {
        $where[]           = 'name LIKE :search';
        $params[':search'] = '%' . $filter['search'] . '%';
    }

    // Sorting — WHITELIST column names, don't use user input directly!
    $validColumns = ['name', 'price', 'stock', 'created_at'];
    $column       = in_array($filter['sort'] ?? '', $validColumns, strict: true)
        ? $filter['sort']
        : 'created_at';

    $direction = strtoupper($filter['order'] ?? '') === 'ASC' ? 'ASC' : 'DESC';

    // Pagination
    $limit  = max(1, min(100, (int) ($filter['limit']  ?? 20)));
    $offset = max(0, (int) ($filter['offset'] ?? 0));

    $sql  = "SELECT id, name, price, stock, category
             FROM products
             WHERE " . implode(' AND ', $where) . "
             ORDER BY $column $direction
             LIMIT :limit OFFSET :offset";

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':limit',  $limit,  PDO::PARAM_INT);
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);

    foreach ($params as $key => $val) {
        $stmt->bindValue($key, $val);
    }

    $stmt->execute();
    return $stmt->fetchAll();
}

Query Optimization with EXPLAIN #

<?php
// Use EXPLAIN to understand how MySQL executes a query
$stmt = $pdo->query("
    EXPLAIN SELECT u.id, u.name, COUNT(o.id) as total_orders
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.active = 1
    GROUP BY u.id
    ORDER BY total_orders DESC
    LIMIT 10
");

$plan = $stmt->fetchAll();
foreach ($plan as $row) {
    echo "Table: {$row['table']}, Type: {$row['type']}, ";
    echo "Key: {$row['key']}, Rows: {$row['rows']}\n";
}

// Signs a query needs optimization:
// type = ALL → full table scan, needs an index
// rows = large number → many rows being examined
// key = NULL → no index used
// Extra = Using filesort → sorting without an index

// Create the right indexes
$pdo->exec("CREATE INDEX idx_users_active ON users(active)");
$pdo->exec("CREATE INDEX idx_orders_user_id ON orders(user_id)");
$pdo->exec("CREATE INDEX idx_products_category_price ON products(category, price)");

Common MySQL Anti-Patterns #

<?php
// ✗ Anti-pattern 1: queries in a loop (the N+1 problem)
$users = $pdo->query("SELECT id FROM users")->fetchAll();
foreach ($users as $user) {
    // A new query for every user — very slow for thousands of users!
    $orders = $pdo->prepare("SELECT * FROM orders WHERE user_id = ?")->execute([$user['id']]);
}

// ✓ One query with a JOIN
$users = $pdo->query("
    SELECT u.id, u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.name
")->fetchAll();

// ✗ Anti-pattern 2: SELECT * — fetching all columns
$pdo->query("SELECT * FROM users"); // including password_hash, tokens, etc.!

// ✓ Explicitly name the needed columns
$pdo->query("SELECT id, name, email, role FROM users");

// ✗ Anti-pattern 3: no index on frequently filtered columns
// Make sure WHERE, JOIN ON, and ORDER BY columns have indexes

// ✗ Anti-pattern 4: connections not closed / not managed
// PDO closes the connection when the object is garbage collected, but:
// - Don't store PDO in PHP sessions
// - Don't create a new connection for every small request

// ✗ Anti-pattern 5: storing plaintext passwords
$pdo->prepare("INSERT INTO users (password) VALUES (?)")->execute([$_POST['password']]);

// ✓ Always hash passwords
$pdo->prepare("INSERT INTO users (password_hash) VALUES (?)")
    ->execute([password_hash($_POST['password'], PASSWORD_BCRYPT)]);

// Verify
$user = $pdo->prepare("SELECT password_hash FROM users WHERE email = ?")->execute([$email]);
if (!password_verify($_POST['password'], $user['password_hash'])) {
    throw new \RuntimeException("Wrong password");
}

Summary #

Always use PDO with ATTR_ERRMODE => ERRMODE_EXCEPTION and ATTR_EMULATE_PREPARES => false — this ensures errors are thrown as exceptions and prepared statements are executed natively by MySQL.
Prepared statements are non-negotiable — never interpolate user input directly into an SQL string. One $name = "' OR '1'='1" can expose your entire database.
Named parameters (:name) are safer and easier to read than positional (?) — no argument order mistakes.
PDO::FETCH_ASSOC as the default prevents data duplication (FETCH_BOTH returns every column twice — with string and numeric keys).
Transactions with try/catch/rollBack — make sure all operations in one unit of work either all succeed or are all cancelled. Use SELECT FOR UPDATE for row locks during read-modify-write.
The repository pattern separates SQL from business logic — easy to swap implementations (MySQL to PostgreSQL) and easy to test with a fake repository.
Whitelist column names for dynamic ORDER BY — never use user input directly as a column name, table name, or sort direction.
Run EXPLAIN before deploying complex queries — type=ALL and key=NULL are signs a query needs index optimization.

← Previous: YAML Next: MSSQL →