Category Archives: MySQL

Commit and Rollback in PHP

index.php

<?php
	function commit(){
		mysql_query("COMMIT");
	}

	function rollback(){
		mysql_query("ROLLBACK");
	}

	mysql_connect("localhost","root", "") or die(mysql_error());
	mysql_select_db("test") or die(mysql_error());
	
	mysql_query("SET AUTOCOMMIT=0");

	$flag = true;
	
  // Valid SQL
	$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";
	$result = mysql_query($query) or die(mysql_error());

	if (!$result) {
		$flag = false;
	}

  // Invalid SQL
	$query2 = "INSERT INTO employee (ssn,name,phone) values ('123-45-0000','Apple','1-800-555-1213' A)";
	$result = mysql_query($query2) or die(mysql_error());
	if (!$result) {
		$flag = false;
	}
	
	if ($flag== false) {
		rollback(); // transaction rolls back
		echo "transaction rolled back";
		exit;
	}else{
		commit(); // transaction is committed
		echo "Database transaction was successful";
	}
?>

employee.sql

CREATE TABLE `employee` (
  `ssn` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `phone` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`ssn`, `name`, `phone`) VALUES
('123-45-6789', 'Matt', '1-800-555-1212'),
('123-45-0000', 'Apple', '1-800-555-1213');