A Fear Of Commitment - PHP

If your database system supports transactions (MySQL doesn't, but quite a few others do), you'll be pleased to hear that ADODB allows you to transparently use this feature in your scripts.

The following example demonstrates:

<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the ADODB library
include("adodb.inc.php");
// create an object instance
// configure library for a MySQL connection
$db = NewADOConnection("mysql");
// open connection to database
$db->Connect("localhost", "john", "doe", "db278") or die("Unable to
connect!");
// turn off auto-commit
// begin transaction block
$db->BeginTrans();
// first query
$query = "INSERT INTO library (title, author) VALUES ('Title A', 'Author
B')"; $result = $db->Execute($query) or die("Error in query: $query. " .
$db->ErrorMsg());
// use ID from first query in second query
if ($result)
{
$id = $db->Insert_ID();
$query = "INSERT INTO purchase_info (id, price) VALUES ($id,
'USD 39.99')";
$result = $db->Execute($query) or die("Error in query: $query. "
. $db->ErrorMsg()); }
// if no failures
if ($result)
{
// commit
$db->CommitTrans();
}
// else rollback
else
{
$db->RollbackTrans();
}
// clean up
$db->Close;
?>

The first step here is to turn off auto-committal of data to the database, via the BeginTrans() method; this method also marks the beginning of a transaction block, one which can be ended by either CommitTrans() or RollbackTrans(). Once auto-commit has been turned off, you can go ahead and execute as many queries as you like, secure in the knowledge that no changes have (yet) been made to the database.

Every call to Execute() within the transaction block returns either a true or false value, depending on whether or not the query was successful. These values can be tracked, and used to determine whether or not the entire transaction should be committed. Once you're sure that all is well, you can save your data to the database via a call to the CommitTrans() method. In the event that you realize you made a mistake, you can rewind gracefully with the RollbackTrans() function.

>>> More PHP Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus