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  >  Simple DB  >  class.database.php  >  Download  
File: class.database.php
Role: Class source
Content type: text/plain
Description: main class file
Class: Simple DB
Store and retrieve data from a MySQL database
Author: By
Last change:
Date: 5 years ago
Size: 10,989 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;
		if($_SESSION['debug']===true) $start_time = getTime();
		//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']);
		//} elseif (preg_match('/^(select|show|describe)\s+/i',$this->sql)) { //this is a select sql query
		} 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']);
		}
		
        //debug messages...
		if($_SESSION['debug']===true){
			$time = getTime() - $start_time;
			$_SESSION['debug_msg'][] = array('sql'=>$sql,'time'=>$time);
		}
		//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);
		preg_match('/^enum\((.*)\)$/',$data->Type, $tmp);
		$tmp = str_replace("'",'',$tmp[1]);
		$tmp = explode(',', $tmp);
		$return = array();
		foreach ($tmp as $value){
			$return[$value] = $value;
		}
		return $return;
	}
	
}
?>