Just finished writing this today. The techniques in it may be old hat to some of you, but the concepts of using classes for the result set and result rows, using Exceptions at all, and implementing SPL::ArrayIterator - are all new to me. This isn't in production yet, so I'd really welcome any input you may have about how to improve these classes.
A total of 3 classes:
- DBE: Basically abstracts everything to do with the database, that doesn't deal with the results of the query. [ I realize the way the __construct() function gets it's connection parameters is a little...cludgy...but I am dealing with an embedded system somewhat & this is how I have to do it. ]
- DBEResult: The result set & provides access to all data (that I think I'll need) with regards to the result set
- DBERecord: A particular record
DBE:
Code: Select all
class DBE
{
private $mysqli;
#####
# Function: __construct()
# Purpose: To create the database connection
# Parameters: $username [string] - the username to connect with. Defaults to boolean FALSE
# $password [string] - the password to connect with. Defaults to boolean FALSE
# $db [string] - the database to connect to. Defaults to boolean FALSE
# $host [string] - the host to connect to. Defaults to boolean FALSE
#
# If $username,$password, & $db are all not specified, credentials are pulled from $SESSION_GLOBALS
##
function __construct($username=FALSE,$password=FALSE,$db=FALSE,$host='localhost')
{
#if credentials were passed, check them
if($username || $password || $db)
{
if(!$username)
throw new Exception('No username provided');
if(!$password)
throw new Exception('No password provided');
if(!$db)
throw new Exception('No database name provided');
}
# otherwise, if the webapp is defined, import the credentials from $SESSION_GLOBALS
else if(defined('CALLING_WEBAPP_NAME'))
{
global $SESSION_GLOBALS;
$host = (isset($SESSION_GLOBALS[CALLING_WEBAPP_NAME]['host'])) ? $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['host'] : 'localhost';
$username = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['user'];
$password = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['password'];
$db = $SESSION_GLOBALS[CALLING_WEBAPP_NAME]['db'];
}
# otherwise we have a problem
else
throw new Exception('Database credentials not provided');
# Try to connect
$this->mysqli = new mysqli($host,$username,$password,$db);
if($this->mysqli->connect_error)
throw new Exception('MySQLi connection error: '.$this->mysqli->connect_error);
}
#####
# Function: __get()
# Purpose: to be a magic getter for variables that aren't public
#
# Note: only exists if someone wants to access self::mysqli
##
function __get($value)
{
if($value == 'mysqli')
return $this->mysqli;
return FALSE;
}
#####
# Function: getInstance()
# Purpose: A Simpleton instantiater - used to get the current database connection.
# Allows for many calls to DBE::getInstance() without making multiple database connections
# Parameters: same as __construct()
##
function getInstance($username=FALSE,$password=FALSE,$db=FALSE,$host='localhost')
{
static $DBEinstance;
if(!isset($DBEinstance))
$DBEinstance = new DBE($username,$password,$db,$host);
return $DBEinstance;
}
#####
# Function: execute()
# Purpose: To execute the query
# Parameters: $query (string) - the query to execute
# $purpose [string] - what this query does.
# $display_query [boolean] - whether or not to display the query if an error occurs
##
function execute($query,$purpose='',$display_query=FALSE)
{
$result = $this->mysqli->query($query);
if($result === FALSE)
{
$error = $this->mysqli->error;
$errno = $this->mysqli->errno;
$msg = <<<ERROR
Database error while "$purpose"<br />
Database message: $error<br />
Database error number: $errno
ERROR;
$msg .= ($display_query) ? "<br />Query: $query" : '';
throw new Exception($msg);
}
else
return new DBEResult($result);
}
}Code: Select all
class DBEResult extends ArrayIterator
{
private $mysqli;
private $resultset;
private $affected_rows;
private $index = 0;
private $rows = array();
#####
# Function: __construct()
# Purpose: To instantiate this object
# Parameters: $result - the result returned from DBE::execute(). Must be boolean TRUE or a MySQLi_Result object
##
function __construct($result)
{
$this->resultset = $result;
$mysqli = DBE::getInstance()->mysqli;
# Set this immediately rather than pulling from $this->mysqli on demand, as
# $this->mysqli is singleton, so it could report a number for a different
# query than the one this resultset is for
$this->affected_rows = $mysqli->affected_rows;
}
#####
# Function: __get()
# Purpose: To return datum about the result set
##
function __get($value)
{
switch($value)
{
case 'affected_rows':
return $this->affected_rows;
break;
case 'num_rows':
return $this->resultset->num_rows;
break;
default:
throw new Exception('Property does not exist');
break;
}
}
#####
# Function: current()
# Purpose: To return the DBERecord object representing the row at the current index
#
# Implements a cache of sort so only one object per row is generated
##
function current()
{
if(!isset($this->rows[$this->index]))
$this->rows[$this->index] = new DBERecord($this->resultset->fetch_assoc());
return $this->rows[$this->index];
}
#####
# Function: key()
# Purpose: To return the current index
##
function key()
{
return $this->index;
}
#####
# Function: next()
# Purpose: To increment the current index
##
function next()
{
$this->index++;
}
#####
# Function: rewind()
# Purpose: To reset the index to 0
##
function rewind()
{
$this->index = 0;
}
#####
# Function: seek()
# Purpose: To move the index to a particular value
# Note: Throws an exception if the requested index is outside the size of the resultset
##
function seek($new_index)
{
if($new_index >= 0 && $new_index < $this->resultset->num_rows)
$this->index = $new_index;
else
throw new Exception('Seek index out of bounds');
}
#####
# Function: valid()
# Purpose: To see if there is an actual row for the current index
##
function valid()
{
if(is_object($this->resultset))
if($this->index >= 0 && $this->index < $this->resultset->num_rows)
return TRUE;
return FALSE;
}
}Code: Select all
class DBERecord
{
private $data = array();
function __construct($row_array)
{
$this->data = $row_array;
}
#####
# Function: __get()
# Purpose: To be a magic getter for fields in $data
#
# This function allows users to reference columns in the result set as $Record->resultsetcolumn,
# rather than $Record->data['resultsetcolumn']
##
function __get($name)
{
if(isset($this->data[$name]))
return $this->data[$name];
else
throw new Exception('Unknown field name');
}
}USAGE
Of particular interest is how the result set can be iterated like an array (an idea recently shared by ~Chris Corbyn), and how the columns in DBERecord can be accessed like class variables.
Code: Select all
<?PHP
require 'dbe.php';
try{
$DB = DBE::getInstance();
}
catch(Exception $e){
echo $e->getMessage();
}
$query = <<<SQL
SELECT
assigned_to,
author,
status,
priority,
on_behalf
contents
FROM
challenge_contents as cc,
challenge_properties as cp
WHERE
cp.challenge_id = cc.challenge_id
ORDER BY
cp.challenge_id DESC
LIMIT 10
SQL;
try{
$Result = $DB->execute($query);
echo $Result->num_rows;
foreach($Result as $Row)
{
echo $Row->author.'<br />';
}
}
catch(Exception $e){
echo $e->getMessage();
}
?>