Login   Register  
PHP Classes
elePHPant
Icontem

File: class.Database.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of marcelo entraigas  >  AMD  >  class.Database.php  >  Download  
File: class.Database.php
Role: Class source
Content type: text/plain
Description: Auxiliary class
Class: AMD
Add, modify and delete MySQL table records
Author: By
Last change:
Date: 4 years ago
Size: 13,028 bytes
 

Contents

Class file image Download
<?php
/**
 * It's an easy way to run SQL querys an retrieve the results as an array.
 * <br>Last update: September 20, 2009
 * <br>License: BSD
 * <br>Author: Marcelo Entraigas <marcelo [at] entraigas.com.ar>
 */
class Database extends Error {
	//private var
	var $_connection      = array();  //(array) database connection
	var $_object_syntax   = true;     //(boolean) last sql query was stored as object or as an associative array
	
	/** (boolean) return results as object or an associative array */
	var $return_as_object = true;
	
	/** last query result array */
	var $results = array();
	
	/** last query metadata array */
	var $metadata = array();
	
	/** (integer) affected rows (INSERT, UPDATE, REPLACE) */
	var $affected_rows = 0;
	
	/** (integer) last insert id */
	var $insert_id     = 0;
	
	/**
	 * Class constructor.
	 *
	 * @param string $config configuration array index
	 */
	function Database($config = 'default') {
		global $cfg_resource;
		$this->_connection['link_id']      = false;
		$this->_connection['resource']     = false;
		$this->_connection['server']       = $cfg_resource[$config]['user']? $cfg_resource[$config]['server'] : '';
		$this->_connection['database']     = $cfg_resource[$config]['user']? $cfg_resource[$config]['database'] : '';
		$this->_connection['user']         = $cfg_resource[$config]['user']? $cfg_resource[$config]['user'] : '';
		$this->_connection['password']     = $cfg_resource[$config]['pass']? $cfg_resource[$config]['pass'] : '';
	}

	/**
	 * Free (unset) the cached results and metadata
	 */
	function free() {
		$this->results       = array();
		$this->metadata      = array();
		$this->insert_id     = 0;
		$this->affected_rows = 0;
	}
	
	/**
	 * Return results as an object or as an array
	 */
	function return_as_object($On = true){
		$this->return_as_object = ($On === true)? true: false;
	}

	/**
	 * Private function. Returns the columns metadata (name, type, size...)
	 *
	 * @return array|false array|false
	 */
	function _get_metadata() {
		if (@mysql_num_fields($this->_connection['resource'])) {
			$column = 0;
			$metadata = array();
			while ($column < @mysql_num_fields($this->_connection['resource'])) {
				$field         = @mysql_fetch_field($this->_connection['resource']);
				$field->flag   = @mysql_field_flags($this->_connection['resource'], $column);
				$field->column = $column;
				$metadata[$field->name] = get_object_vars($field);
				//set framework's format field
				switch ($metadata[$field->name]['type']) {
					case 'year':
					case 'int':
					case 'integer':
					case 'tinyint':
					case 'smallint':
					case 'mediumint':
					case 'bigint':
						$metadata[$field->name]['format'] = 'integer';
						break;
					case 'real':
					case 'float':
					case 'double':
					case 'decimal':
						$metadata[$field->name]['format'] = 'float';
						break;
					case 'date':
					case 'time':
					case 'datetime':
					case 'timestamp':
						$metadata[$field->name]['format'] = 'date';
						break;
					default:
						$metadata[$field->name]['format'] = 'string';
				}
				$column++;
			}
			return $metadata;
		} else
			$this->error('<b>Unable to get column\'s info</b>');
		return false;
	}


