MySQL #

MySQL adalah database relasional paling populer di dunia PHP — hampir semua shared hosting mendukungnya, dan ia menjadi tulang punggung jutaan aplikasi web dari blog WordPress hingga toko e-commerce berskala besar. PHP menyediakan dua cara mengakses MySQL: PDO (PHP Data Objects) yang database-agnostic dan MySQLi yang spesifik MySQL. PDO adalah pilihan yang hampir selalu lebih baik — kode yang sama bisa berjalan di MySQL, PostgreSQL, SQLite, atau database lain hanya dengan mengubah DSN koneksi. Artikel ini membahas semua aspek penting: koneksi yang benar, prepared statement sebagai satu-satunya cara aman mengirim data ke database, semua fetch mode, transaksi, dan pola repository yang membuat kode PHP mudah di-test.

PDO vs MySQLi — Memilih dengan Tepat #

flowchart TD
    A{Hanya\npakai MySQL?} -- Ya --> B{Butuh\nfitur spesifik\nMySQL?}
    B -- Ya --> C[MySQLi\nContoh: async query,\nmulti-statement]
    B -- Tidak --> D[PDO\nLebih portabel dan\nkonsisten]
    A -- Tidak --> D

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

Aspek	PDO	MySQLi
Database yang didukung	13+ (MySQL, PostgreSQL, SQLite, dll.)	Hanya MySQL/MariaDB
API	OOP	OOP + Prosedural
Named parameter	`:nama`	Tidak (hanya `?`)
Prepared statement	✓	✓
Stored procedure	✓	✓
Multi-statement	✗	✓
Async query	✗	✓

Gunakan PDO sebagai default. Hanya pilih MySQLi jika butuh fitur yang benar-benar spesifik MySQL seperti multi-statement atau async query.

Koneksi dengan 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: lempar PDOException saat error (bukan return false)
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,

    // Fetch mode default: array asosiatif
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,

    // Matikan emulasi prepared statement — gunakan prepared statement native MySQL
    // Lebih aman dan lebih efisien
    PDO::ATTR_EMULATE_PREPARES   => false,

    // Persistent connection — reuse koneksi yang sudah ada (connection pooling sederhana)
    // PDO::ATTR_PERSISTENT => true, // aktifkan jika diperlukan

    // Timeout koneksi
    PDO::ATTR_TIMEOUT => 5,
];

try {
    $pdo = new PDO($dsn, username: 'root', password: 'rahasia', options: $options);
    echo "Koneksi berhasil\n";
} catch (PDOException $e) {
    // Jangan tampilkan detail error ke user di production
    error_log("Koneksi database gagal: " . $e->getMessage());
    throw new \RuntimeException("Layanan tidak tersedia. Coba lagi nanti.");
}

Singleton Connection #

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

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

    public static function koneksi(): 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::koneksi();

Prepared Statement — Satu-satunya Cara Aman #

SQL injection adalah salah satu kerentanan paling berbahaya dan paling mudah dicegah. Prepared statement memisahkan SQL dari data — database memperlakukan data sebagai data, bukan sebagai bagian dari query.

<?php
// ANTI-PATTERN: string interpolation langsung — SANGAT BERBAHAYA
$nama = $_GET['nama']; // misalnya: "' OR '1'='1"
$query = "SELECT * FROM users WHERE nama = '$nama'";
// Query menjadi: SELECT * FROM users WHERE nama = '' OR '1'='1'
// Ini mengambil SEMUA user! SQL injection berhasil.
$pdo->query($query); // JANGAN LAKUKAN INI

// BENAR: prepared statement dengan named parameter
$stmt = $pdo->prepare("SELECT * FROM users WHERE nama = :nama AND aktif = :aktif");
$stmt->execute([':nama' => $nama, ':aktif' => true]);
$users = $stmt->fetchAll();

// Atau dengan positional parameter (?)
$stmt = $pdo->prepare("SELECT * FROM users WHERE nama = ? AND aktif = ?");
$stmt->execute([$nama, true]);

