Location: PHPKode > projects > Cartboard Box Manager > cboxmanager_0.1.4/lib/class.database.php
<?php
/**
 *	MySQL database layer class
 * 
 * 	@author			Edoardo Tenani <hide@address.com>
 * 	@license		GNU Public License
 * 					<http://opensource.org/licenses/gpl-3.0.php>
 * 	@copyright		Edoardo Tenani - 2010
 *
 * 	@package 		cboxmanager
 * 	@subpackage 	database
 * 	@category		mysql
 * 	@since			0.0.1
 *
 *	@todo
 *		implements escaping in add_data() and del_data()
 */
class Database {

	/**
	 *	If setted enable debug prints
	 *	
	 *	@access protected
	 *	@var bool
	 */
	protected $debug = false;

	/**
	 *	The host of the MySQL Server
	 *
	 *	@access protected
	 *	@var string
	 */
	protected $host;
	
	/**
	 *	The user to access the MySQL Server
	 *
	 *	@access protected
	 *	@var string
	 */
	protected $user;
	
	/**
	 *	The password to access the MySQL Server
	 *
	 *	@access protected
	 *	@var string
	 */
	protected $pass;

	/**
	 *	The name of the db on the MySQL Server
	 *
	 *	@access protected
	 *	@var string
	 */
	protected $name;
		
	/**
	 *	Define placeholders used by {@link Database::query() query()} function
	 *
	 *	@access protected
	 *	@var array
	 */
	protected $placeholders = array("%d", "%s", "%n", "%f", "%b");
	
	
	/**
	 *	The resource(link) to manage the database
	 *
	 *	@access private
	 *	@var resource
	 */
	private $handler;
	
	/**
	 * 	The last query result ( on which apply some functions )
	 *
	 * 	@access private
	 * 	@var resource
	 */
	private $result;

	/**	
	 *	Constructor of the class.
	 *
	 * 	Initialize $user, $pass, $host, $name and return a pointer
	 * 	($handler) to manage the database.
	 *
	 *	@param	string $host the host of the MySQL server
	 *	@param	string $user the user to access the MySQL server
	 *	@param	string $pass the password to access the MySQL server
	 *	@param	string $database_name the name of the database to be selected
	 *	@return resource the simbolic link to manage the database
	 *
	 * 	@link http://php.net/func_num_args
	 * 	@link http://php.net/func_get_args
	 */
	public function __construct() {
		
		if ( func_num_args() < 4 )
			return false;
		
		$args = func_get_args();
		
		$this->host = $args[0];
		$this->user = $args[1];
		$this->pass = $args[2];
		$this->name = $args[3];
		
		$this->handler = mysql_connect($this->host, $this->user, $this->pass);

		if ( !$this->handler )
			return false;
		
		$select = mysql_select_db($this->name, $this->handler);

		if ( !$select )
			return false;
		
		return $this->handler;
	}
	
/*	OBJECT FUNCTION OVERRIDE
 ************************************************/

	/**
	 * 	When the class is destroyed this function is called.
	 *
	 * 	@return void
	 */
	public function __destruct() {	}

	/**
	 * 	When an inaccesible method of the class is invoked this function
	 * 	is called.
	 *
	 * 	@return void
	 */
	public function __call($name, $arguments) {
        echo get_class($this)."::Error in calling object method <b>'$name'</b>"."<br>\n";
    }

	/**
	 * 	When an inaccesible static method of the class is invoked this
	 * 	function is called.
	 *
	 * 	
	 * 	@return void
	 */
	public static function __callStatic($name, $arguments) {
        echo get_class($this)."::Error in calling object static method <b>'$name'</b>"."<br>\n";
    }

	/**
	 * 	When the object is converted to a string this function is used to
	 * 	format the result string.
	 * 	Values are separated by "|". If the class var is an array, values
	 * 	in it will be separated by ",", or NULL if is not set.
	 * 	The string starts and ends with "-".
	 *
	 * 	@access public
	 * 	@return a string with all class vars values separated by |
	 */
	public function __toString() {		
		return NULL;
	}

/*	SET & GET FUNCTION
 ************************************************/

	/**
	 * 	The function set a specified class var.
	 * 	Is not case sensitive.
	 *
	 * 	@access public
	 * 	@param	string $var the name of the var to be set
	 * 	@param	mixed $value the new value to set
	 * 
	 *	@return bool true
	 */
	public function set($var, $value = NULL) {
		$this->{$var} = $value;
		return true;
	}

	/**
	 * 	The function get a specified class var.
	 * 	Is case sensitive.
	 *
	 * 	@access public
	 * 	@param	string $var the name of the var to be get
	 *
	 * 	@return	mixed the class var
	 */
	public function get($var) {
		return $this->{$var};
	}


/*	PUBLIC FUNCTION
 ************************************************/

##################################################	
##	Connection management

