File: sql.cls.php

<?php
/*
 * class name : sqlFunctions
 * developed by : Md. Shahadat Hossain Khan Razon
 * version : 1.00
 * date : May 17, 2006
 * dependency : none
 * homepage : http://360.yahoo.com/razonklnbd
 * email : razonklnbd@yahoo.com
 * developed for: http://www.squarepharma.com.bd
 */


/*
 * change log
 *
 * 20080519: fixed bug to implement mysql_real_escape_string with stripslashes if magic quote enabled checking
 * 20080522: implement connection identifier with internal $this->curlink variable
 *
 */


/*
	var $defaultdb=NULL;
	var $dbhost=NULL;
	var $dbuser=NULL;
	var $dbpass=NULL;
	var $curlink=false;
	var $curdatabase='';
	var $curtable='';
	var $clsCore;
	var $lastSQLquery;
	var $lastSuccessfulSql;
	var $lastInsertedID;
	var $lastAffectedRows=0;

	function __construct($p_core_class, $p_default_database='', $p_cur_link=false){
	function setConnection($p_conn){
	function setMyDatabase($p_dbname){
	function setCurrentTable($p_table){
	function openMyConn($p_dbhost='', $p_dbuser='', $p_dbpass=''){
	function openMyDatabase($p_dbname=''){
	function mysql_current_db(){
	function isDbOpened($p_dbname=''){
	function isTableExists($p_tablename=NULL){
	
//	 * $p_type=0	: unspecified or dynamic
//	 * $p_type=1	: only insert operation
//	 * $p_type=2	: only update operation
	function sqlInsertUpdate($p_fields, $p_pk_value='', $p_pk_name='', $p_table='', $p_pk_remove=true, $p_type=0){
	function getTotalRows($p_rs=NULL){
//	usage				: total number of rows in value useing sql count() function
	function getTotalRowsX($p_table_name, $p_condition=NULL){
	function getInsSql($p_fields, $p_table=NULL){
	function getUpdtSql($p_fields, $p_condition, $p_table=''){
	function build_sql($p_fld, $p_val, $p_condition='and'){
	function getRowsVal($p_sql, $p_row_num=-1){
//	usages				: return array fetch with assoc mode
	function getRowsValX($p_sql, $p_rwnum=0){
	function getMysqlDtTm($p_dt=0){
//	usage: isValExist($kwrd, 'keyword', 'resume_keywords', true, "and `skcid`='$skcid'")
	function sqlFetchAssoc($p_rs){
	function sqlFetchRow($p_rs){
	function sqlFetchObject($p_rs){
	function getLastExecutedQuery(){
	function getLastSuccessfulQuery(){
	function getLastInsertedID(){
	function runQuery($p_sql, $p_lnk_identifier='', $p_result_mode=''){
	function getRows($p_idfieldname, $p_sql, $p_lnk_identifier=NULL, $p_result_mode=NULL){
	function getArrWithIdAsKey($p_sql, $p_lnk_identifier=NULL, $p_result_mode=NULL){
	function __destruct(){
*/

if(!defined('no')) define('no', 0, true);
if(!defined('yes')) define('yes', 1, true);

