Location: PHPKode > scripts > MySQL Abstraction Pro > mysql-abstraction-pro/database.class.php
<?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(">", "&gt;", $val);
                $val = str_replace("\"", "&quot;", $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);
    }
}
Return current item: MySQL Abstraction Pro