How to Perform a Database Transaction from PHP using PDO (original) (raw)

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:

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,stringpdo, string pdo,stringfirst_name, string $last_name) { $sql = 'SELECT author_id FROM authors WHERE first_name = :first_name AND last_name = :last_name';

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

statement−>bindParam(′:firstname′,statement->bindParam(':first_name', statement−>bindParam(′:firstn​ame′,first_name, PDO::PARAM_STR); statement−>bindParam(′:lastname′,statement->bindParam(':last_name', statement−>bindParam(′:lastn​ame′,last_name, PDO::PARAM_STR);

if ($statement->execute()) {
 <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>o</mi><mi>w</mi><mo>=</mo></mrow><annotation encoding="application/x-tex">row = </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.4306em;"></span><span class="mord mathnormal">ro</span><span class="mord mathnormal" style="margin-right:0.02691em;">w</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">=</span></span></span></span>statement->fetch(PDO::FETCH_ASSOC);
    return <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>o</mi><mi>w</mi><mo stretchy="false">!</mo><mo>=</mo><mo>=</mo><mi>f</mi><mi>a</mi><mi>l</mi><mi>s</mi><mi>e</mi><mo stretchy="false">?</mo></mrow><annotation encoding="application/x-tex">row !== false ? </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.6944em;"></span><span class="mord mathnormal">ro</span><span class="mord mathnormal" style="margin-right:0.02691em;">w</span><span class="mclose">!</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">==</span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal" style="margin-right:0.10764em;">f</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord mathnormal">se</span><span class="mclose">?</span></span></span></span>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,stringpdo, string pdo,stringfirst_name, string $last_name): int { $sql = 'INSERT INTO authors(first_name, last_name) VALUES(:first_name, :last_name)';

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

statement−>bindParam(′:firstname′,statement->bindParam(':first_name', statement−>bindParam(′:firstn​ame′,first_name, PDO::PARAM_STR); statement−>bindParam(′:lastname′,statement->bindParam(':last_name', statement−>bindParam(′:lastn​ame′,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,stringpdo, string pdo,stringtitle, string isbn,stringisbn, string isbn,stringpublished_date, int $publisher_id): int { $sql = 'INSERT INTO books(title, isbn, published_date, publisher_id) VALUES(:title, :isbn, :published_date, :publisher_id)';

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

statement−>bindParam(′:title′,statement->bindParam(':title', statement−>bindParam(′:title′,title, PDO::PARAM_STR); statement−>bindParam(′:isbn′,statement->bindParam(':isbn', statement−>bindParam(′:isbn′,isbn, PDO::PARAM_STR); statement−>bindParam(′:publisheddate′,statement->bindParam(':published_date', statement−>bindParam(′:publishedd​ate′,published_date, PDO::PARAM_STR); statement−>bindParam(′:publisherid′,statement->bindParam(':publisher_id', statement−>bindParam(′:publisheri​d′,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,intpdo, int pdo,intbook_id, int $author_id) { $sql = 'INSERT INTO book_authors(book_id, author_id) VALUES(:book_id, :author_id)';

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

statement−>bindParam(′:bookid′,statement->bindParam(':book_id', statement−>bindParam(′:booki​d′,book_id, PDO::PARAM_INT); statement−>bindParam(′:authorid′,statement->bindParam(':author_id', statement−>bindParam(′:authori​d′,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, <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>b</mi><mi>o</mi><mi>o</mi><msub><mi>k</mi><mi>i</mi></msub><mi>d</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">book_id, </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">b</span><span class="mord mathnormal">oo</span><span class="mord"><span class="mord mathnormal" style="margin-right:0.03148em;">k</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.3117em;"><span style="top:-2.55em;margin-left:-0.0315em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">i</span></span></span></span><span class="vlist-s">​</span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal">d</span><span class="mpunct">,</span></span></span></span>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 #

Did you find this tutorial useful?