PDO Database dalam PHP

PDO (PHP Data Objects) adalah extension PHP yang menyediakan interface yang konsisten untuk mengakses berbagai jenis database dengan cara yang aman dan berorientasi objek.

Apa itu PDO?

PDO (PHP Data Objects) adalah ekstensi database yang menyediakan abstraksi layer untuk mengakses database. PDO tidak menyediakan abstraksi database, melainkan abstraksi akses database.

Keuntungan PDO

  • 🔒 **Security**: Prepared statements mencegah SQL injection
  • 🔄 **Portabilitas**: Dapat digunakan dengan berbagai database
  • ⚡ **Performance**: Prepared statements dapat di-reuse
  • 🎯 **OOP**: Interface berorientasi objek
  • 🛡️ **Error Handling**: Exception handling yang baik
  • 📊 **Fetch Modes**: Berbagai cara mengambil data
  • 🎛️ **Flexibility**: Mendukung berbagai database driver
  • 🔧 **Features**: Named parameters, buffered queries

PDO vs MySQLi

Fitur PDO MySQLi
Database Support ✅ 12+ database ❌ Hanya MySQL
API ✅ OOP saja 🔄 OOP dan Procedural
Prepared Statements ✅ Ya ✅ Ya
Named Parameters ✅ Ya ❌ Tidak

Koneksi Database dengan PDO

Basic Connection

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'root';
$password = '';

try {
    $pdo = new PDO($dsn, $username, $password);
    echo "Koneksi berhasil!";
} catch (PDOException $e) {
    die("Koneksi gagal: " . $e->getMessage());
}
?>

Connection dengan Options

<?php
$dsn = 'mysql:host=localhost;dbname=testdb;charset=utf8mb4';
$username = 'root';
$password = '';

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4"
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    throw new PDOException($e->getMessage(), (int)$e->getCode());
}
?>

Database Configuration Class

<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'testdb';
    private $username = 'root';
    private $password = '';
    private $charset = 'utf8mb4';
    private $pdo;

    public function connect() {
        if ($this->pdo === null) {
            $dsn = "mysql:host={$this->host};dbname={$this->db_name};charset={$this->charset}";
            
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ];

            try {
                $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
            } catch (PDOException $e) {
                throw new PDOException($e->getMessage(), (int)$e->getCode());
            }
        }

        return $this->pdo;
    }

    public function disconnect() {
        $this->pdo = null;
    }
}

// Penggunaan
$database = new Database();
$pdo = $database->connect();
?>

Environment Configuration

<?php
// config.php
class Config {
    const DB_HOST = 'localhost';
    const DB_NAME = 'testdb';
    const DB_USER = 'root';
    const DB_PASS = '';
    const DB_CHARSET = 'utf8mb4';
}

// connection.php
require_once 'config.php';

function getConnection() {
    static $pdo = null;
    
    if ($pdo === null) {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            Config::DB_HOST,
            Config::DB_NAME,
            Config::DB_CHARSET
        );
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        try {
            $pdo = new PDO($dsn, Config::DB_USER, Config::DB_PASS, $options);
        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            throw new Exception("Database connection failed");
        }
    }
    
    return $pdo;
}
?>

Prepared Statements

Mengapa Prepared Statements?

❌ SQL Injection Vulnerable:

// JANGAN LAKUKAN INI!
$query = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";
$result = $pdo->query($query);

✅ Aman dengan Prepared Statements:

$query = "SELECT * FROM users WHERE email = ?";
$stmt = $pdo->prepare($query);
$stmt->execute([$_POST['email']]);

Positional Parameters

<?php
// Menggunakan tanda tanya (?)
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ? AND city = ?");
$stmt->execute([25, 'Jakarta']);

// Atau bind satu per satu
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $email);
$stmt->bindParam(3, $age, PDO::PARAM_INT);
$stmt->execute();
?>

Named Parameters

<?php
// Menggunakan named parameters
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > :age AND city = :city");
$stmt->execute([
    'age' => 25,
    'city' => 'Jakarta'
]);

// Atau bind satu per satu
$stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (:name, :email, :age)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':email', $email);
$stmt->bindParam(':age', $age, PDO::PARAM_INT);
$stmt->execute();
?>