	/**
	 * Run a query and chache the results into an array.
	 *
	 * @param string $sql sql query
	 * @return integer number of affected rows
	 */
	function query($sql) {
		$this->sql = trim($sql);
		if(empty($this->sql)) return false;
		//reset previous result
		$this->free();
		$return   = 0;
		//make db connection
		$this->_connection['link_id'] = @mysql_connect($this->_connection['server'], $this->_connection['user'], $this->_connection['password']);
		if(!$this->_connection['link_id']){
			$this->error('<b>Unable to make a connection to the MySQL server.<br>Details:</b> check if the server is up, also check for the mysql library and user/password.');
			return false;
		}
		if (!@mysql_select_db($this->_connection['database'], $this->_connection['link_id'])) {
			$this->error('<b>Unable to select/use the database.<br>Details:</b> ' . @mysql_error($this->_connection['link_id']));
			return false;
		}
		//execute the sql query
		if(! $this->_connection['resource'] = @mysql_query($this->sql, $this->_connection['link_id'])){
			$this->error('<b>SQL Query error.<br>Details:</b> ' . @mysql_error($this->_connection['link_id']) . '<br><b>sql</b>&nbsp;[<i>' . $this->sql . '</i>]');
		}
		//get the results
		if (preg_match('/^(insert|delete|update|replace)\s+/i',$this->sql)) {
			//get the afected rows
			$this->affected_rows = @mysql_affected_rows($this->_connection['link_id']);
			$return = $this->affected_rows;
			//if it was an insert: get the last insert id
			if (preg_match('/^(insert|replace)\s+/i',$sql))
				$this->insert_id = @mysql_insert_id($this->_connection['link_id']);
		} else { //this is a select sql query
			//get the metadata from database
			$this->metadata = $this->_get_metadata();
			//save the last sql object/array state...
			$this->_object_syntax = $this->return_as_object;
			//get the results in the apropiated format
			if($this->return_as_object == true){
				//return results as an object
				$eval   = 'while ($row = @mysql_fetch_object($this->_connection["resource"])) $this->results[$return++] = $row;';
			}else{
				//return results as an associative array
				$eval   = 'while ($row = @mysql_fetch_array($this->_connection["resource"], MYSQL_BOTH)) $this->results[$return++] = $row;';
			}
			//get the results
			eval($eval);
			//get the total of affected rows
			$this->affected_rows = @mysql_num_rows($this->_connection["resource"]);
			//free mysql buffer
			@mysql_free_result($this->_connection['resource']);
		}
		//return the number of rows affected by the query
		return $return;
	}


	/**
	 * return the all cached results in the apropiate way
	 *
	 * @return array
	 */
	function get_cached_data($field='', $key=''){
		//by dafault return an empty array
		$return = array();
		if($this->_object_syntax == $this->return_as_object){
			//the results are stored using the right object/array syntax!
			//if the user want the results as they are sotred
			if($key=='' and $field=='')
				return $this->results; //return all the results
			//else, must retirve only what the user want...
			$key    = ($key==='')? '$index' : ($this->return_as_object ? "\$values->$key" : "\$values['$key']");
			$field  = ($field==='')? '$values' : ($this->return_as_object ? "\$values->$field" : "\$values['$field']");
			$eval   = "\$return[$key] = $field;"; //no casting
		}else{
			if($this->return_as_object == true){
				//this mean the results are stored as array, but must return as object array
				$key    = ($key==='')? '$index' : (is_integer($key)? "\$values[$key]" : "\$values['$key']");
				$field  = ($field==='')? '(object) $values' : (is_integer($field)? "\$values[$field]" : "\$values['$field']"); //only casting to object if returning all data
				$eval   = "\$return[$key] = $field;";
			}else{
				//this mean the results are stored as object array, but must return as array
				$key    = ($key==='')? '$index' : "\$values->$key";
				$field  = $field===''? 'get_object_vars($values)' : "\$values->$field"; //only casting to array if returning all data
				$eval   = "\$return[$key] = $field;";
			}
		}
		//create an array with the appropiate format and return it to user
		$eval = 'foreach($this->results as $index=>$values) ' . $eval;
		eval($eval);
		return $return;
	}
  
	/**
	 * Run a query and chache the results into an array
	 *
	 * @param string $sql sql query
	 * @param string $field only return this collumn.
	 * @param string $key sql field that will be the index of the array's results.
	 * @return array array
	 */
	function get_all($sql='', $field='', $key='') {
		$this->query($sql);
		return $this->get_cached_data($field, $key);
	}

	/**
	 * Return the cached results (as an object or an asosiative array).
	 *
	 * @param string $sql optional sql query
	 * @param integer $page
	 * @param integer $page_size
	 * @return array array
	 */
	function get_page($sql='', $page = 1, $page_size = 50) {
		if (!empty($sql)){
			$sql .= " LIMIT $page, $page_size";
			$this->query($sql);
		}
		return $this->get_cached_data();
	}

