PHP Database Integration: PDO, MySQLi, and Secure Data Patterns

Database - MySQL/MariaDB

MySQLi Extension (Procedural)

MySQLi procedural style uses functions like mysqli_connect(), mysqli_query(), and mysqli_fetch_assoc() with a connection resource as the first parameter.

`php // Connect $conn = mysqli_connect('localhost', 'user', 'password', 'database'); if (!$conn) { die('Connection failed: ' . mysqli_connect_error()); }

// Query $sql = "SELECT id, name FROM users WHERE active = 1"; $result = mysqli_query($conn, $sql);

// Fetch results while ($row = mysqli_fetch_assoc($result)) { echo $row['id'] . ': ' . $row['name'] . "\n"; }

// Prepared statement $stmt = mysqli_prepare($conn, "SELECT * FROM users WHERE id = ?"); mysqli_stmt_bind_param($stmt, 'i', $userId); mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); // Cleanup mysqli_free_result($result); mysqli_close($conn);

---

MySQLi Extension (Object-Oriented)

MySQLi OOP style creates a mysqli object instance and calls methods on it, providing a cleaner interface and better IDE support than procedural style.

php // Connect $mysqli = new mysqli('localhost', 'user', 'password', 'database'); if ($mysqli->connect_error) { die('Connection failed: ' . $mysqli->connect_error); }

// Set charset $mysqli->set_charset('utf8mb4');

// Simple query $result = $mysqli->query("SELECT id, name FROM users"); while ($row = $result->fetch_assoc()) { echo "{$row['id']}: {$row['name']}\n"; }

// Prepared statement $stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)"); $stmt->bind_param('ss', $name, $email); $name = 'John'; $email = 'john@example.com'; $stmt->execute();

echo "Inserted ID: " . $mysqli->insert_id;

$mysqli->close();

---

PDO (PHP Data Objects)

PDO provides a database-agnostic abstraction layer supporting multiple databases through drivers; it offers prepared statements, transactions, and exception-based error handling.

php // Connect with error mode $pdo = new PDO( 'mysql:host=localhost;dbname=myapp;charset=utf8mb4', 'user', 'password', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ] );

// Query with prepared statement $stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email'); $stmt->execute(['email' => $email]); $user = $stmt->fetch();

// Insert $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->execute([$name, $email]); $id = $pdo->lastInsertId();

// Multiple databases supported by changing DSN // 'pgsql:host=localhost;dbname=myapp' // 'sqlite:/path/to/database.db'

---

Database Connections

Establish database connections with proper error handling, character encoding, and connection options; use persistent connections for high-traffic applications.

php // PDO connection with full options try { $dsn = 'mysql:host=localhost;dbname=myapp;charset=utf8mb4'; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_PERSISTENT => true, // Connection pooling ]; $pdo = new PDO($dsn, $user, $password, $options); } catch (PDOException $e) { error_log('Database connection failed: ' . $e->getMessage()); die('Database unavailable'); }

// Connection singleton pattern class Database { private static ?PDO $instance = null;

public static function getInstance(): PDO {
    if (self::$instance === null) {
        self::$instance = new PDO($dsn, $user, $pass);
    }
    return self::$instance;
}

}

---

Prepared Statements

Prepared statements separate SQL structure from data, preventing SQL injection by sending parameters separately; they also improve performance for repeated queries.

php // Named parameters $stmt = $pdo->prepare(' SELECT * FROM users WHERE email = :email AND status = :status '); $stmt->execute([ 'email' => $email, 'status' => 'active' ]);

// Positional parameters $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ? AND role = ?'); $stmt->execute([1, 'admin']);

// Reuse prepared statement $stmt = $pdo->prepare('INSERT INTO logs (message) VALUES (?)'); foreach ($messages as $msg) { $stmt->execute([$msg]); // Executed multiple times efficiently }

// Bind with explicit types $stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id'); $stmt->bindValue(':id', $id, PDO::PARAM_INT); $stmt->execute();

WITHOUT PREPARED: "SELECT * FROM users WHERE id = $id" ↓ SQL + DATA mixed → SQL INJECTION RISK!

WITH PREPARED: "SELECT * FROM users WHERE id = ?" ↓ SQL structure sent first ↓ Data sent separately → SAFE! `

---

Parameterized Queries

Parameterized queries are essentially prepared statements where user input is passed as parameters rather than concatenated into SQL strings, eliminating injection vulnerabilities.

// DANGEROUS - Never do this! $sql = "SELECT * FROM users WHERE name = '$name'"; // SQL injection!

// SAFE - Parameterized query $stmt = $pdo->prepare("SELECT * FROM users WHERE name = ?"); $stmt->execute([$name]);

// Multiple parameters $stmt = $pdo->prepare(" INSERT INTO orders (user_id, product_id, quantity, price) VALUES (:user, :product, :qty, :price) "); $stmt->execute([ 'user' => $userId, 'product' => $productId, 'qty' => $quantity, 'price' => $price ]);

