<?php
/**
* MySQL Wrapper Class
*
* @author Roy Toledo <toledoroy at hotmail dot com>
* @package DB
* @license GNU General Public License
* @version 3
* @date 2011-04-12
* @uses Constatns DBHost, DBUser, DBPassword, DBDefaultDatabase
* @todo Use Exeptions
*/
class dbWrapper
{
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()
{
$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);
}
}
|
Stumble It!
Bookmark in del.icio.us
Download