	/**
	 *	Check db connection
	 *	
	 *	Check if a connection with database is already established
	 *	
	 *
	 *	@return bool true if connection is established, false otherwise
	 *
	 *	@uses Database::handler
	 *	@internal uses $this->handler to check if connection
	 * 		exists
	 *	
	 *	@see Database::handler
	 */
	public function is_connected() {
		if ( $this->handler !== false )
			return true;
		else
			return false;
	}

	/**
	 *	@ignore
	 *
	 * 	Create a persistent connection
	 *
	 * 	Not yet implemented.
	 */
	public function persistent_connection() {}
	
	/**	
	 *	Close the connection to the database
	 *
	 *	@return bool true if the connection is succesfully close, otherwise
	 * 		false
	 *
	 * 	@link http://php.net/mysql_close
	 */
	public function close() {
		return mysql_close($this->handler) ? true : false;
	}

##################################################	
##	Database management
	
	/**	
	 *	Create a database with the specified name
	 * 
	 *	To be created, the $handler variable must be set to NULL.
	 *
	 *	@param	string $db_name the name of the database to be created
	 *	@return	bool true if the operation success, false if fail or if
	 * 		the $handler var is set
	 */
	public function create_database($db_name) {
		return ( !$this->handler ) ? $this->query("CREATE DATABASE".$db_name) : false;
	}

	/**	
	 *	Drop a database with the specified name
	 * 
	 *	To be dropped, the $handler variable must be set to NULL.
	 *
	 *	@param string $db_name the name of the database to be dropped
	 *	@return bool true if the operation success, false if fail or if the
	 * 		$handler var is set
	 */
	public function drop_database($db_name) {
		return ( !$this->handler ) ? $this->query("DROP DATABASE".$db_name) : false;
	}

##################################################	
##	Table management	
	
	/**	
	 *	Create a table.
	 *
	 * 	Not yet implemented.
	 *
	 * 	@ignore
	 */
	public function create_table() {}
	
	/**	
	 *	Drop a table with the specified name.
	 *
	 *	@param	string $table_name the name of the table to be dropped
	 *	@return bool true if the operation success, false otherwise
	 */
	public function drop_table($table_name) {
		return Database::query("DROP TABLE IF EXISTS".$table_name);
	}

##################################################	
##	Query

	/**	
	 *	Execute the query specified in the argument
	 *
	 * 	Execute the query, storing result in $result and returns
	 * 	results. If a % is needed inside a query, must be prepended
	 *	by a '%' ( %% ).
	 *
	 *	@param	string $query the query to be executed
	 *	@param	mixed $args, ... all the argumens above first are replace for $query placeholders
	 *	@return	resource a link to the results of the query, false if the
	 * 		query fails
	 *
	 * 	@internal escape all arguments passed to make them "sql ready",
	 *		reducing risk of SQL Injection; if no placeholders are found
	 *		in the query string a E_USER_WARNING error is thrown;
	 *
	 *	@uses Database::str_occurences()
	 *	@uses Database::aescape()
	 *	@uses Database::_query()
	 * 	@link http://php.net/debug_backtrace
	 * 	@link http://php.net/trigger_error
	 * 	@link http://php.net/vspritf
	 */
	public function query() {
		$args = func_get_args();
		$query = $args[0];
		array_shift($args);
		
		$types = $this->str_occurences($query, $this->placeholders);
		
		// check if $types is null ( no placeholders ) and are presents
		// single quotes ( ' ) and are not present double % ( which
		// means that a single % is needed in the query
		if ( ( !( $types ) && strpos($query, "'") ) && !strpos($query, "%%") ) {
			$debug = debug_backtrace();
			trigger_error("Using Database::query() without placeholder is not safe. Check your query at <b>".$debug[0]["file"]."</b> on line <b>".$debug[0]["line"]."</b>", E_USER_NOTICE);
		}
		
		$query = vsprintf($query, $this->aescape($types, $args));

		return $this->_query($query);
	}
	
