Databases with PHP
Access MySQL and PostgreSQL safely with PDO prepared statements and transactions.
PDO Connections
PHP Data Objects provide a database abstraction layer. new PDO(dsn, user, pass, options) connects with ERRMODE_EXCEPTION recommended for fail-fast errors.
DSN strings specify driver, host, dbname, charset=utf8mb4 for full Unicode. Reuse connection objects via dependency injection singletons.
Close connections by unsetting PDO references; pooling handled by external middleware in production.
- Never embed credentials in source control
- Use utf8mb4 for emoji and full Unicode
- Log connection failures without exposing passwords
$pdo = new PDO(
'mysql:host=localhost;dbname=shop;charset=utf8mb4',
$user,
$pass,
[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION],
);Prepared Statements
Prepare SQL with placeholders :name or ?. bindParam/bindValue supplies values preventing SQL injection. execute runs the statement.
fetch, fetchAll, and fetchColumn retrieve rows as objects or arrays. PDO::FETCH_CLASS hydrates custom classes.
Never concatenate user input into SQL strings, even for "safe-looking" integers without casting.
- Use transactions for related writes
- Index columns used in WHERE and JOIN
- Limit SELECT * in hot paths—project needed columns
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);