'localhost', 'db' => 'averdo', 'user' => 'averdo', 'pass' => 'J2K6xZWNhNVByMoA96jYiC1J', ]; // hack: set database host according to ENVIRONMENT constant if ( defined('ENVIRONMENT') and( preg_match('/(?:dev|test)/', ENVIRONMENT) or( defined('ENVIRONMENT_TEST') and ENVIRONMENT == ENVIRONMENT_TEST ) ) ) { $var['host'] = '144.76.67.109'; } return $var; } // end function "db_vars" // ******************************************************** // ******************************************************** class database { protected $class_name = 'database'; protected $class_version = '1.2.1'; // handles for connection and result private $db_handle = false; private $db_result = false; // stats (last query) public $db_query = ''; public $db_command = ''; public $db_rows = 0; public $db_time = 0; public $db_unbuffered = false; // stats (total) public $db_total_queries = 0; public $db_total_time = 0; protected $db_default_result_type = 'assoc'; protected $db_db = null; protected $db_host = null; protected $db_user = null; protected $db_new = true; protected $db_error_echo = true; protected $db_error_route = false; protected $db_error_query = false; protected $db_error = array(); protected $db_debug = null; public $db_log_save = false; public $db_log_slow = 5; public $db_log_path = '.'; public $db_log_file = 'class.database.log'; public $db_log_note = ''; // ******************************************************** // ******************************************************** // method : __construct (constructor) // description : --- // parameter : (opt)(string)user, (opt)(bool)new-db-connection, (opt)(string)db // return : (void) public function __construct( $user = null, $new = true, $db = null ) { $this->connect( $user, $new, $db ); } // end method "__construct" // ******************************************************** // ******************************************************** // method : __descruct (destructor) // description : --- // parameter : (none) // return : (void) public function __descruct() { $this->close(); } // end method "__descruct" // ******************************************************** // ******************************************************** // method : _db_connect // description : --- // parameter : (opt)(int)attempts, (opt)(int)sleep // return : (bool) private function _db_connect( $attempts = 5, $sleep = 0 ) { // check: is current connection still alive? if( $this->db_handle and( $this->db_unbuffered // don't ping on unbuffered queries! or mysql_ping( $this->db_handle ) ) ) { return( true ); } // get: db-vars $var = db_vars( $this->db_user ); // connect if( ! $this->db_handle = @mysql_connect( $var[ 'host' ], $var[ 'user' ], $var[ 'pass' ], $this->db_new ) ) { // set: timeout (sleep so we have a chance the database comes back up) if( $sleep > 0 ) { sleep( $sleep ); } // check: tries left? reconnect again if( -- $attempts > 0 ) { return( $this->_db_connect( $attempts, $sleep > 0 ? $sleep : 20 ) ); } // set: error after all retries failed $this->_db_error( 'cannot connect to host: '.$var[ 'host' ] ); return( false ); } // set: db-name if (empty($this->db_db)) { $this->db_db = $var['db']; } // set: host and user (again) as they might have been overwritten in "db_vars" function $this->db_host = $var['host']; $this->db_user = $var['user']; // set: database to use if( ! @mysql_select_db( $this->db_db, $this->db_handle ) ) { $this->_db_error( 'cannot change to database: '.$this->db_db ); return( false ); } // set: default charset UTF-8 and locale // set: do not count above "charset queries" // set: do not debug these queries $old = $this->db_debug; $this->db_debug = false; $this->query( "SET NAMES utf8 COLLATE utf8_unicode_ci, CHARACTER_SET_SERVER = utf8, COLLATION_SERVER = utf8_unicode_ci, LC_TIME_NAMES = de_DE" ); $this->db_total_queries -= 1; $this->db_debug = $old; return( true ); } // end method "_db_connect" // ******************************************************** // ******************************************************** // method : _db_error // description : --- // parameter : (string)error // return : (void) private function _db_error( $error = null ) { // reset: error $this->db_error = array(); // set: manual error message and server message $this->db_error[ 'error' ] = trim( str_replace( '; check the manual that corresponds to your MySQL server version for the right syntax to use', '', ( $error ? $error.' / ' : '' ).( $this->db_handle ? mysql_error( $this->db_handle ) : mysql_error() ) )." [".date( 'd.m.Y H:i:s' )."]" ); // set: route if( $this->db_error_route ) { $this->db_error[ 'route' ] = join( ' => ', $this->_db_route() ); } // set: query if( $this->db_error_query ) { $this->db_error[ 'query' ] = $this->db_query; } if( $this->db_error_echo ) { trigger_error( $this->error_string(), E_USER_WARNING ); } // log: error $this->_db_log( 'error' ); } // end method "_db_error" // ******************************************************** // ******************************************************** // method : _db_log // description : --- // parameter : (string)mode // return : (bool) private function _db_log($mode) { // check: log or not if (!$this->db_log_save) { return false; } $file = $this->db_log_path . '/' . $this->db_log_file; // check: new file? $new = file_exists($file) ? false : true; // check: is file writable if (!$fh = @fopen($file, 'a')) { return false; } // save: query data to file if ($new) { fputs( $fh, join( "\t", array( 'log_date', 'log_time', 'log_type', 'log_note', 'class_name', 'class_version', 'query_runtime', 'query_string', ) ) . "\n" ); } fputs( $fh, join( "\t", array( date('Y-m-d'), date('H:i:s'), $mode, $this->db_log_note, $this->class_name, $this->class_version, number_format($this->db_time, 4, ',', '.'), $this->db_query, ) ) . "\n" ); fclose($fh); // modify: chmod for new file if ($new) { $old = umask(0); chmod($file, 0666); umask($old); } return true; } // end method "_db_log" // ******************************************************** // ******************************************************** // method : _db_route // description : --- // parameter : (none) // return : (array)route private function _db_route() { $trace = debug_backtrace(); $route = array(); for( $i = 1; $i < count( $trace ); $i ++ ) { $trace[ $i ][ 'file' ] = isset( $trace[ $i ][ 'file' ] ) ? preg_replace( '/.*\/([^\/]+)$/', '$1', $trace[ $i ][ 'file' ] ) : 'unknown'; $trace[ $i ][ 'line' ] = isset( $trace[ $i ][ 'line' ] ) ? intval( $trace[ $i ][ 'line' ] ) : 'unknown'; $route[] = ( ! empty( $trace[ $i ][ 'class' ] ) ? $trace[ $i ][ 'class' ].'::' : '' ).( ! empty( $trace[ $i ][ 'function' ] ) ? $trace[ $i ][ 'function' ].' ' : '' ).'[called from '.$trace[ $i ][ 'file' ].' at line '.$trace[ $i ][ 'line' ].']'; } return( $route ); } // end method "_db_route" // ******************************************************** // ******************************************************** // method : array_to_sql // description : parse array data into escaped sql string data // parameter : (array)data, (opt)(string)pattern, (opt)(string)glue, (opt)(array)option // return : (string)data public function array_to_sql($data, $pattern = "%s = '%s'", $glue = "AND", $option = array()) { if (!is_array($data)) { return false; } // set: defaults $option['translate'] = !empty($option['translate']); $option['trim'] = !empty($option['trim']); // sanatize: glue $glue = " " . trim($glue) . " "; $result = array(); foreach ($data as $key => $val) { if ($option['translate']) { $val = str_replace('*', '%', $val); $val = str_replace('?', '_', $val); } $key = trim($key); if ($option['trim']) { $val = trim($val); } $key = $this->escape($key); $val = $this->escape($val); $result[mb_strtolower($key)] = sprintf($pattern, $key, $val); } return join($glue, $result); } // end function "array_to_sql" // ******************************************************** // ******************************************************** // method : close // description : --- // parameter : (none) // return : (void) public function close() { @mysql_close( $this->db_handle ); unset( $this->db_handle, $this->db_result ); } // end method "close" // ******************************************************** // ******************************************************** // method : connect // description : --- // parameter : (opt)(string)user, (opt)(bool)new-db-connection, (opt)(string)db // return : (bool) public function connect( $user = null, $new = true, $db = null ) { // set: db-user if( isset( $user ) and is_string( $user ) ) { $this->db_user = $user; } // set: db-new $this->db_new = (bool)$new; // set: db-name if( isset( $db ) and is_string( $db ) ) { $this->db_db = $db; } return( true ); } // end method "connect" // ******************************************************** // ******************************************************** // method : count // description : --- // parameter : (string)table, (opt)(string)where, (opt)(string)column // return : (int)count public function count( $table, $where = 1, $column = '*' ) { if( ! $table ) { return( false ); } return( $this->get( "SELECT COUNT( ".$column." ) FROM ".$table." WHERE ".$where, "string" ) ); } // end method "count" // ******************************************************** // ******************************************************** // method : data // description : return next query result // parameter : (opt)(string)result-var-type // return : (mixed)query-result public function data( $type = null ) { // check: connection to database if( ! $this->_db_connect() ) { return false; } // check: (correct) query executed if( ! $this->db_result ) { $this->_db_error( 'no (correct) query' ); return( false ); } // get: next query result switch( mb_strtolower( empty( $type ) ? $this->db_default_result_type : $type ) ) { case 'assoc' : return @mysql_fetch_array( $this->db_result, MYSQL_ASSOC ); case 'both' : return @mysql_fetch_array( $this->db_result, MYSQL_BOTH ); case 'num' : return @mysql_fetch_array( $this->db_result, MYSQL_NUM ); case 'object' : return @mysql_fetch_object( $this->db_result ); case 'string' : return( ( $result = @mysql_fetch_array( $this->db_result, MYSQL_NUM ) ) ? $result[ 0 ] : false ); } } // end method "data" // ******************************************************** // ******************************************************** // method : data_all // description : --- // parameter : (opt)(string)result-var-type // return : (array)query-result public function data_all( $type = null ) { $result = array(); while( $row = $this->data( $type ) ) { $result[] = $row; } return( $result ); } // end method "data_all" // ******************************************************** // ******************************************************** // method : error // description : --- // parameter : (none) // return : (bool) public function error() { return( empty( $this->db_error ) ? false : true ); } // end method "error" // ******************************************************** // ******************************************************** // method : error_reset // description : --- // parameter : (none) // return : (void) public function error_reset() { $this->db_error = array(); } // end method "error_reset" // ******************************************************** // ******************************************************** // method : error_string // description : --- // parameter : (opt)(string)separator // return : (string)error public function error_string( $separator = "
\n" ) { if( empty( $this->db_error ) ) { return( '' ); } $buffer = ''; foreach( $this->db_error as $key => $value ) { $buffer .= mb_strtoupper( $key ).': '.$value.$separator; } return( $buffer ); } // end method "error_string" // ******************************************************** // ******************************************************** // method : escape // description : escapes strings to prevent "sql injection attacks" // parameter : (string)unescaped // return : (string)escaped public function escape( $var ) { if( ! $this->_db_connect() ) { return( @mysql_real_escape_string( $var ) ); } return( mysql_real_escape_string( $var, $this->db_handle ) ); } // end method "escape" // ******************************************************** // ******************************************************** // method : export // description : return "head" (column names) and "data" (query result) as wanted "type" // parameter : (opt)(string)type, (opt)(array)option // return : (mixed)result public function export( $type = 'array', $option = array() ) { $result = array(); if( $this->rows() and in_array($this->db_command, array('select', 'show', 'execute')) ) { // set: options $option[ 'head' ] = isset( $option[ 'head' ] ) ? (bool)$option[ 'head' ] : true; $option[ 'separator' ] = isset( $option[ 'separator' ] ) ? trim( $option[ 'separator' ] ) : ( $type == 'txt' ? "\t" : ";" ); $option[ 'enclose' ] = isset( $option[ 'enclose' ] ) ? $option[ 'enclose' ] : '"'; // get: column names (head) if( $option[ 'head' ] ) { $result[] = $this->head(); } // get: column values/rows (data) while( $row = $this->data( 'assoc' ) ) { $result[] = $row; } if( $type == 'csv' or $type == 'txt' ) { // generate: csv|txt $csv = ''; foreach( $result as $row ) { if( ! empty( $option[ 'enclose' ] ) ) { foreach( $row as &$value ) { $value = str_replace( $option[ 'enclose' ], $option[ 'enclose' ].$option[ 'enclose' ], $value ); } } $csv .= $option[ 'enclose' ].join( $option[ 'enclose' ].$option[ 'separator' ].$option[ 'enclose' ], $row ).$option[ 'enclose' ]."\n"; } return( $csv ); } } return( $result ); } // end method "export" // ******************************************************** // ******************************************************** // method : get // description : --- // parameter : (opt)(string)query, (opt)(string)result-var-type // return : (mixed)query-result public function get( $sql = null, $type = null ) { if( ! empty( $sql ) and ! $this->query( $sql ) ) { return( false ); } return( $this->data( $type ) ); } // end method "get" // ******************************************************** // ******************************************************** // method : get_all // description : --- // parameter : (opt)(string)query, (opt)(string)result-var-type // return : (array)query-result public function get_all( $sql = null, $type = null ) { if( ! empty( $sql ) and ! $this->query( $sql ) ) { return( false ); } return( $this->data_all( $type ) ); } // end method "get_all" // ******************************************************** // ******************************************************** // method : head // description : return list of column names // parameter : (opt)(string)result-var-type // return : (array)result public function head( $type = 'assoc' ) { // check: connection to database if( ! $this->_db_connect() ) { return( false ); } // check: (correct) query executed if( ! $this->db_result ) { $this->_db_error( 'no (correct) query' ); return( false ); } $result = array(); while( $row = mysql_fetch_field( $this->db_result ) ) { if( $type == 'num' ) { $result[] = $row->name; } else { $result[ $row->name ] = $row->name; } } return( $result ); } // end method "head" // ******************************************************** // ******************************************************** // method : last // description : get id of last inserted row (when pk has auto increment) // parameter : (none) // return : (int)id public function last() { if( ! $this->_db_connect() ) { return false; } return( mysql_insert_id( $this->db_handle ) ); } // end method "last" // ******************************************************** // ******************************************************** // method : max // description : --- // parameter : (string)table, (string)column, (opt)(string)where // return : (mixed)query-result public function max( $table, $column, $where = 1 ) { return( $this->get( "SELECT MAX( ".$column." ) FROM ".$table." WHERE ".$where, "string" ) ); } // end method "max" // ******************************************************** // ******************************************************** // method : min // description : --- // parameter : (string)table, (string)column, (opt)(string)where // return : (int)count public function min( $table, $column, $where = 1 ) { return( $this->get( "SELECT MIN( ".$column." ) FROM ".$table." WHERE ".$where, "string" ) ); } // end method "min" // ******************************************************** // ******************************************************** // method : query // description : --- // parameter : (string)query // return : (int)affected rows public function query( $sql ) { // reset $this->db_query = ''; $this->db_command = ''; $this->db_rows = 0; // check: db connection opened if( ! $this->_db_connect() ) { return( false ); } // reset: prior results if( is_object( $this->db_result ) ) { mysql_free_result( $this->db_result ); } // execute: query $tmp = microtime( true ); $this->db_result = $this->db_unbuffered ? mysql_unbuffered_query( $sql, $this->db_handle ) : mysql_query( $sql, $this->db_handle ); // stats $this->db_time = microtime( true ) - $tmp; $this->db_query = trim(preg_replace("/ {2,}/", " ", preg_replace("/\t|\r|\n/", " ", $sql))); $this->db_query = mb_strlen($this->db_query) > 1024 ? mb_substr($this->db_query, 0, 1018)." [...]" : $this->db_query; $this->db_command = preg_match( "/^[^a-z]*([a-z]+)[^a-z]/i", $sql, $found ) ? mb_strtolower( $found[ 1 ] ) : 'unknown'; $this->db_total_queries ++; $this->db_total_time += $this->db_time; // check: log query? // check: slow query? if( $this->db_time >= $this->db_log_slow ) { $this->_db_log( 'slow' ); } // check: debug query? if( $this->db_debug ) { echo( $this->class_name." query #".number_format( $this->db_total_queries, 0, ',', '.' )." [".number_format( $this->db_time, 4, ',', '.' )."sec.]: ".$this->db_query."
\n
\n" ); } // check: syntax error if( ! $this->db_handle or mysql_errno( $this->db_handle ) ) { $this->_db_error( 'cannot execute query' ); return( false ); } // get: affected rows $this->db_rows = $this->db_unbuffered ? -1 : ( $this->db_command == 'select' ? mysql_num_rows( $this->db_result ) : mysql_affected_rows( $this->db_handle ) ); return( $this->db_rows ); } // end method "query" // ******************************************************** // ******************************************************** // method : rows // description : set logging vars // parameter : (none) // return : (int)rows public function rows() { return( $this->db_rows ); } // end method "rows" // ******************************************************** // ******************************************************** // method : set // description : set vars // parameter : (string)key|(array)param, (opt)(mixed)value // return : (void) public function set( $param, $value = null ) { if( ! is_array( $param ) ) { $param = array( $param => $value ); } foreach( $param as $key => $value ) { $key = 'db_'.$key; if( property_exists( $this, $key ) ) { $this->$key = $value; } } } // end method "set" // ******************************************************** // ******************************************************** // method : total // description : --- // parameter : (opt)(string)data // return : (mixed) public function total($data = null) { if (!isset($data)) { return [ 'host' => $this->db_host, 'queries' => $this->db_total_queries, 'time' => $this->db_total_time, ]; } switch ($data) { case 'queries' : return $this->db_total_queries; case 'time' : return $this->db_total_time; } return false; } // end method "total" } // end class "database" ?>