// Named parameter lebih jelas dan bisa digunakan berulang dalam satu query
$stmt = $pdo->prepare("
    UPDATE users
    SET nama = :nama, email = :email, updated_at = NOW()
    WHERE id = :id
");
$stmt->execute([
    ':nama'  => 'Budi Santoso',
    ':email' => '[email protected]',
    ':id'    => 42,
]);
echo $stmt->rowCount(); // jumlah baris yang terpengaruh

Binding Parameter dengan Tipe Eksplisit #

<?php
// bindParam() — bind variabel by reference (dievaluasi saat execute)
// bindValue() — bind nilai langsung (lebih aman untuk loop)

$stmt = $pdo->prepare("
    INSERT INTO produk (nama, harga, stok, aktif)
    VALUES (:nama, :harga, :stok, :aktif)
");

// bindValue dengan tipe eksplisit
$stmt->bindValue(':nama',  'Laptop Pro',     PDO::PARAM_STR);
$stmt->bindValue(':harga', 15_000_000,       PDO::PARAM_INT);
$stmt->bindValue(':stok',  5,                PDO::PARAM_INT);
$stmt->bindValue(':aktif', true,             PDO::PARAM_BOOL);
$stmt->execute();

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

// bindParam — variabel by reference, berguna untuk loop insert
$stmt = $pdo->prepare("INSERT INTO log (pesan, level) VALUES (:pesan, :level)");
$stmt->bindParam(':pesan', $pesan, PDO::PARAM_STR);
$stmt->bindParam(':level', $level, PDO::PARAM_STR);

$logs = [
    ['pesan' => 'Server start', 'level' => 'info'],
    ['pesan' => 'Request masuk', 'level' => 'debug'],
];

foreach ($logs as $log) {
    $pesan = $log['pesan']; // bindParam menangkap variabel by reference
    $level = $log['level'];
    $stmt->execute(); // menggunakan nilai $pesan dan $level saat ini
}

Fetch Mode — Mengambil Data #

PDO menyediakan berbagai cara mengambil hasil query:

<?php
$stmt = $pdo->prepare("SELECT id, nama, email, harga FROM produk WHERE aktif = 1");
$stmt->execute();

// fetch() — satu baris pada satu waktu (hemat memori untuk banyak baris)
while ($row = $stmt->fetch()) {
    echo "{$row['id']}: {$row['nama']}\n";
}

// fetchAll() — semua baris sekaligus (mudah tapi boros memori untuk dataset besar)
$stmt->execute();
$semua = $stmt->fetchAll();

// FETCH_ASSOC (default) — array asosiatif
$baris = $stmt->fetch(PDO::FETCH_ASSOC);
// ['id' => 1, 'nama' => 'Laptop', 'email' => '...', 'harga' => 15000000]

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

// FETCH_BOTH — keduanya (default PDO lama — hindari, boros)
$baris = $stmt->fetch(PDO::FETCH_BOTH);

// FETCH_OBJ — stdClass object
$baris = $stmt->fetch(PDO::FETCH_OBJ);
echo $baris->nama;   // Laptop
echo $baris->harga;  // 15000000

// FETCH_CLASS — langsung ke kelas tertentu
class Produk
{
    public int    $id;
    public string $nama;
    public float  $harga;

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

$stmt->setFetchMode(PDO::FETCH_CLASS, Produk::class);
$produk = $stmt->fetch();
echo $produk->hargaFormat(); // Rp 15.000.000

// fetchColumn() — satu kolom dari satu baris
$stmt = $pdo->query("SELECT COUNT(*) FROM produk WHERE aktif = 1");
$total = (int) $stmt->fetchColumn();
echo "Total produk aktif: $total\n";

// FETCH_KEY_PAIR — [kolom1 => kolom2] untuk dua kolom
$stmt = $pdo->query("SELECT id, nama FROM produk");
$lookup = $stmt->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => 'Laptop', 2 => 'Monitor', ...]
echo $lookup[1]; // Laptop

// FETCH_COLUMN dan FETCH_GROUP — grouping
$stmt = $pdo->query("SELECT kategori, nama FROM produk ORDER BY kategori");
$perKategori = $stmt->fetchAll(PDO::FETCH_COLUMN | PDO::FETCH_GROUP);
// ['elektronik' => ['Laptop', 'Monitor'], 'aksesoris' => ['Mouse', 'Keyboard']]

Transaksi #

Transaksi memastikan sekelompok operasi database berhasil semua atau gagal semua — tidak ada kondisi setengah jadi:

<?php
function transferSaldo(PDO $pdo, int $dariId, int $keId, float $jumlah): void
{
    if ($jumlah <= 0) {
        throw new \InvalidArgumentException("Jumlah transfer harus positif");
    }

    $pdo->beginTransaction();

    try {
        // Cek dan kurangi saldo pengirim dengan SELECT FOR UPDATE (lock baris)
        $stmt = $pdo->prepare(
            "SELECT saldo FROM akun WHERE id = :id FOR UPDATE"
        );
        $stmt->execute([':id' => $dariId]);
        $pengirim = $stmt->fetch();

        if (!$pengirim || $pengirim['saldo'] < $jumlah) {
            throw new \DomainException("Saldo tidak mencukupi");
        }

        // Kurangi saldo pengirim
        $pdo->prepare("UPDATE akun SET saldo = saldo - :jumlah WHERE id = :id")
            ->execute([':jumlah' => $jumlah, ':id' => $dariId]);

        // Tambah saldo penerima
        $pdo->prepare("UPDATE akun SET saldo = saldo + :jumlah WHERE id = :id")
            ->execute([':jumlah' => $jumlah, ':id' => $keId]);

        // Catat riwayat transfer
        $pdo->prepare(
            "INSERT INTO riwayat_transfer (dari_id, ke_id, jumlah, created_at)
             VALUES (:dari, :ke, :jumlah, NOW())"
        )->execute([':dari' => $dariId, ':ke' => $keId, ':jumlah' => $jumlah]);

        $pdo->commit();
        echo "Transfer Rp " . number_format($jumlah) . " berhasil\n";

    } catch (\Throwable $e) {
        $pdo->rollBack(); // batalkan semua perubahan
        throw $e;         // re-throw agar pemanggil tahu
    }
}

// Helper wrapper untuk transaksi
function dalamTransaksi(PDO $pdo, callable $operasi): mixed
{
    $pdo->beginTransaction();
    try {
        $hasil = $operasi($pdo);
        $pdo->commit();
        return $hasil;
    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Penggunaan lebih ringkas
$orderId = dalamTransaksi($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 produk SET stok = stok - :qty WHERE id = :id")
           ->execute([':qty' => $item['qty'], ':id' => $item['produk_id']]);
    }

    return $id;
});

Pola Repository #

Repository memisahkan logika akses database dari logika bisnis — membuat kode lebih mudah di-test dan diubah:

<?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, nama, 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, nama, 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, nama, 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 (nama, email, password_hash, role, created_at)
             VALUES (:nama, :email, :password_hash, :role, NOW())"
        );
        $stmt->execute([
            ':nama'          => $data['nama'],
            ':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
    {
        // Bangun SET clause dinamis dari data yang diberikan
        $allowed = ['nama', '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, bukan hapus fisik
        $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();
    }
}

Query Dinamis yang Aman #

Terkadang perlu membangun query secara dinamis (filter, sorting). Kuncinya: nilai selalu via prepared statement, nama kolom/tabel divalidasi via whitelist:

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

    // Filter nilai — selalu via parameter
    if (!empty($filter['kategori'])) {
        $where[]             = 'kategori = :kategori';
        $params[':kategori'] = $filter['kategori'];
    }

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

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

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

    // Sorting — WHITELIST nama kolom, jangan pakai input user langsung!
    $kolomValid = ['nama', 'harga', 'stok', 'created_at'];
    $kolom      = in_array($filter['sort'] ?? '', $kolomValid, strict: true)
        ? $filter['sort']
        : 'created_at';

    $arah = 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, nama, harga, stok, kategori
             FROM produk
             WHERE " . implode(' AND ', $where) . "
             ORDER BY $kolom $arah
             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();
}

Optimasi Query dengan EXPLAIN #

<?php
// Gunakan EXPLAIN untuk memahami bagaimana MySQL mengeksekusi query
$stmt = $pdo->query("
    EXPLAIN SELECT u.id, u.nama, COUNT(o.id) as total_order
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.aktif = 1
    GROUP BY u.id
    ORDER BY total_order DESC
    LIMIT 10
");

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

// Tanda query perlu dioptimasi:
// type = ALL → full table scan, butuh index
// rows = angka besar → banyak baris yang diperiksa
// key = NULL → tidak ada index yang digunakan
// Extra = Using filesort → sorting tanpa index

// Buat index yang tepat
$pdo->exec("CREATE INDEX idx_users_aktif ON users(aktif)");
$pdo->exec("CREATE INDEX idx_orders_user_id ON orders(user_id)");
$pdo->exec("CREATE INDEX idx_produk_kategori_harga ON produk(kategori, harga)");

Anti-Pattern MySQL yang Sering Ditemui #

<?php
// ✗ Anti-pattern 1: query dalam loop (N+1 problem)
$users = $pdo->query("SELECT id FROM users")->fetchAll();
foreach ($users as $user) {
    // Query baru untuk setiap user — sangat lambat untuk ribuan user!
    $orders = $pdo->prepare("SELECT * FROM orders WHERE user_id = ?")->execute([$user['id']]);
}

// ✓ Satu query dengan JOIN
$users = $pdo->query("
    SELECT u.id, u.nama, COUNT(o.id) as jumlah_order
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.nama
")->fetchAll();

// ✗ Anti-pattern 2: SELECT * — ambil semua kolom
$pdo->query("SELECT * FROM users"); // termasuk password_hash, token, dll.!

// ✓ Sebutkan kolom yang diperlukan secara eksplisit
$pdo->query("SELECT id, nama, email, role FROM users");

// ✗ Anti-pattern 3: tidak pakai index untuk kolom yang sering di-filter
// Pastikan kolom WHERE, JOIN ON, dan ORDER BY punya index

// ✗ Anti-pattern 4: koneksi tidak ditutup / tidak dikelola
// PDO menutup koneksi saat objek di-garbage collect, tapi:
// - Jangan simpan PDO di session PHP
// - Jangan buat koneksi baru di setiap request kecil

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

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

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

Ringkasan #

Selalu gunakan PDO dengan ATTR_ERRMODE => ERRMODE_EXCEPTION dan ATTR_EMULATE_PREPARES => false — ini memastikan error dilempar sebagai exception dan prepared statement dieksekusi secara native oleh MySQL.
Prepared statement adalah non-negotiable — jangan pernah interpolasi input user langsung ke string SQL. Satu $nama = "' OR '1'='1" bisa mengekspos seluruh database.
Named parameter (:nama) lebih aman dan mudah dibaca dari positional (?) — tidak ada kesalahan urutan argumen.
PDO::FETCH_ASSOC sebagai default mencegah duplikasi data (FETCH_BOTH mengembalikan setiap kolom dua kali — dengan key string dan numerik).
Transaksi dengan try/catch/rollBack — pastikan semua operasi dalam satu unit kerja berhasil semua atau dibatalkan semua. Gunakan SELECT FOR UPDATE untuk lock baris saat read-modify-write.
Repository pattern memisahkan SQL dari logika bisnis — mudah diganti implementasinya (MySQL ke PostgreSQL) dan mudah di-test dengan fake repository.
Whitelist nama kolom untuk ORDER BY dinamis — jangan pernah pakai input user langsung sebagai nama kolom, tabel, atau arah sorting.
Jalankan EXPLAIN sebelum deploy query yang kompleks — type=ALL dan key=NULL adalah tanda query butuh optimasi dengan index.

← Sebelumnya: YAML Berikutnya: MSSQL →