Safely Handling MySQL Transactions in Zend Framework


Preface

If you’re using Zend MVC and attempting to store transactional data, below is a quick and easy way to handle exceptions while still allowing rollbacks.

The code will essentially ensure that your transaction safely fails if an exception is thrown (i.e. the dreaded Integrity constraint violation: 1062 Duplicate entry) and fall back on your error controller for handling the exception. You can then deal with these exceptions in any manner you see fit by utilising Zend_Log_Writer_Stream.

Code Example

<?php
// the below code assumes you have the private/protected
// variable $this->_db set to your database adapter.
// begin the transaction
$this->_db->beginTransaction();
try {
     // mock insert into a user table
     $result = $this->_db->insert(‘users’, array(
          ‘name’ => ‘Corey’,
          ’email’ => ‘duplicate@alreadyexists.com’,
          ‘password’ => sha1(‘FakeSalt’ . ‘FakePass’),
          ‘created_ts’ => time(),
          ‘modified_ts’ => NULL
     ));
     // begin process of roles insertion
     if ($result) {
          // grab the user id from the last insert
          $user_id = $this->_db->lastInsertId();
          // insert into a mock user roles table
          $result = $this->_db->insert(‘users_roles’, array(
               ‘user_id’ => $user_id,
               ‘role_id’ => 1
          ));
          // on success, commit transaction and return the user_id
          if ($result) {
               $this->_db->commit();
               return $user_id;
          }
     }
} catch (Exception $e) {
     // THIS IS WHERE THE MAGIC HAPPENS
     // rollback
     $this->_db->rollback();
     // throw the same exception so it bubbles
     // back up to the error controller
     throw $e;
}
?>
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s