	/**	
	 *	Add data to a specified table.
	 *
	 *	The arguments are to be passed in these order: $tablename,
	 * 	$column_1, $type_1, $value_1, $column_2, $type_2, $value_2, ...
	 *	<br>
	 *	After the $tablename, respecting the order ( $column, $type, 
	 *	$value ), it's possible to specify an infinite number of values.
	 *
	 * 	@param	string $tablename the name of the table in which insert
	 * 		data
	 * 	@param	string $column the column in which insert data
	 * 	@param	string $type the type of the inserted data ( to be 
	 *		properly escaped )( relative to the previous defined $column
	 *		var )
	 * 	@param	mixed $value the value to be inserted ( relative to the
	 * 		previous defined $column var )
	 *	@param	mixed ... other sets of values
	 *	@return	bool true if the operation success, false otherwise
	 *
	 *	@internal escapes inserted data
	 *
	 * 	@uses Database::query()
	 * 	@link http://php.net/func_num_args
	 * 	@link http://php.net/func_get_args
	 */
	public function add_data() {
		if ( func_num_args() < 3 )
			return false;
		
		$args = func_get_args();
		
		$table = $args[0];
		$count = 0;
		$i = 1;
		while ( $i < func_num_args() ) {
			$data[$count] = $args[$i];
			$type[$count] = $args[$i+1];
			$value[$count] = $args[$i+2];
			
			$count++;
			$i += 3;
		}
		$query = "INSERT INTO ".$table." (";
		for ( $i = 0; $i < $count; $i++ ) {
			if ( $i == $count-1 )
				$query .= $data[$i];
			else
				$query .= $data[$i].", ";
		}
		$query .= ") VALUES (";
		for ( $i = 0; $i < $count; $i++ ) {
			if ( $i == $count-1 )
				$query .= "'".$this->escape($type[$i], $value[$i])."'";
			else
				$query .= "'".$this->escape($type[$i], $value[$i])."', ";
		}
		$query .= ")";
		
		return $this->_query($query);
	}

	/**	
	 *	Delete a data from a specified table.
	 *
	 *	@param	string $table the name of the table in which the data
	 *		are stored
	 *	@param	string $conditions the conditions to select the data to
	 *		be deleted
	 *	@param	mixed $args, ... all the argumens above second are 
	 *		replace for $conditions placeholders
	 *	@return	bool true if the operation success, false if fail
	 *
	 *	@internal escape all arguments passed to make them "sql 
	 *		ready", reducing risk of SQL Injection; if no placeholders
	 *		are found in the query string a E_USER_WARNING error is 
	 *		thrown;
	 *
	 *	@uses Database::str_occurences()
	 *	@uses Database::aescape()
	 *	@uses Database::_query()
	 * 	@link http://php.net/debug_backtrace
	 * 	@link http://php.net/trigger_error
	 * 	@link http://php.net/vspritf
	 */
	public function del_data() {
		$args = func_get_args();
		
		$table = $args[0];
		array_shift($args);
		
		$conditions = $args[0];
		array_shift($args);
		
		$query = "DELETE FROM ".$table." WHERE ".$conditions;
		
		$types = $this->str_occurences($query, $this->placeholders);
		
		// check if $types is null ( no placeholders ) and are presents
		// single quotes ( ' ) and are not present double % ( which
		// means that a single % is needed in the query
		if ( ( !( $types ) && strpos($query, "'") ) && !strpos($query, "%%") ) {
			$debug = debug_backtrace();
			trigger_error("Using Database::del_data() without placeholder is not safe. Check your query at <b>".$debug[0]["file"]."</b> on line <b>".$debug[0]["line"]."</b>", E_USER_WARNING);
		}
		
		$query = vsprintf($query, $this->aescape($types, $args));
		
		return $this->_query($query);
	}

	/**	
	 *	Modify data in a table.
	 *
	 * 	Not yet implemented.
	 *
	 * 	@ignore
	 */
	public function mod_data() {}

##################################################	
##	Utilities
	
	/**
	 * 	Execute mysql_num_rows() on the last query result
	 *
	 * 	Execute mysql_num_rows() on $this->result
	 *
	 * 	@access public
	 * 	@return	mixed the number of rows in a result set on success or
	 * 		false on failure
	 *
	 * 	@internal use the class private var result as argument to the
	 * 		mysql_num_rows() function
	 *
	 * 	@link http://php.net/mysql_num_rows
	 */
	public function num_rows() {
		return mysql_num_rows($this->result);
	}
	
	/**
	 * 	Execute mysql_fetch_array() on the last query result
	 *
	 * 	Execute mysql_fetch_array() on $this->result
	 *
	 * 	@access public
	 * 	@param	mixed $query_result results of a Database::query() call
	 * 		can be specified to execute mysql_fetch_array on the specified
	 * 		results set
	 * 	@param	string $type specify the type of fetch to be performed:
	 * 		- BOTH	( default ) return both an associative and numerical
	 * 			array
	 * 		- ASSOC	return an associative array
	 * 		- NUM		return a numerical array
	 * 	@return	mixed an array with the row data ( based on the $type
	 * 		param ) or false on failure
	 *
	 * 	@internal use the class private var result as argument to the
	 * 		mysql_fetch_array() function
	 *
	 * 	@link http://php.net/mysql_fetch_array
	 */
	public function fetch_array($query_result = NULL, $type = "BOTH") {
		if ( isset($query_result) && !empty($query_result) )
			$result = $query_result;
		else
			$result = $this->result;

		switch ( $type ) {
			case "BOTH":
				$ret = mysql_fetch_array($result, MYSQL_BOTH);
			break;
			case "ASSOC":
				$ret = mysql_fetch_array($result, MYSQL_ASSOC);
			break;
			case "NUM":
				$ret = mysql_fetch_array($result, MYSQL_NUM);
			break;
			default:
				$ret = false;
			break;
		}
		
		return $ret;
	}
	
