Database Abstraction With PHP - Commitment Issues (
Page 8 of 11 )
If your database system supports transactions (MySQL doesn't, but quite a few
others do), you'll be thrilled to hear that PHP's database abstraction layer
comes with a bunch of methods that allow you to transparently use this feature
in your scripts. What you may not be so thrilled about, though, is the fact that
it also allows me the opportunity to make a few bad puns.
The following
example demonstrates:
<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");
// include the DB abstraction layer
include("DB.php");
// connect to the database (Oracle)
$dbh = DB::connect("oci8://john:doe@listener/db287");
// turn off autocommit
$dbh->autoCommit(false);
// execute one query
$dbh->query("INSERT INTO cds (id,title, artist) VALUES(SEQ_CDS_ID.nextval,
'Weathered', 'Creed')");
// commit the data to database
$dbh->commit();
// execute one more query
$dbh->query("INSERT INTO cds (id,title, artist) VALUES(SEQ_CDS_ID.nextval,
'The Joshua Tree', 'U2')");
// now rollback to previous stage
$dbh->rollback();
// now if you check the contents of the table
// only one entry will be visible
// as the second action has been rolled back
// close database connection
$dbh->disconnect();
?>
The first step here is to turn off auto-committal of data to
the database, via the autoCommit() method. Once that's done, 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.
Once your fear of commitment passes
(and I don't mean just here, oh no!), you can save your data to the database via
a call to the commit() method. In the event that you realize you made a mistake,
you can uncommit yourself gracefully (notice how this never happens in real
life?) with the rollback() function. It's simple, it's healthy, and the guy who
manages to apply it to human behaviour will make a gazillion.
/ 57 
