MySQL Database Manager v1.0

<?php

define('dbServer',   'localhost');
define('dbUser',     'user');
define('dbPassword', 'password');
define('dbDatabase', 'database');
define('logFileSql',  '/absolute/path/to/log_file.txt'); // Give write permissions

require_once('class.MySQLManager.php');

/* Select Record */
$db = new MySQLManager();
$db->query("SELECT * FROM table");

echo $db->numRows();

while ($data = $db->nextRecord('array', MYSQL_ASSOC) )
{
    echo '<pre>';
    var_dump($data);
    echo '</pre>';
}

/* Insert Record*/
$db2 = new MySQLManager();
$db2->insert('table', array('field1' => 'value1', 'field2' => "value2"), true);
echo $db3->insertId();

/* Update Record */
$db3 = new MySQLManager();
$db3->update('table', array('field1' => 'value1', 'field2' => 'value2'), array('field1' => 'condition1', 'field2' => 'condition2'), 'AND', true);

?>

<?php
/**
 * MySQLDatabase
 * 
 * Creates MySQL database connection and execute queries, inserts, updates, deletes etc.
 * 
 * Following constants need to be defined before including this class:
 * dbServer   - Database server adres (usually localhost)
 * dbUser     - Database user
 * dbPassword - Database password
 * dbDatabase - Database name
 * sqlLogFile - Path + filename (txt) of SQL queries log 
 * 
 * @since     10-11-2010
 */

class MySQLManager
{
    /**
     * Contains MySQL link identifier
     * @var mixed
     */
    private    $_connection;
    
    /**
     * Contains SQL result resource
     * @var mixed
     */
    private    $_result;
    
    /**
     * Contains debug info
     * @var mixed
     */
    private    $_caller;
    
    /**
     * Creates database connection
     * 
     * @param   string  $dbServer    Database server adres (usually localhost)
     * @param   string  $dbUser      Database user
     * @param   string  $dbPassword  Database password
     * @param   string  $dbDatabase  Database name
     * @return  void
     */
    function __construct($dbServer = null, $dbUser = null, $dbPassword = null, $dbDatabase = null)
    {
        $this->_caller = debug_backtrace();
        
        if ( empty($dbServer) )
        {
            $dbServer = dbServer;
        }
        if ( empty($dbUser) )
        {
            $dbUser = dbUser;
        }
        if ( empty($dbPassword) )
        {
            $dbPassword = dbPassword;
        }
        if ( empty($dbDatabase) )
        {
            $dbDatabase = dbDatabase;
        }
        
        $this->_connection = @mysql_connect($dbServer, $dbUser, $dbPassword);
        
        if (!$this->_connection)
        {
            $this->triggerError('Connection error in __construct()', 'ERROR');
        }
        
        $selectDb = @mysql_select_db($dbDatabase);
        
        if (!$selectDb)
        {
            $this->triggerError('Cannot select database in __construct()', 'ERROR');
        }
    }
    
    /**
     * Creates error messages with caller file and line unlike trigger_error() does
     * 
     * @param   string  $errorMessage  Error message
     * @param   string  $errorType     Type of error (ERROR / WARNING / NOTICE)
     * @return  void
     */
    private function triggerError($errorMessage, $errorType)
    {
        if ( !empty($errorMessage) && ( $errorType == 'ERROR' || $errorType == 'WARNING' || $errorType == 'NOTICE' ) )
        {
            $errorTitle = '';
            
            switch ($errorType)
            {
                case 'ERROR':
                    $errorTitle = 'Fatal error';
                break;
                case 'WARNING':
                    $errorTitle = 'Warning';
                break;
                case 'NOTICE':
                    $errorTitle = 'Notice';
                break;
            }
            
            echo '<br /><strong>'.$errorTitle.':</strong> Class MySQLDatabase - '.$errorMessage.' in <strong>'.$this->_caller[0]['file'].'</strong> on line <strong>'.$this->_caller[0]['line'].'</strong><br />';
            
            if ($errorType == 'ERROR')
            {
                exit();
            }
        }
        else
        {
            $this->triggerError('Invalid error type or empty error message for triggerError()','ERROR');
        }
    }
    
