Database Connection Patterns in PHP

Always use PDO

PDO is the standard PHP database abstraction layer. Use it over mysqli or legacy mysql_ functions. It supports parameterised queries natively, which is your primary injection defence.

 PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]
);

Parameterised queries

// Named parameters (preferred for clarity)
$stmt = $pdo->prepare("SELECT * FROM orders WHERE customer_id = :id AND status = :status");
$stmt->execute([":id" => $customerId, ":status" => "shipped"]);

Transactions

try {
    $pdo->beginTransaction();
    $pdo->prepare("INSERT INTO orders ...")->execute([...]);
    $pdo->prepare("UPDATE inventory ...")->execute([...]);
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

Persistent connections

Set PDO::ATTR_PERSISTENT => true to reuse connections across requests (poor man's pooling). On PHP-FPM, this is often the simplest way to reduce connection overhead without adding PgBouncer or ProxySQL.