PHP MYSQL Transactions

PHP MYSQL Transactions and Rollbacks

If you decide to create a php / mySQL application which will use a fair amount of insert, update and delete queries, you may want to use transactions with your mySQL queries. Transactions allows for data integrity. It can only help keep your data more precise. In order to use transactions, the database table storage engine should use the InnoDB.

Transactions work like this. Multiple mySQL statements will be executed only if the requirements are met. If anything is off, nothing happens.

The code below shows a simple transaction. If there is asuccess, the loop runs. If there is no success, the loop does dot show anything.

include('connect.inc');

	   $success = true;  // transaction success

       //start transaction
       $command = "SET AUTOCOMMIT=0"; // this command sets mysql up to make sure that COMMIT or ROLLBACK command is used to execute a query.
       $result = mysqli_query($db, $command);
       $command = "BEGIN"; // this starts the transaction
       $result = mysqli_query($db, $command);
                 
       $command = "SELECT id FROM table_sort ";
       $result = mysqli_query($db, $command);  
       while($row = mysqli_fetch_assoc($result)){
       $id = $row['id'];
	   $ids[] = $id;
	    echo "We got the array!";
		}	        		  
			  if (mysqli_num_rows($result) <2) { // checking for success here
                   $success = false;
              }  else if (mysqli_num_rows($result) >2){
			     $success = true;
			  echo "We got the array!";
			  }
           
       if (!$success) { // if success is false, do this
              $command = "ROLLBACK"; // this command does not allow the statement "We go the array" to display if the $result is false
              $result = mysqli_query($db, $command);
              $error = "We've rolled back and did not create the array because something went wrong along the way.";
			  echo $error;
       }
       else {
	          $command = "COMMIT"; // transaction will now occur since everything went well
              $result = mysqli_query($db, $command);
      }
       $command = "SET AUTOCOMMIT=1";  // Now, mysql continues on without transactions
	   $result = mysqli_query($db, $command);