Both are database extensions for connecting PHP to databases, but they differ in scope and features.

PDO is generally preferred for new projects due to database portability and cleaner syntax.

Create a new PDO instance with a DSN (Data Source Name), username, password, and options. Always wrap it in try-catch since PDO throws exceptions on connection failure.

try {
    $pdo = new PDO(
        'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
        'username',
        'password',
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
} catch (PDOException $e) {
    die('Connection failed: ' . $e->getMessage());
}

Setting ERRMODE_EXCEPTION ensures database errors throw catchable exceptions instead of silent failures.

Prepared statements separate SQL logic from data, so user input is never treated as executable SQL. This is the primary defense against SQL injection.

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $userInput]);
$user = $stmt->fetch();

Without prepared statements, concatenating user input directly into SQL strings allows attackers to inject malicious SQL.

Transactions ensure a group of database operations either all succeed or all fail together, maintaining data consistency.

try {
    $pdo->beginTransaction();
    $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
        ->execute([100, $fromId]);
    $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
        ->execute([100, $toId]);
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$id = 5;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 10;
$stmt->execute();
// Uses 10, because bindParam reads at execute time

PDO supports several fetch styles controlled by PDO::FETCH_* constants.

$stmt = $pdo->query('SELECT * FROM users');
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
    echo $user['name'];
}

MySQLi uses positional placeholders (?) with a type string for binding, while PDO supports both positional and named placeholders.

$stmt = $mysqli->prepare('SELECT * FROM users WHERE email = ? AND status = ?');
$stmt->bind_param('si', $email, $status);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
    echo $row['name'];
}

SQL injection happens when untrusted input is concatenated directly into a SQL query, letting attackers alter query logic.

Set PDO to throw exceptions, catch them, log details for debugging, and show a generic message to users without exposing sensitive info.

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
} catch (PDOException $e) {
    error_log('DB connection failed: ' . $e->getMessage());
    http_response_code(500);
    die('A database error occurred. Please try again later.');
}

Never echo raw exception messages to end users in production - they can reveal database structure, credentials, or file paths to attackers.

Connection pooling reuses a set of open database connections across requests instead of opening/closing one per request, reducing overhead.

$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_PERSISTENT => true
]);

Persistent connections can cause issues with transaction state leaking between requests, so they should be used carefully and tested thoroughly.