Why PDO?
PDO (PHP Data Objects) is PHP's official database abstraction layer. One API works with MySQL, PostgreSQL, SQLite, SQL Server, Oracle, and more - swap the DSN, keep the code.
Connecting to a Database
PHP
<?php
// MySQL / MariaDB
$dsn = "mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4";
// PostgreSQL
// $dsn = "pgsql:host=127.0.0.1;dbname=myapp";
// SQLite (file)
// $dsn = "sqlite:" . __DIR__ . "/db.sqlite";
// SQLite in-memory (great for tests)
// $dsn = "sqlite::memory:";
$pdo = new PDO($dsn, "username", "password");
Safe Connection Options
The recommended PDO setup for every new project:
PHPbootstrap.php
<?php
$dsn = "mysql:host=127.0.0.1;dbname=myapp;charset=utf8mb4";
$pdo = new PDO($dsn, "username", "password", [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4",
]);
| Option | Why |
|---|---|
ERRMODE_EXCEPTION | Throw on errors instead of silent false returns |
FETCH_ASSOC | Get associative arrays - cleaner than default mixed |
EMULATE_PREPARES => false | Real server-side prepares, native types preserved |
STRINGIFY_FETCHES => false | Keep ints as ints, floats as floats |
charset=utf8mb4 in DSN | Full Unicode (emojis included) |
Simple Queries
PHP
<?php
// One-shot exec - INSERT/UPDATE/DELETE without parameters
$rowsAffected = $pdo->exec("DELETE FROM logs WHERE created < '2024-01-01'");
// Direct query - SELECT without parameters
// Only safe when SQL is fully static!
$stmt = $pdo->query("SELECT NOW() AS now");
$row = $stmt->fetch();
echo $row["now"];
Never concatenate user input into SQL
Even (int) casts can be bypassed. Always use prepared statements with placeholders. "SELECT * FROM users WHERE id = $_GET['id']" is a SQL injection waiting to happen.
Fetch Modes
PHP
<?php
$stmt = $pdo->query("SELECT id, name, email FROM users");
// fetch() - one row at a time (returns false when done)
while ($row = $stmt->fetch()) {
echo $row["name"];
}
// fetchAll() - all rows as an array
$rows = $stmt->fetchAll(); // [["id"=>1,"name"=>"A",...], ...]
// fetchColumn() - just one column from one row
$count = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
// fetchAll(PDO::FETCH_KEY_PAIR) - 2 columns -> assoc array
$idsByName = $pdo->query("SELECT name, id FROM users")
->fetchAll(PDO::FETCH_KEY_PAIR);
// ["Alice" => 1, "Bob" => 2, ...]
// Fetch into objects of a class
$stmt = $pdo->query("SELECT * FROM users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, User::class);
Prepared Statements
The ONLY safe way to use user input in SQL:
PHP
<?php
// Positional placeholders
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ? AND active = ?");
$stmt->execute([42, true]);
$user = $stmt->fetch();
// Named placeholders (preferred for readability)
$stmt = $pdo->prepare(
"SELECT * FROM users WHERE email = :email AND active = :active"
);
$stmt->execute([":email" => "x@y.com", ":active" => true]);
$user = $stmt->fetch();
// Bind explicitly with types (rarely needed when emulate=false)
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = :id");
$stmt->bindValue(":id", 42, PDO::PARAM_INT);
$stmt->execute();
Inserting & Last Insert ID
PHP
<?php
$stmt = $pdo->prepare(
"INSERT INTO users (name, email, created_at) VALUES (?, ?, NOW())"
);
$stmt->execute(["Ruban", "ruban@example.com"]);
// Auto-increment ID just created
$newId = $pdo->lastInsertId();
// Number of rows affected (UPDATE/DELETE)
$stmt = $pdo->prepare("UPDATE users SET active = 0 WHERE last_login < ?");
$stmt->execute(["2024-01-01"]);
echo $stmt->rowCount(); // e.g. 17
PDO vs mysqli
| Aspect | PDO | mysqli |
|---|---|---|
| Database support | 12+ drivers | MySQL/MariaDB only |
| Prepared statements | Cleaner API, named params | Positional only, weirder syntax |
| Object orientation | OOP only | OOP + procedural |
| Used by frameworks | Yes (Laravel, Symfony) | Rare |
| Recommendation | Use this | Legacy code only |