PHP Application Development With ADODB (part 2) - A Fear Of Commitment (
Page 3 of 7 )
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.
/ 8 
