HomePHP Page 3 - PHP Application Development With ADODB (part 2)
A Fear Of Commitment - PHP
In this concluding article, find out about ADODB's advancedfunctions, with examples that demonstrate how ADODB can be used tooptimize multiple-run queries, commit and roll back transactions,improve performance by caching query results, and automatically writeHTML (ortext) files.
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:
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the ADODB library
// 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
// turn off auto-commit
// begin transaction block
// first query
$query = "INSERT INTO library (title, author) VALUES ('Title A', 'Author
B')"; $result = $db->Execute($query) or die("Error in query: $query. " .
// use ID from first query in second query
$id = $db->Insert_ID();
$query = "INSERT INTO purchase_info (id, price) VALUES ($id,
$result = $db->Execute($query) or die("Error in query: $query. "
. $db->ErrorMsg()); }
// if no failures
// else rollback
// clean up
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.