	/**
	 * Return a single row from cached results in the apropiate way
	 *
	 * @param string $sql optional sql query
	 * @param integer $row number of row (starting at 0)
	 * @return object|array object|array
	 */
	function get_row($sql='', $row=0) {
		if ($sql!='')
			$this->query($sql);
		if($this->_object_syntax == $this->return_as_object)
			//the results are stored using the right object/array syntax
			return $this->results[$row] ? $this->results[$row] : null;
		else{
			//if the results are stored as an array, but must return an object
			if($this->return_as_object == true)
				return $this->results[$row] ? (object) $this->results[$row] : null;
			//else, the results are stored as an object, but must return an array
			return $this->results[$row] ? get_object_vars($this->results[$row]) : null;
		}
	}

	/**
	 * Return a value from cached results
	 *
	 * @param string $sql optional sql query
	 * @param string|integer $field field name or number (starting at 0)
	 * @param integer $row number of row (starting at 0)
	 * @return value|null value|null
	 */
	function get_value($sql='', $row=0, $field=0) {
		if ($sql!='')
			$this->query($sql);
		if($this->_object_syntax == true){
			if(is_numeric($field)){
				$c=0;
				foreach ($this->metadata as $key => $value){
					if($c++==$field) $field = $key;
				}
			}
			return $this->results[$row]->$field ? $this->results[$row]->$field : null;
		}else
			return $this->results[$row][$field] ? $this->results[$row][$field] : null;
	}

	/**
	 * Returns the columns metadata (name, type, size...)
	 *
	 * @return array|false array|false
	 */
	function get_metadata() {
		return $this->metadata;
	}

	/**
	 * Returns the enum values of a column
	 *
	 * @param string $table
	 * @param string $column
	 * @return array
	 */
	function get_enum_values($table, $column){
		$sql = sprintf("SHOW COLUMNS FROM %s LIKE '%s'",$table, $column);
		$data = $this->get_row($sql);
		$enum = $this->return_as_object? $data->Type : $data['Type'];
		preg_match('/^enum\(\'(.*)\'\)$/',$enum, $tmp);
		$tmp = explode("','", $tmp[1]);
		$return = array();
		foreach ($tmp as $value){
			$return[$value] = $value;
		}
		return $return;
	}
	
}

class CachedDB extends Database {
	
	var $is_cached = null;
	var $cache_id  = '';
	
	function CachedDB($config = 'default'){
		parent::Database($config);
		global $cfg_resource;
		$this->_connection['cache_ttl']    = $cfg_resource[$config]['cache_ttl']? intval($cfg_resource[$config]['cache_ttl']) : 0;
		$this->_connection['cache_path']   = $cfg_resource[$config]['cache_path']? $cfg_resource[$config]['cache_path'] : '';
		$this->_connection['cache_prefix'] = $cfg_resource[$config]['cache_prefix']? $cfg_resource[$config]['cache_prefix'] : 'db_';
	}
	
	/**
	 * Check is the Database object is cached and not expired
	 *
	 * @param string $sql sql query
	 * @return boolean true|false
	 */
	function is_cached ($sql){
		$this->cache_id = $this->_connection['cache_path'] . $this->_connection['cache_prefix'] . md5($sql);
		//is it cached?
		if($this->cached) return true;
		//is it not cached?
		if($this->_connection['cache_ttl'] <= 0 or !file_exists($this->cache_id)) return false;
		//is it expired?
		if(!($mtime = filemtime($this->cache_id))) return false;
		if(($mtime + $this->_connection['cache_ttl']) < time()) {
			//erase the cached template
			@unlink($this->cache_id);
			return false;
		} else {
			//cache the result of is_cached() call
			$this->cached = true;
			return true;
		}
	}

	/**
	 * Reimplement the query method with caching system
	 *
	 * @param string $sql sql query
	 * @return integer number of affected rows
	 */
	function query($sql, $ttl=''){
		if($ttl>0)
			$this->_connection['cache_ttl'] = $ttl;
		$return = 0;
		if($this->is_cached($sql)){
			//try to load object from disk
			$vars = unserialize(file_get_contents($this->cache_id));
			foreach($vars as $key=>$val)
				eval("$"."this->$key = $"."vars['"."$key'];");
			$return = $this->affected_rows;
		}else{
			//execute the query
			$return = parent::query($sql);
			//try to save it to disk
			if($f = @fopen($this->cache_id,"w")){
				$arr_tmp = array(
					'results' => $this->results,
					'metadata' => $this->metadata,
					'insert_id' => $this->insert_id,
					'affected_rows' => $this->affected_rows,
				);
				@fwrite($f,serialize($arr_tmp));
				@fclose($f);
			}else{
				$this->error('Could not save db cache file');
			}
		}
		return $return;
	}
}
?>