PDO Wrapper
The Resource Model of Web Design says that certain elements of a Web Program (such as the database connection and notification object are resources that should be available throughout the script used to make the web page.
PHP's object model limits the scope of object; so programmers have been going through contortions such to keep from opening unnecessary database connections.
In this contortion, I decided to wrap the PDO object in a procedure with a flow control variable. I do this to make the point that, just as one can wrap up procedures into an object, one can encapsulate an object in a procedure.
Now, I admit, I wrote this program as a thought experiment. But it turns out I really like the way programs look with this procedure.
I call the procedured dbMain(). The first parameter is a flow control variable that determines what the program does and returns. The second parameter is SQL statement that uses the ? for variables. The third parameter is an array of variables. The fourth and fifth variables are success and failure messages.
Code using dbMain() might look like:
// DB_VAL returns a single value.
$sql='SELECT name FROM Test_Table WHERE id = ?';
$testName = dmMain(DB_VAL,$sql,$sql,[3]);
// DB_ROW returns an entire row, which I parse with list()
$sql = 'SELECT city, region FROM Address WHERE id = ?';
list($addrCity, $addrRegion) = dbMain(DB_ROW,$sql,[3]);
// DB_STMT return a PDO_Statement that I can use in loops:
$sql = 'SELECT id, name FROM Test_Table');
$stmt = dbMain(DB_STMT,$sql);
if ($stmt) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
// do something;
}
}
// You can execute commands.
// DB_TRANS starts a transaction.
dbMain(DB_TRANS);
// DB_ID inserts a row and returns the autoincrement value:
// It can report a success message or failure message.
$sql= 'INSERT INTO Test_Table (id, name) VALUES (?, ?)';
newId = dbMain(DB_ID,$sql,'Added New User','Add new user failed');
The dbMain() encapsulates the complex syntax of the PDO Object. This helps streamline my code and makes it easier to debug. I simply have to remember the descriptive options for the flow control variable.
For this article, I created a flow control for all of common paths through the PDO object.
This program uses the msg notification program to report errors.
WARNING: (2015-12-13) I am testing and making major modifications to this program as I write. I present the function below to show you the direction I am taking.
const DB_VAL = 1; // fetches row & returns a single value.
const DB_ROW = 2; // fetches & returns a single numeric indexed array
const DB_ALL = 3; // fetches & returns all in a numeric array.
const DB_ASSOC = 4; // fetches & returns a single associative array.
const DB_ALL_ARRAY = 5; // fetches & returns all in an associative array.
const DB_STMT = 6; // fetches & returns a PDOStatment
const DB_EXEC = 7; // execute & return a true or false on success or failure
const DB_ID = 8; // execute & return the generated id
const DB_CNT = 9; // execute & return the affected row count.
const DB_OVERRIDE=10; // executes a command, ignoring errors
const DB_TRANS =11; // start a transaction
const DB_COMMIT =12; // commits a transaction
const DB_ROLLBACK=13; // rollsback transaction
const DB_CLOSE=14; // close the connection & return count.
function dbMain($fc, $sql, $arr=null,$successMsg = '', $failureMsg = '') {
static $dbh=null; // variable will hold the PDO object.
static $dbName = 'main';
static $dbCnt = 0; // increments each time function is called.
static $dbErrors = false; // set this if there is a database error.
static $inTrans = false; // you are in a transaction!
$rv = null;
$stmt = null;
$dbCnt++; // Counts the calls to this function.
// we set the database handler on the first call.
// When $dbErrors is set go straight to final error handler.
if ($dbErrors) throw new Exception('Database Errors');
try {
if ($dbh == null and $fc != DB_CLOSE and !$dbErrors) {
try {
$dbh = new PDO('sqlite:/var/www/db/main.db');
// $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
// record error and set DB_ACTIVE to DB_NONE.
$dbErrors = true;
file_put_contents(SDB_PATH.'pdoerr.txt', $dbName.' //'.$_SERVER['REQUEST_TIME'].' '.$e->getMessage(), FILE_APPEND | LOCK_EX);
throw new Exception('DB connection failed: '.$e->getMessage());
}
}
// This is the main program.
// $fc determines what happens and what gets returned.
if ($fc == DB_TRANS) {
$dbh->beginTransaction();
$inTrans = true;
} elseif ($fc == DB_COMMIT) {
if ($inTrans) {
$dbh->commit();
$inTrans = false;
} else {
msgNote(MSG_COMMENT,'You are not in a transaction.');
}
} elseif ($fc == DB_ROLLBACK) {
$dbh->rollBack();
$inTrans = false;
} elseif ($fc == DB_CLOSE) {
$dbh = null;
$rv = $dbCnt-1;
} elseif (!msgNote(MSG_ISOK) && ($fc == DB_EXEC or $fc== DB_CNT or $fc==DB_ID)) {
// Do not run any data modification commands if there is an error
// You can run the query commands.
$rv = 0;
} elseif($fc >= DB_EXEC or $fc <= DB_OVERRIDE) {
// we attempt to prepare and execute the SQL command.
try {
$stmt = $dbh->prepare($sql);
if ($stmt===false) {
msgNote(MSG_COMMENT,$sql);
throw new Exception('SQL Error');
}
$stmt->execute($arr);
if ($successMsg != '') msgNote(MSG_OK,$successMsg);
if ($fc == DB_VAL) {
$row = $stmt->fetch(PDO::FETCH_NUM);
$rv = $row[0];
} elseif ($fc == DB_ROW) {
$rv = $stmt->fetch(PDO::FETCH_NUM);
} elseif ($fc==DB_ARRAY) {
$rv = $stmt->fetch(PDO::FETCH_ASSOC);
} elseif ($fc==DB_ALL) {
$rv = $stmt->fetchAll(PDO::FETCH_NUM);
} elseif ($fc==DB_ALL_ASSOC) {
$rv = $stmt->fetchAll(PDO::FETCH_ASSOC);
} elseif ($fc==DB_STMT) {
$rv = &$stmt;
} elseif ($fc==DB_CNT) {
$rv = $stmt->rowCount();
} elseif ($fc==DB_ID) {
// the last insert id should be in the PDO object.
$rv = $dbh->lastInsertId ();
}
} catch(PDOException $e) {
if ($inTrans) $dbh->rollback();
if ($failureMsg != '') msgNote(MSG_ERROR,$failureMsg);
throw new Exception('SQL Error: '.$e->getCode().' '.$e->getMessage());
}
} else {
// only happens in programming mistake
msgNote(MSG_ERROR,'Invalid dbQuery action. '.$fc.' on db call <br />'.$dbCnt.' '.$sql);
}
} catch (Exception $e) {
msgNote(MSG_ERROR,$e->getMessage());
// create appropriate rv value for request type.
if ($fc >= DB_ROW and $fc <= DB_ALL_ARRAY) {
$rv = [null,0,0,0,0,0,0,0,0,0,0,0];
} elseif ($fc == DB_STMT) {
$rv = false;
}
}
return $rv;
}
I started using variations of this code on my production servers earlier this year in PHP5 set ups. I like the code because it is fast and the program flow from the code is extremely clear.
The program has a dependency on the notification object msg, which is one of the programs I consider to be a valuale resource. Please leave comments on my Disqus page.