Changing data and transactions

Reading from a database is half the job. The other half is adding, changing and deleting records so that the data stays consistent even if something breaks in the middle of an operation.

The examples in this lesson use an in-memory SQLite database. The site's browser sandbox has no database support, so they have no "Run" button — run them on your own machine.

Adding a record

An INSERT query is run through a prepared statement. lastInsertId() returns the identifier of the new row:

<?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(['Tea', 250]);
 
echo $pdo->lastInsertId(); // 1

Updating and the row count

After UPDATE and DELETE the rowCount() method tells how many rows were affected:

<?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 ('Tea', 250), ('Coffee', 400), ('Juice', 180)");
 
$statement = $pdo->prepare('UPDATE products SET price = price + 50 WHERE price < ?');
$statement->execute([300]);
 
echo $statement->rowCount(); // 2

If the number is zero, the condition matched no rows — there is no record with that identifier, for example.

Deleting

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

Why transactions are needed

A money transfer is two queries: take from one account and add to another. If the program crashes between them, the money vanishes. A transaction ties the queries together: either all of them run or none does.

A transaction

beginTransaction() starts a transaction, commit() saves the changes, rollBack() undoes them:

<?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

Rolling back on an error

The queries go into try. Any error undoes everything the transaction has done so far:

<?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 'Transfer cancelled', "\n";
}
 
echo implode(', ', $pdo->query('SELECT balance FROM accounts')->fetchAll(PDO::FETCH_COLUMN)); // 500, 100

The first query ran, the second broke the rule "the balance is not below zero" — and the deposit was undone as well.

Common mistake

UPDATE and DELETE without WHERE change or delete every row of the table. Check the condition twice, and before a risky query on a live database run a SELECT with the same condition first — it shows which rows will be affected.

Thanks for reading!