PDO Transactions

Transactions provide a method of batch processing a saved set of queries to be carried out (committed) at a later time. They also have the advantage of being able to roll back should a problem arise.

 

The 3 PDO methods utilised for this functionality are:

  • beginTransaction()
  • commit()
  • rollback()

 

After much testing, and finding the rollBack() method wasn't working, I discovered:

  • Ensure your database supports transactions!
  • MyISAM tables do not support transactions
  • Use InnoDB

 

This example has been stripped right down to purely focus on transactions:

  1. The beginTransaction() method is called by the database object, thus turning off auto-commit mode
  2. Four SQL insert queries are executed, but are not committed to the database
  3. The fifth SQL insert query contains errors
  4. The commit() method is called by the database object, but since the above contains an error an exception is thrown
  5. The exception is caught in the catch block
  6. The rollBack() method is called by the database object, and rolls back the above queries
  7. The database appears unchanged
<?php
	include_once('connect.php');
	include_once('error_handling.php');

	try{
		//begin the transaction
		$dbh->beginTransaction();

		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Bradley Smith', 'Yamaha', '41', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Michele Pirro', 'Ducati', '30', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Andrea Iannone', 'Ducati', '24', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Randy De Puniet', 'ART', '15', 'CRT')");
		$dbh->query("INSERT INTO potato (dog, spoon, car, pool) VALUES ('700', 'Change', 'Bye', '99')");

		foreach($dbh->query('SELECT * from riders') as $row) {
			echo $row['name'] . " rides for " . $row['team'] . "<br>";
		}

		$dbh->commit();
	}
	catch(PDOException $error){
		$dbh->rollBack();
		echo errorHandling($error);
	}
?>

Save & refresh browser:

Connected to the motogp database
Error!
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'motogp.potato' doesn't exist
Class & Method: PDO & query
File: /path/user/public_html/pdo/index.php
Line: 13

 

 

Simply comment out line 12 with the errors to see the queries committed to the database:

<?php
	include_once('connect.php');
	include_once('error_handling.php');

	try{
		//begin the transaction
		$dbh->beginTransaction();

		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Bradley Smith', 'Yamaha', '41', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Michele Pirro', 'Ducati', '30', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Andrea Iannone', 'Ducati', '24', 'Factory')");
		$dbh->query("INSERT INTO riders (name, team, points, status) VALUES ('Randy De Puniet', 'ART', '15', 'CRT')");
	//	$dbh->query("INSERT INTO potato (dog, spoon, car, pool) VALUES ('700', 'Change', 'Bye', '99')");

		foreach($dbh->query('SELECT * from riders') as $row) {
			echo $row['name'] . " rides for " . $row['team'] . "<br>";
		}

		$dbh->commit();
	}
	catch(PDOException $error){
		$dbh->rollBack();
		echo errorHandling($error);
	}
?>

Save & refresh browser:

Connected to the motogp database
Dani Pedrosa rides for Honda
Jorge Lorenzo rides for Yamaha
Marc Marquez rides for Honda
Cal Crutchlow rides for Yamaha
Valentino Rossi rides for Yamaha
Andrea Dovizioso rides for Ducati
Stefan Bradl rides for Honda
Nicky Hayden rides for Ducati
Alvaro Bautista rides for Honda
Aleix Espargaro rides for ART
Bradley Smith rides for Yamaha
Michele Pirro rides for Ducati
Andrea Iannone rides for Ducati
Randy De Puniet rides for ART

Leave a Reply