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();Copy

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();Copy

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

$pdo->rollback();Copy

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;
}Copy

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();
}Copy

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();
}Copy

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();
}Copy

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());
}Copy

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.

Was this helpful?

YesNo