Location: PHPKode > scripts > MySQL Wrapper Script > class.mysqldb.php
<?php

/** 
*	MySQL Wrapper Class 
*
* @author Roy Toledo <toledoroy at hotmail dot com> 
* @package DB 
* @license GNU General Public License
* @version 2.2
* @date 2010-01-12
* @uses Constatns DBHost, DBUser, DBPassword, DBDefaultDatabase
* @todo Use Exeptions
*/

class db
{
    protected $host		= DBHost;
    protected $user 	= DBUser;
    protected $pass		= DBPassword;
    protected $dbname	= DBDefaultDatabase;
    protected $link;
	private $result;
	private $sql;
	private $rollback	= false;
	public $debug 		= false;
	
 	 
		 
	function __construct($db_name)
	{
		$this->connect();
		mysql_set_charset('utf8',$this->link); 
		$this->set_db($this->dbname);
		return $this->link;
	}
	
	private function connect()
	{
		$this->link = mysql_connect($this->host,$this->user,$this->pass,true) or die ("DB Connection Failed:<br />".$this->ReportError());
	}
	
	public function set_db($value)
	{
		if (mysql_select_db($value,$this->link)){
			return true;
		}else{
			$this->ReportError();
		}
	}
	
	public function getResult()
	{
		return $this->result;
	}
	/**
	* Check If Results Recieved From Last Query 
	* @return false, or Number Of Records int
	*/
	public function hasResults()
	{
		if ($this->result && mysql_num_rows($this->result) > 0)
			return mysql_num_rows($this->result);
		else
			return false;
	}
	
	/**
	* Handle DB Errors 
	*/
	private function ReportError($message = '')
	{
		$errNum  = mysql_errno($this->link);
		$errText = mysql_error($this->link);
		
		//Handle Specific Errors: 
		if ($errNum == 2006) exit("Database Not Available, Please Try Again Later");
	
		//Prepare The Error Message:
		$ERR_message = $_SERVER['PHP_SELF'].sprintf("DB Error,\n (%s): %s<p>", $errNum, $errText);
		if ($message != ''){
			$ERR_message .= $message;
		}
		
		//Take Action
		if ($this->debug) {
			echo($ERR_message);
		}else{
			error_log($ERR_message,0);
		}
		//throw new Exception($ERR_message);
	}
	
	/**
	* Run SQL Query 
	* @return Mysql Result Handler
	* @param $value Query String
	*/
	public function query($value)
	{
		$this->sql = $value;
	    if (!$this->result = mysql_query($value,$this->link)) {
			$this->rollback = true ;
			$this->ReportError();
			return false;
	    }else{
			return $this->result;
		}
	}
	
	/**
	* Run SQL One Row Query 
	* @return Array
	* @param $sql Query String
	*/
	public function queryRow($sql)	//Returns One Row as an Array
	{
		$this->sql = $sql;
		$result = $this->query($sql);
		if ($result)
			$row = mysql_fetch_array($result,MYSQL_ASSOC);
		else
			$row = false;
		return $row;
	}
	
	/**
	* Select All Table Rows
	* @param string $table_name Table Name
	* @param string $where WHERE Statement
	*/
	public function selectTable ($table_name,$where = 1)
	{
		$query = "SELECT * FROM $table_name WHERE $where;";
		return $this->query($query);
	}
	
	/**
	 * Get Enum Colum's Values By Column's Name 
	 * @param string $table
	 * @param string $field
	 */
	public function getEnumValues($table,$field)
	{
		if ($result = $this->query("SHOW COLUMNS FROM $table LIKE '$field' ",$this->link)) {
			while ($table = mysql_fetch_array($this->result)){
				$options= explode(",", preg_replace("/^enum\(|'|\)/i", "", $table[Type]));
			}
			return $options;
		}else{ $this->ReportError(); return false; }
	}
	
	/**
	* Get Query Row Result 
	* @return Array
	*/
	public function getRow()
	{
		if ($this->result)
			$row = mysql_fetch_array($this->result,MYSQL_ASSOC);
		else
			$row = false;
		return $row;	
	}
	
	/**
	* Get Last Insert ID 
	* @return Last Insert PKey
	*/
	public function getInsertId()
	{
		return mysql_insert_id($this->link);
	}
	
	/***** MySQL Transactions *****/
	
	/**
	 * Begin Transaction
	 */
	public function begin()
	{
	     $this->rollback = false;
	     $this->query("BEGIN");
	     if ($this->debug) echo "<br /><font color='blue'>Mysql begin</font><br />";
	}
	
