14 Nov 08 _ MySQL Transactions With CakePHP 1.2
Whew, been a while since I’ve posted something useful.
Hopefully, everyone is aware of MySQL transactions and how useful they can be. They come in especially handy when you want to save multiple related entries into different tables in your app. Cake actually already uses this in a behind the scenes way with the Model::saveAll() function, which does some of this for you (you can read more about saving your data with saveAll() here).
But you can easily use Transactions for anything you want to do.
Note: for this to work at all you have to make sure whatever table you’re using transactions with are of the type InnoDB, and not the standard MyISAM type.
ALTER TABLE table_name type=InnoDB;Ok, in your model, set the variable:
var $transactional = true;Then, in a function you want to use transactions, call the datasource object:
$db =& ConnectionManager::getDataSource($this->useDbConfig);Then, before you start interacting with the database, you call:
$db->begin($this);If your action is successful, run:
$db->commit($this);Or, if there’s a failure, you can roll back your DB with:
$db->rollback($this);Note that your DB will automatically rollback if there’s not a commit() command anyway.
In the course of a Model class, the usage of these functions could look something like this:
class YourModel extends AppModel {
var $transactional = true;
function someCoolModelAction()
{
# Grab DataSource Object #
$db =& ConnectionManager::getDataSource($this->useDbConfig);
# Start Transaction #
$db->begin($this);
/* SQL or Model Functions for whatever you need to do go here */
# If your database interaction was successful, then commit the changes to the Database ( I'm assuming there is some boolean
# variable, $successVar, that describes whether the action was successful or not).
if ($successVar === true) {
return $db->commit($this);
} else {
# If it wasn't successful, rollback any changes and you're good #
$db->rollback($this);
return false;
}
}
}
As you can see, it’s pretty easy. And cool.
