PHP PDO Transaction

Summary: in this tutorial, you will learn how to perform a database transaction from PHP by using PDO API.

Introduction to PHP PDO transaction #

To start a transaction in PDO, you use the PDO::beginTransaction() method:

$pdo->beginTransaction();Code language: PHP (php)

The beginTransaction() method turns off the autocommit mode. It means that the changes made to the database via the PDO object won’t take effect until you call the PDO::commit() method.

To commit a transaction, you call the PDO::commit() method:

$pdo->commit();Code language: PHP (php)

If you want to roll back the transaction, you can call the PDO::rollback() method:

$pdo->rollback();Code language: PHP (php)

The PDO::rollback() method rolls back all changes made to the database. Also, it returns the connection to the autocommit mode.

The PDO::beginTransaction() method throws an exception if the database doesn’t support transactions.

PDO transaction example #

Suppose that you need to insert data into three tables: books, authors, and book_authors.

To do that, you need to:

• Get the author id if the author exists; otherwise, insert the author into the authors table.
• Insert the book into the books table.
• Insert the link between book and author into the book_authors table.

To organize the code, we’ll place all functions in the functions.php file and include it in the mains script.

The following get_author_id() function finds author by first name and last name and returns the author id if the author exists in the authors table:

<?php

function get_author_id(\PDO $pdo, string $first_name, string $last_name)
{
	$sql = 'SELECT author_id 
            FROM authors 
            WHERE first_name = :first_name 
                AND last_name = :last_name';

	$statement = $pdo->prepare($sql);

	$statement->bindParam(':first_name', $first_name, PDO::PARAM_STR);
	$statement->bindParam(':last_name', $last_name, PDO::PARAM_STR);

	if ($statement->execute()) {
		$row = $statement->fetch(PDO::FETCH_ASSOC);
		return $row !== false ? $row['author_id'] : false;
	}

	return false;
}Code language: HTML, XML (xml)

The following insert_author() function inserts a new author into the authors table and returns the author id:

<?php

function insert_author(\PDO $pdo, string $first_name, string $last_name): int
{
	$sql = 'INSERT INTO authors(first_name, last_name) 
            VALUES(:first_name, :last_name)';

	$statement = $pdo->prepare($sql);

	$statement->bindParam(':first_name', $first_name, PDO::PARAM_STR);
	$statement->bindParam(':last_name', $last_name, PDO::PARAM_STR);

	$statement->execute();

	return  $pdo->lastInsertId();
}Code language: HTML, XML (xml)

The following insert_book() function inserts a new book into the books table:

<?php

function insert_book(\PDO $pdo, string $title, string $isbn, string $published_date, int $publisher_id): int
{
	$sql = 'INSERT INTO books(title, isbn, published_date, publisher_id) 
            VALUES(:title, :isbn, :published_date, :publisher_id)';

	$statement = $pdo->prepare($sql);

	$statement->bindParam(':title', $title, PDO::PARAM_STR);
	$statement->bindParam(':isbn', $isbn, PDO::PARAM_STR);
	$statement->bindParam(':published_date', $published_date, PDO::PARAM_STR);
	$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);

	$statement->execute();

	return  $pdo->lastInsertId();
}Code language: HTML, XML (xml)

The following function inserts a new row into the book_authors table:

<?php

function insert_book_author(\PDO $pdo, int $book_id, int $author_id)
{
	$sql = 'INSERT INTO book_authors(book_id, author_id) 
            VALUES(:book_id, :author_id)';

	$statement = $pdo->prepare($sql);

	$statement->bindParam(':book_id', $book_id, PDO::PARAM_INT);
	$statement->bindParam(':author_id', $author_id, PDO::PARAM_INT);

	$statement->execute();
}Code language: HTML, XML (xml)

The following script uses those functions above to perform a transaction:

<?php

require 'functions.php';
$pdo = require 'connect.php';

$book = [
	'title' => 'Eternal',
	'isbn' => '9780525539766',
	'published_date' => '2021-03-23',
	'publisher_id' => 2,
];

$author = [
	'first_name' => 'Lisa',
	'last_name' => 'Scottoline',
];

try {
	$pdo->beginTransaction();

	// find the author by first name and last name
	$author_id = get_author_id(
		$pdo,
		$author['first_name'],
		$author['last_name']
	);

	// if author not found, insert a new author
	if (!$author_id) {
		$author_id = insert_author(
			$pdo,
			$author['first_name'],
			$author['last_name']
		);
	}

	$book_id = insert_book(
		$pdo,
		$book['title'],
		$book['isbn'],
		$book['published_date'],
		$book['publisher_id']
	);

	// insert the link between book and author
	insert_book_author($pdo, $book_id, $author_id);

	// commit the transaction
	$pdo->commit();
} catch (\PDOException $e) {
	// rollback the transaction
	$pdo->rollBack();

	// show the error message
	die($e->getMessage());
}Code language: HTML, XML (xml)

Summary #

• Use the PDO::beginTransaction() method to start a new transaction.
• Use the PDO::commit() method to commit a transaction and PDO::rollback() to roll back a transaction.