	/**
	 * Commit Transaction (If Successful)
	 */
	public function commit()
	{
	    if ($this->rollback == false) {
			$this->query("COMMIT");
			if ($this->debug) echo "<br /><font color='blue'>Mysql commit</font><br />";
	    	return true;
	    }else{
	    	 $this->rollback();
	    	 if ($this->debug) echo "<br /><font color='red'>Mysql rollback</font><br />";
	    	 return false;
		}
	}
	
	/**
	 * Rollback Transaction
	 */
	public function rollback()
	{
		$this->query("ROLLBACK");
		$this->rollback = false;
		if ($this->debug) echo "<br /><font color='red'>Mysql Rollback</font><br />";
	}
	
	/**
	* Move Pointer To Resorce Begining
	*/
	public function reset()
	{
		@mysql_data_seek($this->result,0);		//Move Pointer To Resorce Begining
	} 
	
	/**
	* Get Results In a 3D Array
	* @return 3d Array
	*/
	public function get3DArray()
	{
		while ($row = mysql_fetch_array($this->result)){
			$result3d[] = $row;
		}
		return $result3d; 
	}
	
	/**
	* Prepare Values For Query  
	* @param $value string Single Value To Be Processed
	* @return $value string Processed Value
	*/
	private function prepare_sql_data_string($value)
	{
		/*
		if(get_magic_quotes_runtime()) $string = stripslashes($string);
		return @mysql_real_escape_string($string,$this->link_id);
		*/
		if ($value != ""){
			$exceptions = array('null','now()');
			if (!in_array(strtolower($value),$exceptions)){
				//$value = mysql_escape_string($value);
				$value = str_replace("'","`",$value);
				$value = "'".$value."'";
			}
			return $value;
		}else return "''";	
	}
	
	/**
	* Update a Table 
	* @param array $valuesArray Update Values as name=>value
	* @param string $table Table Name
	* @param string $pKey Table Private Key
	* @return bool
	*/
	public function update(array $valuesArray,$table,$pKey='id') // Recives colom name as array key and row value as array value (updates single table via PKey)
	{
	// Build Update Query
		foreach( $valuesArray as $key => $value) {
			$value = trim($value);
			if ($key == $pKey) {
		    	$where = "$pKey = ".$this->prepare_sql_data_string($value);
			}elseif ($value != ""){
				$sql_update[] = "`$key` = ".$this->prepare_sql_data_string($value);
			}
		}
		$set = implode(",",$sql_update);
		$query = "UPDATE $table SET $set WHERE $where";
		$this->sql = $query;
		if ($this->debug) echo "SQL: ".$query;
	// Run Query
		if ($this->query($query))
			return true;
		else {
			$this->ReportError();
			return false;
		}
	}
	
	/**
	* Insert Values To Table 
	* @param array $valuesArray Insert Values as name=>value Array
	* @param string $table Table Name
	* @return bool
	*/
	public function insert(array $valuesArray,$table) // Recives colom name as array key and row value as array value
	{
		$i=0;
	// Build Insert Query
		foreach($valuesArray as $key => $value) {
			$value = trim($value);
			if ($value !== ""){
				$sql_keys[$i] = $key;
				$sql_values[$i] = $this->prepare_sql_data_string($value);
				$i++;
			}
		}
		$q_keys = implode(",",$sql_keys);
		$q_values = implode(",",$sql_values);
		$query = "INSERT INTO $table ($q_keys) VALUES ($q_values)";
		if ($this->debug) echo "SQL: ".$query;	  				 
	// Run query
		if($this->query($query)){
			return true;
		}else{
			$this->ReportError();
			return false;
		}
	}
	
	/**
	* Replace Values in Table 
	* @param array $valuesArray, Update Values as name=>value Array
	* @param string $table Table Name 
	* @return bool
	*/
	public function replace($valuesArray,$table) // Recives colom name as array key and row value as array value
	{
		$i=0;
	// Build Insert Query
		foreach($valuesArray as $key => $value) {
			$value = trim($value);
			if ($value !== ""){
				$sql_keys[$i] = $key;
				$sql_values[$i] = $this->prepare_sql_data_string($value);
				$i++;
			}
		}
		$q_keys = implode(",",$sql_keys);
		$q_values = implode(",",$sql_values);
		$query = "REPLACE INTO $table ($q_keys) VALUES ($q_values)";
		if ($this->debug) echo "SQL: ".$query;	  				 
	// Run query
		return $this->query($query);
	}
	
	/**
	 * Copy Complete Table
	 * @param unknown_type $tableName
	 */
	public function backupTable($tableName)
	{	
		$newName = $tableName."_".date("dmy");;
		$sql = "CREATE TABLE `$newName` SELECT * FROM `$tableName` ";
		$this->query($sql);
	}

}
Return current item: MySQL Wrapper Script