    /**
     * Executes any SQL query and log query in text file
     * 
     * @param   string   $sql    SQL query to execute
     * @param   boolean  $print  Print query
     * @return  boolean
     */
    public function query($sql, $print = false)
    {
        if ( !empty($sql) )
        {
            if ($print)
            {
                echo $sql.'<br />';
            }
            
            /* Log query in log-file */
            $currentLogFile  = file_get_contents(logFileSql);
            $currentLogFile .= "[".date('d-M-Y H:i:s')."] SQL query: ".$sql." in ".$this->_caller[0]['file']." on line ".$this->_caller[0]['line']."\n";
            
            file_put_contents(logFileSql, $currentLogFile);
            
            $this->_result = mysql_query($sql, $this->_connection);
            
            if (!$this->_result)
            {
                $this->triggerError('Cannot execute SQL query in query() ('.mysql_error($this->_connection).')', 'ERROR');
                
                return false;
            }
            else
            {
                return true;
            }
        }
        else
        {
            return false;
        }
    }
    
    /**
     * Free result memory
     * 
     * @return  void
     */
    public function freeResult()
    {
        $freeResult = @mysql_free_result ($this->_result);
        
        if (!$freeResult)
        {
            $this->triggerError('Invalid result resource for freeResult()', 'WARNING');
        }
    }
    
    /**
     * Returns an object with properties or an array that corresponds to the fetched row and moves the internal data pointer ahead
     * 
     * @param   string    $fetchType   The way to return a result row (object / array)
     * @param   constant  $resultType  The type of array that is to be fetched (MYSQL_ASSOC / MYSQL_NUM / MYSQL_BOTH - only when $fetchType is 'array')
     * @return  mixed
     */
    public function nextRecord($fetchType = 'object', $resultType = MYSQL_ASSOC)
    {
        if ( ($fetchType == 'array' || $fetchType == 'object') && ($resultType == MYSQL_ASSOC || $resultType == MYSQL_NUM || $resultType == MYSQL_BOTH) )
        {
            $data = '';
            
            switch ($fetchType)
            {
                case 'object':
                    $data = @mysql_fetch_object($this->_result);
                break;
                
                case 'array':
                    $data = @mysql_fetch_array($this->_result, $resultType);
                break;
            }
            
            return $data;
        }
        else
        {
            $this->triggerError('Invalid fetch type or result type for nextRecord()', 'ERROR');
            
            return false;
        }
    }
    
    /**
     * Returns the number of rows from a result set of SELECT or SHOW
     * To retrieve the number of rows from a INSERT, UPDATE, REPLACE or DELETE use affected_rows()
     * 
     * @return  integer
     */
    public function numRows()
    {
        $num_rows = @mysql_numrows($this->_result);
        
        if (!$num_rows)
        {
            $this->triggerError('Invalid result resource for numRows()', 'WARNING');
        }
        else
        {
            return $num_rows;
        }
    }
    
    /**
     * Returns the number of affected rows by the last INSERT, UPDATE, REPLACE or DELETE
     * 
     * @return  integer
     */
    public function affectedRows()
    {
        $affected_rows = @mysql_affected_rows($this->_connection);
        
        if ($affected_rows == -1)
        {
            $this->triggerError('Invalid resource link identifier for affectedRows()', 'WARNING');
        }
        else
        {
            return $affected_rows;
        }
    }
    
    /**
     * Executes INSERT query
     * 
     * @param   string   $table  Database table
     * @param   array    $data   Array with fieldname as key and data as value
     * @param   boolean  $print  Print query
     * @return  boolean
     */
    public function insert($table, $data, $print = false)
    {
        if ( !empty($table) && is_array($data))
        {
            $fields = array();
            $values = array();
            
            foreach ($data as $fieldName => $value)
            {
                $fields[] = mysql_real_escape_string($fieldName);
                $values[] = mysql_real_escape_string($value);
            }
            
            $sql = "INSERT INTO `".mysql_real_escape_string($table)."` (`".implode("`,`", $fields)."`) VALUES ('".implode("','", $values)."');";
            
            $this->query($sql, $print);
            
            return true;
        }
        else
        {
            $this->triggerError('Invalid data array or table for insert()', 'ERROR');
        
            return false;
        }
    }
    
    /**
     * Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT)
     * 
     * @return  mixed
     */
    public function insertId() {
        $insertId = @mysql_insert_id($this->_connection);
        
        if (!$insertId)
        {
            $this->triggerError('Invalid resource link identifier insertId()', 'WARNING');
        
            return false;
        }
        else
        {
            return $insertId;
        }
    }
    
