MySQL transactions in Zend framework

MySQL transactions in Zend framework © by jrdurao/sxc

Hi! Today I’m going to explain a part of Zend framework DB functionality. Transactions in general are quite useful, like temporary tables, but in most of situations unnecessary. Because of that, I’m going to explain when and how to use them through Zend framework.

What is transaction?

A simple answer would be – a number of individual queries that are grouped together.

An example would be something like this:

UPDATE balance SET total = total - 100 WHERE account_id=1;
UPDATE balance SET total = total + 100 WHERE account_id=2;

Why transactions?

In general web development, transactions are unnecessary. If for some reason post in your CMS isn’t saved, you’ll write it again. It is a problem, but only annoying one, not a crucial one. But if you own a web shop, and someone create an order, pay for it, and you – owner of that store can’t see it because of database glitch, that would be a problem. Take a look at example above. I only first query in that transaction was executed, total amount of money from account would be decreased by 100, and that money would simply disappear (if we’re looking at your database). Or if only second was executed, account 2 would have 100 extra in its total sum – again virtually from nowhere.

This is the place where transactions are necessary. Because transactions either execute all queries assigned to it, or none of them. I hope you see the point. 😀

The syntax

This is a basic example from official MySQL documentation:

START TRANSACTION;
--BEGIN TRANSACTION
SELECT @A:=SUM(salary) FROM table1 WHERE TYPE=1;
UPDATE table2 SET summary=@A WHERE TYPE=1;
--SET SOME QUERIES
COMMIT;
--AND COMMIT THEM

When the Zend framework comes in

Its pretty much self explained, so here’s a code snippet with comments:

$db = self::db();
//GET DATABASE CONNECTION (DEPENDING ON YOUR SETUP)
 
$q =  "UPDATE balance SET total = total - 100 WHERE account_id=1;UPDATE balance SET total = total + 100 WHERE account_id=2;";
//PREPARE QUERY
 
$db->beginTransaction();
//BEGIN TRANSACTION ON DATABASE CONNECTION
try
{
$db->exec($q);
//SET QUERY YOU WISH TO EXECUTE
 
$query = $db->commit();
//COMMIT QUERY TO DATABASE
}
catch(Exception $e)
{
$db->rollBack();
//ROLLBACK IF TRANSACTION FAILS
 
$error_result = Array();
$message = $e->getMessage();
$code = $e->getCode();
$error_result[0] = $message;
$error_result[1] = $code;
//GET ERROR INFORMATION IF SQL FAILS
}

Conclusion

Now, You’ve created a transaction in your Zend project. You’ll get a rollback on your database if any of the queries fails and you won’t have any “missing records and values” in your database. This isn’t so complicated to create for every crucial query in your application, especially when you know its going to work like a charm!

Feel free to comment, as I might learn something new as well. Bye!

You made it all the way down here so you must have enjoyed this post! You may also like:

Using Redis cache backend and session storage in Magento Marko Martinovic
Marko Martinovic, | 43

Using Redis cache backend and session storage in Magento

Consuming Magento REST service using Zend_OAuth_Consumer Darko Goles
Darko Goles, | 45

Consuming Magento REST service using Zend_OAuth_Consumer

Create Windows virtual hosts for your Magento projects Nikola Stojiljkovic
Nikola Stojiljkovic, | 8

Create Windows virtual hosts for your Magento projects

13 comments

  1. Hello, Thanks for sharing the information about transaction in Zend.

    Can you please explain , If we are using multiple queries in different method, how we can implement the transaction.

  2. I am new and i want to store image in MySql through Zend Framework after that show that image in some other page ….. help me pleas

    1. Thanks for your comment !
      Spent 2 hours wondering why my rollback didn’t rollback every record !

  3. Hi Eduardo,

    there are many ways of doing it in specific situation. That’s the way for Default connection, but if you’re using more than one connection inside your application, it can’t be done that way.

    That’s why I wrote:

    .//GET DATABASE CONNECTION (DEPENDING ON YOUR SETUP)

    Cheers!

  4. Supposing I’m in the model that extends Zend_Db_Table and using the PDO_Adapter, is there a shortcut to

    $this->getDefaultAdapter()->beginTransaction();

    ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.