<?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='';
}
}
?> |
Advertise on this site
Site map
Statistics
Site tips
Privacy policy
Contact