    /**
     * Updates a record based on conditions
     * 
     * @param   string   $table       Database table
     * @param   array    $data        Array with fieldname as key and data as value
     * @param   array    $conditions  Array with fieldname as key and condition as value
     * @param   string   $operator    SQL operator (AND / OR)
     * @param   boolean  $print       Print query
     * @return  boolean
     */
    public function update($table, $data, $conditions, $operator, $print = false)
    {
        if ( !empty($table) && is_array($data) && is_array($conditions) && ($operator == 'AND' || $operator == 'OR' ) )
        {
            $tempData = array();
            
            foreach ($data as $fieldName => $value)
            {
                $tempData[] = mysql_real_escape_string($fieldName)."` = '".mysql_real_escape_string($value);
            }
            
            $tempConditions = array();
            
            foreach ($conditions as $fieldName => $value)
            {
                $tempConditions[] = mysql_real_escape_string($fieldName)."` = '".mysql_real_escape_string($value);
            }

            $sql = "UPDATE `".mysql_real_escape_string($table)."` SET `".implode("', `", $tempData)."' WHERE `".implode("' ".$operator." `", $tempConditions)."';";
            
            $this->query($sql, $print);
            
            return true;
        }
        else
        {
            $this->triggerError('Invalid table, data array, conditions array or operator for update()', 'ERROR');
            
            return false;
        }
    }
    
    /**
     * Removes a record based on conditions
     * 
     * @param   string   $table       Database table
     * @param   array    $conditions  Array with field as key and condition as value
     * @param   string   $operator    SQL operator (AND / OR)
     * @param   boolean  $print       Print query
     * @return  boolean
     */
    public function delete($table, $conditions, $operator, $print = false)
    {
        if ( !empty($table) && is_array($conditions) && ($operator == 'AND' || $operator == 'OR' ) )
        {
            $tempConditions = array();
            
            foreach ($conditions as $fieldName => $value)
            {
                $tempConditions[] = mysql_real_escape_string($fieldName)."` = '".mysql_real_escape_string($value);
            }
            
            $sql = "DELETE FROM `".mysql_real_escape_string($table)."` WHERE `".implode("' ".$operator." `", $tempConditions)."';";
            
            $this->query($sql, $print);
            
            return true;
        }
        else
        {
            $this->triggerError('Invalid table or conditions array for delete()', 'ERROR');
        
            return false;
        }
    }
    
    /**
     * Checks if data exists in given table based on conditions
     * 
     * @param   string   $table       Database table
     * @param   string   $primaryKey  Primery key for selection
     * @param   array    $conditions  Array with field as key and condition as value
     * @param   string   $operator    SQL operator (AND / OR)
     * @param   boolean  $print       Print query
     * @return  boolean
     */
    public function dataExists($table, $primaryKey, $conditions, $operator, $print = false)
    {
        if ( !empty($table) && !empty($primaryKey) && is_array($conditions) && ($operator == 'AND' || $operator == 'OR' ) )
        {
            $tempConditions = array();
            
            foreach ($conditions as $fieldName => $value)
            {
                $tempConditions[] = mysql_real_escape_string($fieldName)."` = '".mysql_real_escape_string($value);
            }
            
            $sql = "SELECT `".mysql_real_escape_string($primaryKey)."` FROM `".mysql_real_escape_string($table)."` WHERE `".implode("' ".$operator." `", $tempConditions)."';";
            
            $this->query($sql, $print);
            
            if ($this->numRows() > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        else
        {
            $this->triggerError('Invalid table, primary key or conditions array for dataExists()', 'ERROR');
            
            return false;
        }
    }
    
    /**
     * Returns detailed information about the last query
     * 
     * @return  mixed
     */
    public function sqlInfo() {
        $sqlInfo = mysql_info($this->_connection);
        
        if (!$sqlInfo)
        {
            $this->triggerError('Invalid resource link identifier for sqlInfo()', 'WARNING');
        }
        else
        {
            return $sqlInfo;
        }
    }
    
    /**
     * Returns the current server status
     * 
     * @return  mixed
     */
    public function sqlStats() {
        $sqlStats = @mysql_stat($this->_connection);
        
        if ($sqlStats != NULL)
        {
            return $sqlStats;
        }
        else
        {
            $this->triggerError('Invalid resource link identifier for sqlStats()', 'WARNING');
        
            return false;
        }
    }
}
?>