	/**
	 *	Free result memory
	 *
	 *	Free all memory associated with the last result
	 *
	 *	@return true on success, false otherwise
	 *
	 *	@link http://php.net/mysql_free_result
	 */
	public function free() {
		return mysql_free_result($this->result);
	}

/*	PRIVATE FUNCTION
 ************************************************/
 
	/**
	 *	Execute a query
	 *
	 *	Execute the specified query, saving result to $this->result.
	 *	Is private becaus doesn't do an escape on values; is useful because is called to perform the query
	 *
	 *	@param	string $query the query to be executed
	 *	@return resource a link to the results of the query, false if the
	 * 		query fails
	 *
	 * 	@internal store result for further use ( like mysql_num_rows or 
	 *		mysql_fetch_array without need of an argument)
	 *
	 *	@link http://php.net/mysql_query
	 */
	private function _query($query) {
		if ( $this->debug )
			echo "<pre>", var_dump($query), "</pre>";
		
		$this->result = mysql_query($query);
		
		return $this->result;
	}

	/**
	 *	Escape a string
	 *
	 *	The function escapes passed value basing on the specified type
	 *
	 *	@param	string $type can be one of the following:
	 *		- %d: decimal value
	 *		- %s: text value
	 *		- %n: numeric value ( not exadecimal )
	 *		- %f: float value
	 *		- %b: binary value
	 *	@param	mixed $value the value to be escaped
	 *	@return mixed the escaped value or false on failure
	 *
	 *	@internal in case of binary data, data are not escaped and must be
	 * 		binary data before calling this function on them
	 *
	 *	@link http://php.net/mysql_real_escape_string
	 *	@link http://php.net/is_numeric
	 *	@link http://php.net/preg_match
	 *	@link http://php.net/manual/en/language.types.type-juggling.php
	 */
	private function escape($type, $value = "") {
		switch ($type) {
			case '%d':
				return (int) $value;
			break;
			case '%s':
				return mysql_real_escape_string($value);
			break;
			case '%n':
				return is_numeric($value) && !preg_match('/x/i', $value) ? $value : '0';
			break;
			case '%f':
				return (float) $value;
			break;
			case '%b':
				return $value;
			break;
		}
		
		return false;
	}
	
	/**
	 *	Escape all array values
	 *
	 *	Escapes array values basing on key to detect type; like {@link Database::escape() escape()}, but accepts an array as second argument
	 *
	 *	@param	array $types an array with all the types of the values to be escaped
	 *	@param	array $values an array with all the values to be escaped
	 *	@return array an array with escaped values or false on error
	 *
	 *	@see Database::escape()
	 */
	private function aescape($types, $values) {
		if ( count($types) == count($values) ) {
			$iteration = count($types);
			for ( $i = 0; $i < $iteration; $i++ ) {
				$array[] = $this->escape($types[$i], $values[$i]);
			}
			return $array;
		}
		
		return false;
	}
	
	/**
	 *	Return all occurences of a string
	 *
	 *	Return an array with all the occurences oa the specified string
	 *
	 *	@param	string $haystack the string to search in 
	 *	@param	mixed $needle the string to search for; can be a single string or an array of strings
	 *	@return an array of needed strings ordered by postiion in string or false on failure( no string found or errors )
	 *
	 *	@link hhtp://php.net/is_array
	 *	@link hhtp://php.net/is_int
	 *	@link hhtp://php.net/strpos
	 *	@link hhtp://php.net/ksort
	 *	@link hhtp://php.net/array_values
	 */
	private function str_occurences($haystack, $needle) {
		$offset = 0;
		$array = array();

		if ( is_array($needle) ) {
			foreach ( $needle as $need ) {
				while ( is_int($offset = strpos($haystack, $need, $offset)) ) {
					$array[$offset] = $need;
					
					$offset++;
				}
			}
		}
		else {
			while ( is_int($offset = strpos($haystack, $needle, $offset)) ) {
				$array[$offset] = $need;

				$offset++;
			}
		}

		if ( $array ) {
			ksort($array);
			
			return array_values($array);
		}
		
		return false;
	}
}
?>
Return current item: Cartboard Box Manager