<?php
/**
* @name MYSQL Database Abstraction Layer
* @author Joey Adams
* @see http://bin.joeyadams.net/mysql_ab_pro/
* @version 4.0
* @uses
* Use this Abstraction layer to keep your business logic seperate.
*
* Change Options Below for either mysql or mysqli drivers.
*
* By Default query() returns Associative array, change options to return resource, or object.
*
* Note: On error, function db::error($str) is called, which just die()'s with error string.
* Modify this to log errors, or turn off for silence, or whatever you like. Change Error in
* config for silence.
*
* Free to use and modify to your liking, just keep this comment block here.
*
* Make sure you run your vars through prepare() before using them in query() to protect
* against SQL Injection.
*
*
* When performing query() , it accepts 2 arguments. query($string,$return_type);
* String is the query string. Return type is one of the below;
* ASSOS = Associative Array
* OBJ = Return Object
* RES = return resource
*
* If no type is given, it will return type per config.
*
*
* @copyright GPL (http://www.gnu.org/licenses/gpl.txt)
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>
*
*/
class DB {
// Options
/**
* Set MYSQL Driver
* 0 = MYSQL
* 1 = MYSQLI
*
* @var int driver
*/
private $driver = 1;
/**
* Default Query Results Return Style
*
* 0 = Return Resource
* 1 = Return Associative Array
* 2 = Return Object
*
* @var int def_return_style
*/
private $def_return_style = 1;
/**
* Log File to store Error info
* Make sure this file is readable/writeable
*
* @var str log_file
*/
private $log_file = "mysql_pro_error.log";
/**
* Log Erros
* 0 = Do not log errors
* 1 = Log Errors
*
* Logs errors to log_file above
*
*/
private $log_errors = 0;
/**
* Set whether to die() with error string
*
* 0 = Do not display, silent mode.
* 1 = die() with errors.
*
* Note: Change db::error() to change how
* errors are handled
*
* @var unknown_type
*/
private $display_errors = 0;
//-- End Options --
// Connection Info
/**
* MYSQL Server Host
* @var str host
*/
private $host = "host";
/**
* MYSQL Username
* @var str username
*/
private $username = "username";
/**
* MYSQL Password
* @var str password
*/
private $password = "password";
/**
* MYSQL Default Database
* Set this to default db to use, although you can
* pass a db name to getInstance() to use different one
*
* @var str def_db
*/
private $def_db = "test";
//-- End Connection Info --
/**
*
*
* Edit Below This line at your own risk
*
*
*/
/**
* Variable holds plugin for iObject
*
* @var str class_name
*/
private $iobj;
/**
* Variables holds plugin for iRow
*
* @var unknown_type
*/
private $irowobj;
/**
* MYSQL Connection Link
* Holds Link for connection for functionality
*
* @var static MYSQL_CONNECTION
*/
private static $_link;
/**
* Array that stores connections
* made with create()
*
* @var array
*/
private $_link_array = array();
/**
* Establish MYSQL Connection
* with either mysql, or mysqli driver
* based on config.
*
* Accepts 1 param for database name,
* If null, uses self::def_db
*
* @param str $db
*/
public function __construct($db = null){
if(!is_null($db)){
$this->def_db = $db;
}
if($this->driver){
$this->_link = new mysqli($this->host,$this->username,$this->password,$this->def_db);
if(mysqli_connect_errno()){
$this->error(mysqli_connect_error());
}
} else {
$this->_link = mysql_connect($this->host,$this->username,$this->password);
mysql_select_db($this->def_db,$this->_link);
if(mysql_errno()){
$this->error(mysql_error());
}
}
$this->_link_array['def'] = $this->_link;
}
/**
* Closes MYSQL Connection
*
*/
public function __destruct(){
if($this->driver){
$this->_link->close();
} else {
mysql_close($this->_link);
}
}
/**
* Add extended class plugin. Extends iObject return style.
*
* @param string $class_name
*/
public function iobjectPlugin($class_name){
if(class_exists($class_name) && array_key_exists('iobject',class_parents($class_name))){
$this->iobj = $class_name;
}
}
/**
* Add extended class plugin. Extends iRow for iObject return style.
*
* @param string $class_name
*/
public function irowPlugin($class_name){
if(class_exists($class_name) && array_key_exists('irow',class_parents($class_name))){
$this->irowobj = $class_name;
}
}
/**
* Called when error occurs
*
* @param str $error
*/
public function error($error){
if($this->log_errors){
if(!$fp = fopen($this->log_file,'a')){
if($this->display_errors){
die("Error Handler: Could not open file for writing, ".$this->log_file);
}
}
$date = date(DATE_RFC822);
$err = $date . " - " . $error . "\n";
if(!fwrite($fp,$err)){
if($this->display_errors){
die("Error Handler:Could Not Write To File");
}
}
fclose($fp);
}
if($this->display_errors){
die($error);
}
}
/**
* Returns instance of MYSQL
*
* Use this to init DB(), or to
* get an instance of the connection.
*
* Makes sure only 1 persistent connection
* is maintained.
*
* Pass a string when initializing if you want
* to use a different database than in config.
*
* @param str $db
* @return MYSQL_CONNECTION
*/
public function getInstance($db = null){
if(is_null(self::$_link)){
self::$_link = new DB($db);
}
return self::$_link;
}
/**
* Wrapper for Real_Escape_String
*
* Accepts either array, or string
* and returns escaped vars.
*
* Use this before query() on vars to
* protect from sql injection
*
* @param str,array $var
* @return str,array $clean
*/
public function prepare($var){
if(is_array($var)){
if($this->driver){
foreach($var as $key=>$val){
$clean[$key] = $this->_link->real_escape_string($val);
}
} else {
foreach($var as $key=>$val){
$clean[$key] = mysql_real_escape_string($val,$this->_link);
}
}
} else {
if($this->driver){
$clean = $this->_link->real_escape_string($var);
} else {
$clean = mysql_real_escape_string($var,$this->_link);
}
}
return $clean;
}
/**
* Performs Query Against Database
*
* Accepts two args, the query string,
* and an optional return style.
*
* Return Style Can be
* 'OBJ' or 'OBJECT' = Return Object
* 'RES' or 'RESOURCE' = Return Resource
* 'ASSOC' or 'ASSOCIATIVE' = Return Associative Array
*
* Note:: If return_style is omitted, return will be determined
* by config above.
*
* @param str $query
* @return MYSQL_RESULT,ASSOC_ARRAY
*/
public function query($query,$return_style = null){
$return_style = $this->_getReturnStyle($return_style);
if($this->driver){
if(!$resultSet = $this->_link->query($query)){
$this->error(mysqli_error($this->_link));
}
if($return_style){
if($return_style == 1){
$returnResult['affected_rows'] = $this->_link->affected_rows;
if(is_object($resultSet)){
$returnResult['num_rows'] = $resultSet->num_rows;
if($resultSet->num_rows){
$i=0;
while($result = $resultSet->fetch_assoc()){
foreach($result as $key=>$val){
$returnResult[$i][$key] = $val;
}
$i++;
}
}
}
} elseif ($return_style == 2){
if(!is_null($this->iobj)){
$i_obj = $this->iobj;
} else {
$i_obj = "iobject";
}
$iobject = new $i_obj($this->irowobj);
$iobject->query = $query;
$iobject->affected_rows = $this->_link->affected_rows;
if(is_object($resultSet)){
$iobject->num_rows = $resultSet->num_rows;
if($resultSet->num_rows){
while($result = $resultSet->fetch_assoc()){
$row = $iobject->newRow();
foreach($result as $key=>$val){
$row->$key = $val;
}
}
}
}
$returnResult = &$iobject;
}
} else {
$returnResult = $resultSet;
}
} else {
if(!$resultSet = mysql_query($query,$this->_link)){
$this->error(mysql_error());
}
if($return_style){
if($return_style == 1){
$returnResult['affected_rows'] = mysql_affected_rows();
if(is_resource($resultSet)){
$returnResult['num_rows'] = mysql_num_rows($resultSet);
if($returnResult['num_rows']){
$i=0;
while($result = mysql_fetch_assoc($resultSet)){
foreach($result as $key=>$val){
$returnResult[$i][$key] = $val;
}
$i++;
}
}
}
} elseif ($return_style == 2){
if(!is_null($this->iobj)){
$i_obj = $this->iobj;
} else {
$i_obj = "iobject";
}
$iobject = new $i_obj($this->irowobj);
$iobject->query = $query;
$iobject->affected_rows = mysql_affected_rows();
if(is_resource($resultSet)){
$iobject->num_rows = mysql_num_rows($resultSet);
if($iobject->num_rows){
while($result = mysql_fetch_assoc($resultSet)){
$row = $iobject->newRow();
foreach($result as $key=>$val){
$row->$key = $val;
}
}
}
}
$returnResult = &$iobject;
}
} else {
$returnResult = $resultSet;
}
}
return $returnResult;
}
/**
* Obtains the return style for query
*
* @param str $style
* @return str $style
*/
public function _getReturnStyle($style){
if(!is_null($style)){
$style = strtolower($style);
if($style == 'obj' || $style == 'object'){
$return_style = 2;
} elseif ($style == 'res' || $style == 'resource'){
$return_style = 0;
} elseif ($style == 'assoc' || $style == 'associative'){
$return_style = 1;
}
} else {
$return_style = $this->def_return_style;
}
return $return_style;
}
/**
* Create A New DB Connection
*
* This connection will use adapter in config and
* be stored in an array, you define the name for the
* connection to be identified by the argument "$name".
* You can retrieve this connection through $db->open('name');
*
* @param str $name
* @param str $host
* @param str $username
* @param str $password
* @param str $db
*/
public function create($name,$host,$username,$password,$db){
if($this->driver){
$this->_link_array[$name] = new mysqli($host,$username,$password,$db);
if(mysqli_connect_errno()){
$this->error(mysqli_connect_error());
}
} else {
$this->_link_array[$name] = mysql_connect($host,$username,$password);
mysql_select_db($db,$this->_link_array[$name]);
if(mysql_errno()){
$this->error(mysql_error());
}
}
}
/**
* Open stored DB connection
*
* Open connection stored using create(),
* by name. If no name, or no connection was found
* by the name given, will restore default connection.
*
* @param str $name
* @return instance DB
*/
public function open($name = null) {
if(!is_null($name) && array_key_exists($name,$this->_link_array)){
$rtn = $this->_link_array[$name];
} else {
$rtn = $this->_link_array['def'];
}
$this->_link = $rtn;
}
}
/**
* Result Object Class for returning
* objects on query()
*
* Extend iobject to add functionality
* to result object.
*
*/
class iobject{
public $affected_rows;
public $num_rows;
public $rows;
public $irowobj;
public $query;
/**
* Constructor, sets irow object plugin if available
*
* @param unknown_type $irowobj
*/
public function __construct($irowobj=null){
if(!is_null($irowobj)){
$this->irowobj = $irowobj;
} else {
$this->irowobj = 'irow';
}
}
/**
* Fetch row returns row where var = val
* Returns first row matched as iRow object.
* To return an array of all rows matched, pass 1 to
* return_array
*
* @param str $var
* @param str $val
* @param int $return_array null
* @return iRow obj or Array
*/
public function fetchRow($var,$val,$return_array=null){
if(is_array($this->rows)){
foreach($this->rows as $row){
if($row->$var && $row->$var == $val){
if($return_array){
$result[] = $row;
} else {
return $row;
}
}
}
}
return $result;
}
/**
* Creates new iRow Object
*
* @return iRow Obj
*/
public function newRow(){
$row = new $this->irowobj();
$this->rows[] = &$row;
return $row;
}
/**
* Output results as XML data
*
* @return xml output
*/
public function asXML(){
$xml_output = "<?xml version=\"1.0\"?>\n";
$xml_output .= "<results>\n";
foreach($this->rows as $row){
$xml_output .= "\t<row>\n";
foreach($row as $var=>$val){
$val = str_replace("&", "&", $val);
$val = str_replace("<", "<", $val);
$val = str_replace(">", ">", $val);
$val = str_replace("\"", """, $val);
$xml_output .= "\t\t<{$var}>" . $row->escape($var) . "</{$var}>\n";
}
$xml_output .= "\t</row>\n";
}
$xml_output .= "</results>";
return $xml_output;
}
/**
* Returns HTML Formatted Document
* Containing Result Data
* Great for testing, or reports
*
* @return str HTML
*/
public function asHTML(){
$i=0;
$html = "<html>\n";
$html .="<head>\n<title>Query Results</title>\n</head>\n<body>\n";
$html .= "<h1 style='margin:0;padding:0;'>Query Results</h1>\n";
$html .= "<table border='1'>\n<tr>\n<td colspan='2'><b>Query String:</b> ". $this->query . "</td>\n</tr>\n";
$html .= "<tr><td><b>Num Rows:</b> ".$this->num_rows."</td><td><b>Affected Rows:</b> ".$this->affected_rows."</td></tr>";
$html .= "<tr>\n<td colspan='2'>\n";
$html .= "<table border='1'>\n";
$html .="\t<th><b>#</b></th>";
foreach($this->rows[0] as $key=>$val){
$html .= "<th>".htmlspecialchars($key)."</th>";
}
$html .="\n";
foreach($this->rows as $row){
$bgcolor = ($i % 2) ? '#ccc' : '#fff';
$html .= "\t<tr style='background:$bgcolor'>\n";
$html .= "\t\t<td><b>$i</b></td>\n";
foreach ($row as $var=>$val){
$html .= "\t\t<td>".$row->escape($var)."</td>\n";
}
$html .= "\t</tr>\n";
$i++;
}
$html .= "</table>\n";
$html .= "</td>\n</tr>\n</table>\n";
$html .= "</body>\n</html>";
return $html;
}
}
/**
* Row Object
* Holds rows returned by query()
* with return as object
*
* Extend irow to add functionality
* to row objects.
*
*/
class irow{
/**
* Return sanitized string.
*
* ex. if want to echo $row->user
* echo $row->escape('user');
*
* @param str $var
* @return str safe var
*/
public function escape($var){
return htmlspecialchars($this->$var);
}
}