bindParam vs bindValue

<?php
$stmt = $pdo->prepare("INSERT INTO users (name, status) VALUES (:name, :status)");

// bindParam - bind by reference
$name = "John";
$status = "active";
$stmt->bindParam(':name', $name);
$stmt->bindParam(':status', $status);

$name = "Jane"; // Ini akan mengubah nilai yang di-bind
$stmt->execute(); // Insert "Jane", bukan "John"

// bindValue - bind by value
$stmt->bindValue(':name', "John");
$stmt->bindValue(':status', "active");
$stmt->execute(); // Insert "John"
?>

Data Types

<?php
$stmt = $pdo->prepare("INSERT INTO products (name, price, is_active, created_at) VALUES (?, ?, ?, ?)");

$stmt->bindParam(1, $name, PDO::PARAM_STR);
$stmt->bindParam(2, $price, PDO::PARAM_STR); // Untuk decimal
$stmt->bindParam(3, $isActive, PDO::PARAM_BOOL);
$stmt->bindParam(4, $createdAt, PDO::PARAM_STR);

$name = "Laptop";
$price = "15000000.00";
$isActive = true;
$createdAt = date('Y-m-d H:i:s');

$stmt->execute();
?>

CRUD Operations dengan PDO

CREATE (INSERT)

<?php
// Single insert
function createUser($pdo, $name, $email, $age) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email, age, created_at) VALUES (?, ?, ?, NOW())");
    $stmt->execute([$name, $email, $age]);
    
    return $pdo->lastInsertId();
}

// Batch insert
function createMultipleUsers($pdo, $users) {
    $stmt = $pdo->prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");
    
    $pdo->beginTransaction();
    try {
        foreach ($users as $user) {
            $stmt->execute([$user['name'], $user['email'], $user['age']]);
        }
        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}

// Penggunaan
$userId = createUser($pdo, "John Doe", "john@example.com", 30);
echo "User created with ID: " . $userId;
?>

READ (SELECT)

<?php
// Fetch single row
function getUserById($pdo, $id) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
    $stmt->execute([$id]);
    return $stmt->fetch(); // Returns array or false
}

// Fetch multiple rows
function getUsersByAge($pdo, $minAge) {
    $stmt = $pdo->prepare("SELECT * FROM users WHERE age >= ? ORDER BY name");
    $stmt->execute([$minAge]);
    return $stmt->fetchAll();
}

// Fetch with different modes
function getAllUsers($pdo) {
    $stmt = $pdo->query("SELECT * FROM users");
    
    // Fetch as associative array (default)
    $users = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    // Fetch as objects
    $users = $stmt->fetchAll(PDO::FETCH_OBJ);
    
    // Fetch as custom class
    $users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
    
    return $users;
}

// Count records
function getUserCount($pdo) {
    $stmt = $pdo->query("SELECT COUNT(*) FROM users");
    return $stmt->fetchColumn();
}
?>

UPDATE

<?php
// Update single record
function updateUser($pdo, $id, $name, $email) {
    $stmt = $pdo->prepare("UPDATE users SET name = ?, email = ?, updated_at = NOW() WHERE id = ?");
    $stmt->execute([$name, $email, $id]);
    
    return $stmt->rowCount(); // Returns affected rows
}

// Update with conditions
function updateUserStatus($pdo, $status, $lastLogin) {
    $stmt = $pdo->prepare("
        UPDATE users 
        SET status = ?, updated_at = NOW() 
        WHERE last_login < ? OR last_login IS NULL
    ");
    $stmt->execute([$status, $lastLogin]);
    
    return $stmt->rowCount();
}

// Conditional update
function updateUserIfExists($pdo, $id, $data) {
    // Check if user exists
    $stmt = $pdo->prepare("SELECT id FROM users WHERE id = ?");
    $stmt->execute([$id]);
    
    if (!$stmt->fetch()) {
        return false; // User not found
    }
    
    // Build dynamic update query
    $fields = [];
    $values = [];
    
    foreach ($data as $field => $value) {
        $fields[] = "$field = ?";
        $values[] = $value;
    }
    
    $values[] = $id; // For WHERE clause
    
    $sql = "UPDATE users SET " . implode(', ', $fields) . " WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($values);
    
    return $stmt->rowCount() > 0;
}
?>

DELETE

<?php
// Delete single record
function deleteUser($pdo, $id) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$id]);
    
    return $stmt->rowCount() > 0;
}