// IN clause (requires special handling) $ids = [1, 2, 3, 4, 5]; $placeholders = implode(',', array_fill(0, count($ids), '?')); $stmt = $pdo->prepare("SELECT * FROM users WHERE id IN ($placeholders)"); $stmt->execute($ids);

CRUD Operations

CRUD (Create, Read, Update, Delete) operations form the basis of database interactions; use prepared statements for all operations involving user data.

// CREATE $stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)'); $stmt->execute(['John', 'john@example.com']); $newId = $pdo->lastInsertId();

// READ $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); $stmt->execute([$id]); $user = $stmt->fetch(); // Single row $users = $stmt->fetchAll(); // All rows

// UPDATE $stmt = $pdo->prepare('UPDATE users SET name = ?, email = ? WHERE id = ?'); $stmt->execute(['Jane', 'jane@example.com', $id]); $affected = $stmt->rowCount();

// DELETE $stmt = $pdo->prepare('DELETE FROM users WHERE id = ?'); $stmt->execute([$id]);

// Check if row exists $stmt = $pdo->prepare('SELECT 1 FROM users WHERE email = ?'); $stmt->execute([$email]); $exists = $stmt->fetchColumn() !== false;

Transactions

Transactions group multiple database operations into an atomic unit; either all succeed and are committed, or all fail and are rolled back, ensuring data consistency.

try { $pdo->beginTransaction();

// Debit one account
$stmt = $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
$stmt->execute([100, $fromAccount]);

if ($stmt->rowCount() === 0) {
    throw new Exception('Source account not found');
}

// Credit another account
$stmt = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
$stmt->execute([100, $toAccount]);

// Log the transfer
$stmt = $pdo->prepare('INSERT INTO transfers (from_id, to_id, amount) VALUES (?, ?, ?)');
$stmt->execute([$fromAccount, $toAccount, 100]);

$pdo->commit();
echo "Transfer successful";

} catch (Exception $e) { $pdo->rollBack(); echo "Transfer failed: " . $e->getMessage(); }

Error Handling in Database Operations

Configure PDO to throw exceptions for errors, catch them appropriately, log details for debugging, and present user-friendly messages without exposing sensitive information.

// Set error mode to exceptions $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

try { $stmt = $pdo->prepare('INSERT INTO users (email) VALUES (?)'); $stmt->execute([$email]);

} catch (PDOException $e) { // Log full error for debugging error_log('Database error: ' . $e->getMessage()); error_log('SQL State: ' . $e->getCode());

// Check for specific errors
if ($e->getCode() === '23000') {  // Integrity constraint
    echo "Email already exists";
} else {
    echo "An error occurred. Please try again.";
}

}

// MySQLi error handling $mysqli->query($sql); if ($mysqli->error) { error_log('MySQLi Error: ' . $mysqli->error); }

Connection Pooling

PHP typically uses persistent connections via PDO::ATTR_PERSISTENT to reuse database connections across requests, reducing connection overhead in high-traffic applications. // Enable persistent connections $pdo = new PDO($dsn, $user, $password, [ PDO::ATTR_PERSISTENT => true ]);

// MySQLi persistent connection (p: prefix) $mysqli = new mysqli('p:localhost', 'user', 'password', 'database');

┌─────────────────────────────────────────────────────────┐
│   Without Pooling          With Pooling                 │
├─────────────────────────────────────────────────────────┤
│   Request 1                Request 1                    │
│   └─ Connect               └─ Get from pool             │
│   └─ Query                 └─ Query                     │
│   └─ Disconnect            └─ Return to pool            │
│                                                         │
│   Request 2                Request 2                    │
│   └─ Connect (slow)        └─ Reuse connection (fast)   │
│   └─ Query                 └─ Query                     │
│   └─ Disconnect            └─ Return to pool            │
└─────────────────────────────────────────────────────────┘

---

Database Abstraction

Database abstraction layers like Doctrine DBAL or custom wrappers provide a consistent API across database engines, simplifying migrations and reducing vendor lock-in.

// Simple abstraction layer
class Database {
    private PDO $pdo;
    
    public function query(string $sql, array $params = []): array {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    public function insert(string $table, array $data): int {
        $columns = implode(', ', array_keys($data));
        $placeholders = ':' . implode(', :', array_keys($data));
        
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($data);
        
        return (int) $this->pdo->lastInsertId();
    }
    
    public function update(string $table, array $data, array $where): int {
        // Build SET clause
        // Build WHERE clause
        // Execute and return affected rows
    }
}

// Doctrine DBAL example
$conn = DriverManager::getConnection($config);
$users = $conn->fetchAllAssociative('SELECT * FROM users WHERE id = ?', [1]);

---