class sqlFunctions{
	var $defaultdb=NULL;
	var $dbhost=NULL;
	var $dbuser=NULL;
	var $dbpass=NULL;
	var $curlink=false;
	var $curdatabase='';
	var $curtable='';
	var $clsCore;
	var $lastSQLquery;
	var $lastSuccessfulSql;
	var $lastInsertedID;
	var $lastAffectedRows=0;
	var $myrs;

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function __construct($p_core_class, $p_default_database='', $p_cur_link=false){
		$this->clsCore=$p_core_class;
		if(strlen($p_default_database)>0) $this->defaultdb=$p_default_database;
		if(strlen($this->defaultdb)>0) $this->curdatabase=trim($this->defaultdb);
		if($p_cur_link){
			$this->curlink=$p_cur_link;
			$this->curdatabase=trim($this->defaultdb);
		}
		return true;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20080522
	last modified date	: 20080522
*/
	//	Please use this function before every module running of this class
	function setConnection($p_conn){
		if(!is_resource($p_conn)) trigger_error('<h3>Invalid Resource Identifier</h3>'.mysql_error(), E_USER_ERROR);
		$this->curlink=$p_conn;
		return true;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function setMyDatabase($p_dbname){
		$this->curdatabase=trim($p_dbname);
		return true;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	//	Please use this function before every module running of this class
	function setCurrentTable($p_table){
		$this->curtable=trim($p_table);
		return true;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080522
*/
	function openMyConn($p_dbhost=NULL, $p_dbuser=NULL, $p_dbpass=NULL){
		$f_dbhost=$this->dbhost;
		$f_dbuser=$this->dbuser;
		$f_dbpass=$this->dbpass;
		if(isset($p_dbhost) && strlen($p_dbhost)>0) $f_dbhost=$p_dbhost;
		if(isset($p_dbuser) && strlen($p_dbuser)>0) $f_dbuser=$p_dbuser;
		if($f_dbpass!=$p_dbpass) $f_dbpass=$p_dbpass;
		if(!isset($f_dbhost) || strlen($f_dbhost)<=0) trigger_error('<h3>NULL Database Host</h3>'.mysql_error(), E_USER_ERROR);
		if(!isset($f_dbuser) || strlen($f_dbuser)<=0) trigger_error('<h3>NULL Database User</h3>'.mysql_error(), E_USER_ERROR);
		if(!isset($f_dbpass) || strlen($f_dbpass)<=0) trigger_error('<h3>NULL Database Password</h3>'.mysql_error(), E_USER_ERROR);
		if(!$this->curlink || $f_dbhost!=$this->dbhost || $f_dbuser!=$this->dbuser || $f_dbpass!=$this->dbpass){
			$f_lnk=mysql_pconnect($f_dbhost, $f_dbuser, $f_dbpass);
			if($f_lnk){
				$this->curlink=$f_lnk;
				$this->dbhost=$f_dbhost;
				$this->dbuser=$f_dbuser;
				$this->dbpass=$f_dbpass;
			}else trigger_error('<h3>Cannot open connection..</h3><div>Host: '.$f_dbhost.', User: '.$f_dbuser.', Password: '.$f_dbpass.'</div>'.mysql_error(), E_USER_ERROR);
		}
		return $this->curlink;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function openMyDatabase($p_dbname=NULL){
		if(strlen($p_dbname)>0) $f_dbname=trim($p_dbname);
		elseif(strlen($this->curdatabase)>0) $f_dbname=$this->curdatabase;
		else $f_dbname=trim($this->defaultdb);
		if(!$this->curlink) $this->openMyConn();
		if($this->curlink && isset($f_dbname)){
			if(!$this->isDbOpened($f_dbname)){
				$this->thisdb=mysql_select_db($f_dbname, $this->curlink) or die('Could not select database');
				if($this->thisdb) $this->curdatabase=$f_dbname;
				return $this->thisdb;
			}
		}
		return false;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function mysql_current_db(){
		if(!isset($this->curlink)) trigger_error('<h3>No Database Connection!</h3>'.mysql_error(), E_USER_ERROR);
		$v='';
		$r=$this->runQuery('SELECT DATABASE()') or die(mysql_error());
		if($r) $v=mysql_result($r,0);
		return $v;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function isDbOpened($p_dbname=NULL){
		if(strlen($p_dbname)>0){
			$v_dbname=$this->mysql_current_db();
			if($v_dbname==$p_dbname) return true;
		}
		return false;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20080110
	last modified date	: 20080110
*/
	function isTableExists($p_tablename=NULL){
		$bOk=false;
		if(isset($this->curtable) && @strlen($this->curtable)>0) $tblnm=$this->curtable;
		if(isset($p_tablename)>0) $tblnm=$p_tablename;
		if(isset($tblnm)){
			$rs=$this->runQuery('SHOW TABLES');
			while(list($tname)=$this->sqlFetchRow($rs)){
				if($tname==$p_tablename) $bOk=true;
			}
		}
		return $bOk;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	/*
	 * $p_type=0	: unspecified or dynamic
	 * $p_type=1	: only insert operation
	 * $p_type=2	: only update operation
	 */
	function sqlInsertUpdate($p_fields, $p_pk_value='', $p_pk_name='', $p_table='', $p_pk_remove=true, $p_type=0){
		$f_return=false;
		$f_update=false;
		if(strlen($p_table)>0) $f_table=$p_table;
		elseif(strlen($this->curtable)>0) $f_table=$this->curtable;
		if(strlen($p_pk_name)>0){
			if($p_pk_value!='') $f_update=$this->isValExist($p_pk_value, $p_pk_name, $f_table);
			if(true==$p_pk_remove) unset($p_fields[$p_pk_name]);
		}
		$v_sql=$this->getInsSql($p_fields, $f_table);
		if($p_type==0 || $p_type==2){
			if(true==$f_update) $v_sql=$this->getUpdtSql($p_fields, '`'.$p_pk_name.'`=\''.$p_pk_value.'\'', $f_table);
			if($p_type==2 && strlen($p_pk_name)<=0) $v_sql='';
			if($p_type==2 && false==$f_update) $v_sql='';
		}
		if(strlen($v_sql)>0){
			if(!$this->thisdb) $this->openMyDatabase($this->curdatabase);
			$this->lastSQLquery=$v_sql;
			$v_rs=$this->runQuery($v_sql);
			if($v_rs){
				$this->lastSuccessfulSql=$v_sql;
				$f_return=$p_pk_value;
				if(false==$f_update){
					$f_return=mysql_insert_id();
					$this->lastInsertedID=$f_return;
				}
				$this->curtable=trim($f_table);
			}
		}
		return $f_return;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function getTotalRows($p_rs=NULL){
		$r_rows=0;
		if(is_resource($p_rs)) $r_rows=mysql_num_rows($p_rs);
		return $r_rows;
	}

/*
	usage				: total number of rows in value useing sql count() function
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20071206
	last modified date	: 20071206
*/
	function getTotalRowsX($p_table_name, $p_condition=NULL){
		$f_sql='select count(*) from `'.$p_table_name.'`';
		if(NULL!=$p_condition) $f_sql.=' where '.$p_condition;
		$f_tot=0;
		$f_get=$this->getRowsVal($f_sql, 0);
		if(intval($f_get)>0) $f_tot=intval($f_get);
		return $f_tot;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080505
*/
	function getInsSql($p_fields, $p_table=NULL){
		$return_sql='';
		if(strlen($p_table)>0) $f_table=$p_table;
		elseif(strlen($this->curtable)>0) $f_table=$this->curtable;
		if(isset($f_table) && is_array($p_fields)){
			$sap=NULL;
			while (list($key, $val) = each($p_fields)){
				if(strlen(trim($val))>0){
					$keys_ins.=$sap."`$key`";
					$vals_ins.=$sap."'".mysql_real_escape_string((get_magic_quotes_gpc()==yes?stripslashes($val):$val))."'";
					$sap=', ';
				}
			}
			$return_sql="insert into `$f_table` ( $keys_ins ) values ( $vals_ins )";
		}
		return $return_sql;
	}
	
/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080505
*/
	function getUpdtSql($p_fields, $p_condition, $p_table=''){
		$return_sql='';
		if(strlen($p_table)>0) $f_table=$p_table;
		elseif(strlen($this->v_table)>0) $f_table=$this->v_table;
		if(isset($f_table) && is_array($p_fields)){
			$sap = "";
			while (list($key, $val)=each($p_fields)){
				$set_fields.=$sap.' `'.$key.'`='.(strlen(trim($val))>0?"'".mysql_real_escape_string((get_magic_quotes_gpc()==yes?stripslashes($val):$val))."'":'NULL');
				$sap = ', ';
			}
			$return_sql="update `$p_table` set $set_fields where $p_condition";
		}
		return $return_sql;
	}
	
/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function build_sql($p_fld, $p_val, $p_condition='and'){
		$return_val=NULL;
		if(strlen($p_fld)>0 && strlen($p_val)>0){
			$vals=explode(',', trim($p_val));
			$frstTm=true;
			foreach($vals as $valX){
				if($frstTm==false) $return_val.=" ".$p_condition." ";
				$val=strtolower(trim($valX));
				$return_val.="lower(`".trim($p_fld)."`) like '%".mysql_real_escape_string((get_magic_quotes_gpc()==yes?stripslashes($val):$val))."%'";
				$frstTm=false;
			}
			$return_val='('.$return_val.')';
		}
		return $return_val;
	}
	
/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function getRowsVal($p_sql, $p_row_num=-1){
		$this->lastSQLquery=$p_sql;
		$f_rs=$this->runQuery($p_sql);
		if($f_rs){
			$this->lastSuccessfulSql=$p_sql;
			if(mysql_num_rows($f_rs)>0){
				$f_rw=mysql_fetch_row($f_rs);
				if(intval($p_row_num)>=0) return stripslashes($f_rw[intval($p_row_num)]);
				else return $f_rw;
			}else return true;
		}else return false;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20080402
	last modified date	: 20080402
	
	usages				: return array fetch with assoc mode
*/
	function getRowsValX($p_sql, $p_rwnum=0){
		$f_rtrn=false;
		$this->lastSQLquery=$p_sql;
		$f_rs=$this->runQuery($p_sql);
		if($f_rs){
			$this->lastSuccessfulSql=$p_sql;
			if(mysql_num_rows($f_rs)>0){
				$f_ctr=0;
				$f_fl=true;
				while(true==$f_fl){
					$f_rw=mysql_fetch_assoc($f_rs);
					if($f_ctr==$p_rwnum && true==$f_fl){
						$f_fl=false;
						unset($f_rtrn);
						foreach($f_rw as $fld=>$val) $f_rtrn[$fld]=stripslashes($val);
					}
					$f_ctr++;
				}
			}
		}
		return $f_rtrn;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function getMysqlDtTm($p_dt=0){
		if($p_dt<=0) $p_dt=$this->clsCore->getMyCurrentTime();
		return date("Y-m-d H:i:s", $p_dt);
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
	
	usage: isValExist($kwrd, 'keyword', 'resume_keywords', true, "and `skcid`='$skcid'")
*/
	function isValExist($p_value, $p_field_name='id', $p_table=NULL, $p_case_ignored = true, $p_extra_condition = NULL){
		$return_val = false;
		if(strlen($p_table)>0) $p_table_name=$p_table;
		elseif(strlen($this->v_table)>0) $p_table_name=$this->v_table;
		if(strlen(trim($p_value))>0 && isset($p_table_name)>0){
			$v_value = trim($p_value);
			$condition1 = "lower(`".$p_field_name."`)='".strtolower(mysql_real_escape_string((get_magic_quotes_gpc()==yes?stripslashes($v_value):$v_value)))."'";
			if($p_case_ignored == false) $condition1="`".trim($p_field_name)."` = '".mysql_real_escape_string((get_magic_quotes_gpc()==yes?stripslashes($v_value):$v_value))."'";
			$v_sql = "select `".trim($p_field_name)."` from `".trim($p_table_name)."` where $condition1";
			if(strlen($p_extra_condition) > 0) $v_sql.=' '.trim($p_extra_condition);
			$v_rs = $this->runQuery($v_sql);
			if($v_rs){
				if(mysql_num_rows($v_rs) > 0){
					$v_rw = mysql_fetch_array($v_rs);
					$v_data = trim($v_rw[0]);
					if($v_data == $v_value) $return_val = true;
				}
			}
		}
		return $return_val;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function sqlFetchAssoc($p_rs=NULL){
		$frs=$this->myrs;
		if(isset($p_rs)) $frs=$p_rs;
		if($frs){
			$r_rw=mysql_fetch_assoc($frs);
			if(!isset($p_rs)) $this->myrs=$frs;
		}
		if(is_array($r_rw) && isset($r_rw)){
			foreach($r_rw as $k=>$v) $f_rtrn[$k]=stripslashes($v);
		}
		return $f_rtrn;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080522
*/
	function sqlFetchRow($p_rs=NULL){
		$frs=$this->myrs;
		if(isset($p_rs)) $frs=$p_rs;
		if($frs){
			$r_rw=mysql_fetch_row($frs);
			if(!isset($p_rs)) $this->myrs=$frs;
		}
		if(is_array($r_rw) && isset($r_rw)){
			foreach($r_rw as $k=>$v) $f_rtrn[$k]=stripslashes($v);
		}
		return $f_rtrn;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080522
*/
	function sqlFetchObject($p_rs=NULL, $p_result_type=NULL){
		$frs=$this->myrs;
		if(isset($p_rs)) $frs=$p_rs;
		if($frs){
			$r_rw=mysql_fetch_object($frs, $p_result_type);
			if(!isset($p_rs)) $this->myrs=$frs;
		}
		if(is_array($r_rw) && isset($r_rw)){
			foreach($r_rw as $k=>$v) $f_rtrn[$k]=stripslashes($v);
		}
		return $f_rtrn;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function getLastExecutedQuery(){
		return $this->lastSQLquery;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function getLastSuccessfulQuery(){
		return $this->lastSuccessfulSql;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080522
*/
	function getLastInsertedID($p_lnk=NULL){
		$flnkid=$this->curlink;
		if(isset($p_lnk)) $flnkid=$p_lnk;
		if(!isset($this->lastInsertedID)) $this->lastInsertedID=mysql_insert_id($flnkid);
		$rtrn=$this->lastInsertedID;
		unset($this->lastInsertedID);
		return $rtrn;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20080522
*/
	function runQuery($p_sql, $p_lnk_identifier=NULL){
		$this->lastAffectedRows=0; // this is not implemented very well.. i plan to implement this function later...
		$this->lastSQLquery=$p_sql;
		
		$flnkid=$this->curlink;
		if(isset($p_lnk_identifier)) $flnkid=$p_lnk_identifier;
		$rs=mysql_query($p_sql, $flnkid);
		if($rs){
			$this->lastAffectedRows=mysql_affected_rows($flnkid); // this is not implemented very well.. i plan to implement this function later... so please use this at your own risk!!!
			$this->lastSuccessfulSql=$p_sql;
			$this->myrs=$rs;
		}else trigger_error("<strong>SQL Syntex Error...</strong><br><em>$p_sql</em><br />".mysql_error()."<br />", E_USER_ERROR);
		return $this->myrs;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20071030
	last modified date	: 20080505
*/
	function getRows($p_idfieldname, $p_sql, $p_lnk_identifier=NULL, $p_result_mode=NULL){
		$cf_return=false;
		$rs=$this->runQuery($p_sql, $p_lnk_identifier, $p_result_mode);
		if($rs){
			if($this->getTotalRows($rs)>0){
				while($rw=$this->sqlFetchAssoc($rs)){
					foreach($rw as $k=>$v) $rx[$k]=stripslashes($v);
					$cf_return[$rx[$p_idfieldname]]=$rx;
				}
			}
		}
		return $cf_return;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20071031
	last modified date	: 20080505
*/
	function getArrWithIdAsKey($p_sql, $p_lnk_identifier=NULL, $p_result_mode=NULL){
		$cf_return=false;
		$rs=$this->runQuery($p_sql, $p_lnk_identifier, $p_result_mode);
		if($rs){
			if($this->getTotalRows($rs)>0){
				while($rw=$this->sqlFetchRow($rs)) $cf_return[trim($rw[0])]=trim(stripslashes($rw[1]));
			}
		}
		return $cf_return;
	}

/*
	created by			: Md. Shahadat Hossain Khan Razon (razonklnbd@yahoo.com)
	created date		: 20060517
	last modified date	: 20071030
*/
	function __destruct(){
		if(isset($this->curlink)){
			mysql_close($this->curlink);
			unset($this->curlink);
		}
		$this->curdatabase='';
		$this->curtable='';
		$this->lastSQLquery='';
		$this->lastSuccessfulSql='';
	}

}
?>