// Soft delete
function softDeleteUser($pdo, $id) {
    $stmt = $pdo->prepare("UPDATE users SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL");
    $stmt->execute([$id]);
    
    return $stmt->rowCount() > 0;
}

// Delete with conditions
function deleteInactiveUsers($pdo, $daysInactive) {
    $stmt = $pdo->prepare("
        DELETE FROM users 
        WHERE last_login < DATE_SUB(NOW(), INTERVAL ? DAY) 
        AND status = 'inactive'
    ");
    $stmt->execute([$daysInactive]);
    
    return $stmt->rowCount();
}

// Safe delete with confirmation
function safeDeleteUser($pdo, $id, $confirmationCode) {
    $pdo->beginTransaction();
    
    try {
        // Verify user and confirmation
        $stmt = $pdo->prepare("SELECT id FROM users WHERE id = ? AND confirmation_code = ?");
        $stmt->execute([$id, $confirmationCode]);
        
        if (!$stmt->fetch()) {
            throw new Exception("Invalid user or confirmation code");
        }
        
        // Archive user data before deletion
        $stmt = $pdo->prepare("
            INSERT INTO users_archive (user_id, name, email, deleted_at) 
            SELECT id, name, email, NOW() FROM users WHERE id = ?
        ");
        $stmt->execute([$id]);
        
        // Delete user
        $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
        $stmt->execute([$id]);
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Advanced PDO Features

Transactions

<?php
function transferMoney($pdo, $fromAccount, $toAccount, $amount) {
    $pdo->beginTransaction();
    
    try {
        // Deduct from source account
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?");
        $stmt->execute([$amount, $fromAccount, $amount]);
        
        if ($stmt->rowCount() === 0) {
            throw new Exception("Insufficient funds or invalid account");
        }
        
        // Add to destination account
        $stmt = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
        $stmt->execute([$amount, $toAccount]);
        
        if ($stmt->rowCount() === 0) {
            throw new Exception("Invalid destination account");
        }
        
        // Log transaction
        $stmt = $pdo->prepare("
            INSERT INTO transactions (from_account, to_account, amount, created_at) 
            VALUES (?, ?, ?, NOW())
        ");
        $stmt->execute([$fromAccount, $toAccount, $amount]);
        
        $pdo->commit();
        return true;
        
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
?>

Custom Fetch Classes

<?php
class User {
    public $id;
    public $name;
    public $email;
    public $created_at;
    
    public function getDisplayName() {
        return ucwords($this->name);
    }
    
    public function isRecentUser() {
        return strtotime($this->created_at) > strtotime('-30 days');
    }
}

// Fetch as custom class
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');

foreach ($users as $user) {
    echo $user->getDisplayName() . " (" . ($user->isRecentUser() ? "New" : "Existing") . ")\n";
}
?>

Database Abstraction Layer

<?php
class QueryBuilder {
    private $pdo;
    private $table;
    private $conditions = [];
    private $parameters = [];
    private $orderBy = [];
    private $limit;
    private $offset;
    
    public function __construct(PDO $pdo) {
        $this->pdo = $pdo;
    }
    
    public function table($table) {
        $this->table = $table;
        return $this;
    }
    
    public function where($column, $operator, $value) {
        $this->conditions[] = "$column $operator ?";
        $this->parameters[] = $value;
        return $this;
    }
    
    public function orderBy($column, $direction = 'ASC') {
        $this->orderBy[] = "$column $direction";
        return $this;
    }
    
    public function limit($limit, $offset = 0) {
        $this->limit = $limit;
        $this->offset = $offset;
        return $this;
    }
    
    public function get() {
        $sql = "SELECT * FROM {$this->table}";
        
        if (!empty($this->conditions)) {
            $sql .= " WHERE " . implode(' AND ', $this->conditions);
        }
        
        if (!empty($this->orderBy)) {
            $sql .= " ORDER BY " . implode(', ', $this->orderBy);
        }
        
        if ($this->limit) {
            $sql .= " LIMIT {$this->limit}";
            if ($this->offset) {
                $sql .= " OFFSET {$this->offset}";
            }
        }
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($this->parameters);
        return $stmt->fetchAll();
    }
    
    public function first() {
        $this->limit(1);
        $results = $this->get();
        return $results ? $results[0] : null;
    }
}

// Penggunaan
$query = new QueryBuilder($pdo);
$users = $query->table('users')
              ->where('age', '>', 18)
              ->where('status', '=', 'active')
              ->orderBy('name')
              ->limit(10)
              ->get();
?>

Connection Pool

<?php
class ConnectionPool {
    private static $instances = [];
    private static $maxConnections = 10;
    
    public static function getConnection($database = 'default') {
        if (!isset(self::$instances[$database])) {
            self::$instances[$database] = [];
        }
        
        // Reuse existing connection if available
        if (!empty(self::$instances[$database])) {
            return array_pop(self::$instances[$database]);
        }
        
        // Create new connection if under limit
        if (count(self::$instances[$database]) < self::$maxConnections) {
            return self::createConnection($database);
        }
        
        throw new Exception("Maximum connections reached");
    }
    
    public static function releaseConnection($pdo, $database = 'default') {
        if (count(self::$instances[$database]) < self::$maxConnections) {
            self::$instances[$database][] = $pdo;
        }
    }
    
    private static function createConnection($database) {
        $config = self::getConfig($database);
        
        $dsn = "mysql:host={$config['host']};dbname={$config['dbname']};charset=utf8mb4";
        
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ];
        
        return new PDO($dsn, $config['username'], $config['password'], $options);
    }
    
    private static function getConfig($database) {
        $configs = [
            'default' => [
                'host' => 'localhost',
                'dbname' => 'myapp',
                'username' => 'root',
                'password' => ''
            ],
            'analytics' => [
                'host' => 'analytics.example.com',
                'dbname' => 'analytics',
                'username' => 'analyst',
                'password' => 'secret'
            ]
        ];
        
        return $configs[$database] ?? $configs['default'];
    }
}
?>

💡 Tips dan Best Practices

  • 🔒 Security First: Selalu gunakan prepared statements untuk mencegah SQL injection
  • ⚡ Connection Reuse: Gunakan singleton pattern atau connection pooling untuk efisiensi
  • 🛡️ Error Handling: Set error mode ke EXCEPTION untuk handling yang lebih baik
  • 📊 Fetch Mode: Set default fetch mode untuk konsistensi
  • 🔄 Transactions: Gunakan transactions untuk operasi yang saling bergantung
Sebelumnya: AJAX dengan PHP
Selanjutnya: RESTful API
Path Belajar PHP
Step 1: Dasar-Dasar PHP

Memahami apa itu PHP dan cara instalasinya

📄 Pengenalan📄 Instalasi
Step 2: Sintaks & Variabel

Mempelajari cara menulis kode PHP yang benar

📄 Sintaks Dasar📄 Variabel📄 Operator
Step 3: Logika & Function

Memahami alur program dan pengorganisasian kode

📄 Struktur Kontrol📄 Function📄 Array
Step 4: Web Development

Membuat aplikasi web interaktif

📄 Form📄 Session📄 File
Step 5: Database & Keamanan

Mengelola data dan keamanan aplikasi

✅ Database📍 Pdo✅ Security
Step 6: Advanced Topics

Topik lanjutan untuk pengembangan professional

📄 Oop Basic📄 Email📄 Framework
Tips: Ikuti urutan step untuk hasil belajar optimal. Setiap step membangun pengetahuan dari step sebelumnya.
Tutorial Saat Ini
Level: Menengah

Memerlukan pemahaman dasar PHP

Daftar Isi
Tutorial Terkait
Navigasi
Tutorial Sebelumnya
Tutorial Selanjutnya
Semua Tutorial
Tips Belajar