PDO (PHP Data Objects)
Подключение
<?php
declare(strict_types=1);
// Basic connection
$pdo = new PDO(
'mysql:host=localhost;dbname=myapp;charset=utf8mb4',
'username',
'password',
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
]
);
// PostgreSQL
$pdo = new PDO('pgsql:host=localhost;dbname=myapp', 'user', 'pass');
// SQLite
$pdo = new PDO('sqlite:/path/to/db.sqlite');
$pdo = new PDO('sqlite::memory:'); // In-memory database
Error Modes
<?php
declare(strict_types=1);
// PDO::ERRMODE_SILENT — default, check manually
// PDO::ERRMODE_WARNING — trigger E_WARNING
// PDO::ERRMODE_EXCEPTION — throw PDOException (RECOMMENDED)
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
$pdo->exec('INVALID SQL');
} catch (PDOException $e) {
echo $e->getMessage();
echo $e->getCode(); // SQLSTATE error code
}
Запомни: ВСЕГДА устанавливайте
PDO::ERRMODE_EXCEPTION. Режим по умолчанию (SILENT) молча игнорирует ошибки, что крайне опасно. На экзамене часто спрашивают режимы ошибок.
Prepared Statements
<?php
declare(strict_types=1);
// Named placeholders (recommended)
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND active = :active');
$stmt->execute([
'email' => '[email protected]',
'active' => true,
]);
$user = $stmt->fetch();
// Positional placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE age > ? AND city = ?');
$stmt->execute([18, 'Moscow']);
$users = $stmt->fetchAll();
// bindValue — binds a VALUE (copy)
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindValue(':id', 42, PDO::PARAM_INT);
$stmt->execute();
// bindParam — binds a VARIABLE (reference)
$id = 1;
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 1;
$stmt->execute(); // Queries with id=1
$id = 2;
$stmt->execute(); // Queries with id=2 (same prepared statement)
Ловушка экзамена:
bindValue()привязывает КОПИЮ значения на момент вызова.bindParam()привязывает ССЫЛКУ на переменную — значение берётся в моментexecute(). Это ключевое различие!
INSERT, UPDATE, DELETE
<?php
declare(strict_types=1);
// INSERT
$stmt = $pdo->prepare('INSERT INTO users (name, email, age) VALUES (:name, :email, :age)');
$stmt->execute([
'name' => 'Alice',
'email' => '[email protected]',
'age' => 25,
]);
// Last insert ID
$lastId = $pdo->lastInsertId();
// UPDATE
$stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id');
$stmt->execute(['name' => 'Alice Smith', 'id' => 1]);
// Affected rows
echo $stmt->rowCount(); // Number of updated rows
// DELETE
$stmt = $pdo->prepare('DELETE FROM users WHERE id = :id');
$stmt->execute(['id' => 1]);
echo $stmt->rowCount(); // Number of deleted rows
Fetch Modes
<?php
declare(strict_types=1);
$stmt = $pdo->query('SELECT id, name, email FROM users');
// PDO::FETCH_ASSOC — associative array
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// ['id' => 1, 'name' => 'Alice', 'email' => '[email protected]']
// PDO::FETCH_NUM — numeric array
$row = $stmt->fetch(PDO::FETCH_NUM);
// [0 => 1, 1 => 'Alice', 2 => '[email protected]']
// PDO::FETCH_BOTH — both (default)
$row = $stmt->fetch(PDO::FETCH_BOTH);
// [0 => 1, 'id' => 1, 1 => 'Alice', 'name' => 'Alice', ...]
// PDO::FETCH_OBJ — stdClass object
$row = $stmt->fetch(PDO::FETCH_OBJ);
// object(stdClass) { $id = 1, $name = 'Alice', ... }
// PDO::FETCH_CLASS — custom class
class User {
public int $id;
public string $name;
public string $email;
}
$stmt->setFetchMode(PDO::FETCH_CLASS, User::class);
$user = $stmt->fetch(); // User object
// PDO::FETCH_COLUMN — single column
$names = $pdo->query('SELECT name FROM users')->fetchAll(PDO::FETCH_COLUMN);
// ['Alice', 'Bob', 'Charlie']
// PDO::FETCH_KEY_PAIR — key-value pairs
$map = $pdo->query('SELECT id, name FROM users')->fetchAll(PDO::FETCH_KEY_PAIR);
// [1 => 'Alice', 2 => 'Bob', 3 => 'Charlie']
// PDO::FETCH_UNIQUE — indexed by first column
$indexed = $pdo->query('SELECT id, name, email FROM users')->fetchAll(PDO::FETCH_UNIQUE);
// [1 => ['name' => 'Alice', 'email' => '...'], ...]
// PDO::FETCH_GROUP — group by first column
$grouped = $pdo->query('SELECT city, name FROM users')->fetchAll(PDO::FETCH_GROUP);
// ['Moscow' => [['name' => 'Alice'], ['name' => 'Bob']], 'Paris' => [...]]
Транзакции
<?php
declare(strict_types=1);
try {
$pdo->beginTransaction();
$stmt1 = $pdo->prepare('UPDATE accounts SET balance = balance - :amount WHERE id = :from');
$stmt1->execute(['amount' => 100, 'from' => 1]);
if ($stmt1->rowCount() === 0) {
throw new RuntimeException('Source account not found');
}
$stmt2 = $pdo->prepare('UPDATE accounts SET balance = balance + :amount WHERE id = :to');
$stmt2->execute(['amount' => 100, 'to' => 2]);
if ($stmt2->rowCount() === 0) {
throw new RuntimeException('Destination account not found');
}
$pdo->commit();
echo 'Transfer complete';
} catch (Throwable $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "Error: " . $e->getMessage();
}
Запомни: Транзакция — это атомарная операция: либо ВСЕ запросы выполняются, либо НИ ОДИН.
beginTransaction()начинает,commit()подтверждает,rollBack()откатывает. Всегда проверяйтеinTransaction()перед rollback.
Безопасность
<?php
declare(strict_types=1);
// NEVER DO THIS — SQL injection vulnerability!
$email = $_GET['email'];
$pdo->query("SELECT * FROM users WHERE email = '$email'");
// Input: ' OR 1=1 --
// Result: SELECT * FROM users WHERE email = '' OR 1=1 --'
// ALWAYS use prepared statements
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute(['email' => $email]);
// Identifiers (table/column names) cannot be parameterized
// Validate them with a whitelist
$allowedColumns = ['name', 'email', 'age'];
$column = $_GET['sort'] ?? 'name';
if (!in_array($column, $allowedColumns, true)) {
throw new InvalidArgumentException("Invalid column: {$column}");
}
$stmt = $pdo->query("SELECT * FROM users ORDER BY {$column}");