Изменение данных и транзакции

Чтение из базы — половина работы. Вторая половина — добавлять, изменять и удалять записи так, чтобы данные оставались согласованными, даже если посреди операции что-то сломается.

Примеры используют базу SQLite в памяти и здесь не запускаются — выполните их у себя.

Добавление записи

Запрос INSERT выполняют через подготовленный запрос. Идентификатор новой строки возвращает lastInsertId():

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
 
$statement = $pdo->prepare('INSERT INTO products (title, price) VALUES (?, ?)');
$statement->execute(['Чай', 250]);
 
echo $pdo->lastInsertId(); // 1

Изменение и число строк

После UPDATE и DELETE метод rowCount() сообщает, сколько строк затронуто:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT, price INTEGER)');
$pdo->exec("INSERT INTO products (title, price) VALUES ('Чай', 250), ('Кофе', 400), ('Сок', 180)");
 
$statement = $pdo->prepare('UPDATE products SET price = price + 50 WHERE price < ?');
$statement->execute([300]);
 
echo $statement->rowCount(); // 2

Если число равно нулю, условие не подошло ни одной строке — например, записи с таким идентификатором нет.

Удаление

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, title TEXT)');
$pdo->exec("INSERT INTO products (title) VALUES ('Чай'), ('Кофе')");
 
$statement = $pdo->prepare('DELETE FROM products WHERE id = ?');
$statement->execute([1]);
 
echo $pdo->query('SELECT COUNT(*) FROM products')->fetchColumn(); // 1

Зачем нужны транзакции

Перевод денег — это два запроса: списать с одного счёта и зачислить на другой. Если программа упадёт между ними, деньги исчезнут. Транзакция объединяет запросы: либо выполняются все, либо ни один.

Транзакция

beginTransaction() начинает транзакцию, commit() сохраняет изменения, rollBack() отменяет:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)');
$pdo->exec('INSERT INTO accounts (balance) VALUES (500), (100)');
 
$pdo->beginTransaction();
$pdo->exec('UPDATE accounts SET balance = balance - 200 WHERE id = 1');
$pdo->exec('UPDATE accounts SET balance = balance + 200 WHERE id = 2');
$pdo->commit();
 
echo implode(', ', $pdo->query('SELECT balance FROM accounts')->fetchAll(PDO::FETCH_COLUMN)); // 300, 300

Откат при ошибке

Запросы помещают в try. Любая ошибка отменяет всё, что транзакция успела сделать:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER CHECK (balance >= 0))');
$pdo->exec('INSERT INTO accounts (balance) VALUES (500), (100)');
 
try {
    $pdo->beginTransaction();
    $pdo->exec('UPDATE accounts SET balance = balance + 900 WHERE id = 2');
    $pdo->exec('UPDATE accounts SET balance = balance - 900 WHERE id = 1');
    $pdo->commit();
} catch (PDOException $error) {
    $pdo->rollBack();
    echo 'Перевод отменён', "\n";
}
 
echo implode(', ', $pdo->query('SELECT balance FROM accounts')->fetchAll(PDO::FETCH_COLUMN)); // 500, 100

Первый запрос выполнился, второй нарушил правило «баланс не меньше нуля» — и зачисление тоже отменилось.

Частая ошибка

UPDATE и DELETE без WHERE меняют или удаляют все строки таблицы. Проверяйте условие дважды, а перед опасным запросом на рабочей базе сначала выполните SELECT с тем же условием — он покажет, какие строки будут затронуты.

Спасибо, что читаете!