<?php
/*#######################################################################
# #
# Description : framework for database accesses #
# Author : Mr. AsankA De Silva #
# Date Created : November 11,2008 #
# Project : #
# #
#######################################################################*/
class DBAccess
{
/* this member variable holds the name of the data base*/
protected $Catalog =' ';
/*this holds the name of the db server. [may be the IP address of the server with port id example 192.172.0.243:4328]*/
protected $ServerName ='';
/*the user name use to loggin in to the database */
protected $Username = '';
/*the password for the above user name wich used to loggin in to the db server*/
protected $password = '';
protected $errorMsg="";
//########################################## MySql 4 #########################################################################
/*the constructor initializes the db connection properties
* to get the connection you must call the openConnection() method
* it returns link identifier for current db Connection
*/
function __construct()
{
/*$this->ServerName= ini_get("ServerName");
$this->UserName = ini_get("UserName");
$this->password = ini_get("Password");
$this->Catalog = ini_get("dbName"); */
}
function __toString()
{
return "DB Acces Class";
}
/*opens the Connection to
*database and return the
*Link Identifier for the
*connection
*/
public function ConnectDb()
{
try
{
$Link = mysql_connect($this->ServerName, $this->Username ,$this->password);
//if the link identifer =FALSE then Exception is trhown since it has not connected to the server
if(!$Link)
{
$errorMsg ="Connecting mysql server was failed";
return false;
}
$isConnected = mysql_select_db($this->Catalog,$Link);
if(!$isConnected)
{
$errorMsg ="Database cannot be found";
return false;
}
//if selection fails then Exception is thrown
}
catch (Exception $e)
{
$errorMsg ="Connecting mysql server was failed";
//echo("Error In Connetion");
return false;
// throw new Exception('Could not Connect the Database ['.$this->Catalog.']'.$e);
}
//if no exception is thrown then link identifier is returned
return $Link;
}
/*
* attemp to close the
* database connection
* identified through
* the provided link identifier
*/
public function CloseConnection($linkIdentifier)
{
try
{
return mysql_close($linkIdentifier);
}
catch (Exception $ex)
{
return fals;
}
}
/*
*perform a insert/update query on
*mysql database and return [true]
* on success and [false] if fails
*/
public function InsertUpdateQuery($strQuery)
{
$result=false;
try
{
$link = $this->ConnectDB();
$result = mysql_query($strQuery,$link);
}
catch(Exception $ex)
{
$result=false;
$errorMsg =mysql_error($link);
}
$this->CloseConnection($link);
return $result;
}
/*
* perform a select query and
* retuns the results as Associative array
* if fails returns a Exception from MySql
* <param>
* <$strQuery> MySql Select Query </$strQuery>
* </param>
*/
public function GetPopulateResultSet($strQuery)
{
try
{
$link = $this->ConnectDb();
if(!$link)
{
$errorMsg="Connection failed to server";
return false;
}
else
{
$ResultSet = mysql_query($strQuery,$link);
$this->CloseConnection($link);
return $ResultSet;
}
}
catch (Exception $e)
{
$this->CloseConnection($link);
$errorMsg = mysql_error($link);
return false;
}
}
/*
* returns an integer value
* indicating total no of
* records on the particular
* Table you specified
* <param>
* <$tableName> name of the table </$tableName>
* </param>
*/
public function GetNoOfRecords($tableName)
{
/*$link = $this->ConnectDb();
try
{
$sqlCommand= "Select * From $tableName";
$result= mysql_query($sqlCommand,$link);
$numOfRecords= mysql_num_rows($result);
}
catch (Exception $e)
{
$this->CloseConnection($link);
throw $e;
}
$this->CloseConnection($link);
return $numOfRecords;*/
}
/*
* this initializes a transaction on mySql
* server and return link identfier for the
* created connection if success, returns
* false if fails.
*/
public function beginTransaction()
{
try
{
$link = $this->ConnectDb();
$result = mysql_query("BEGIN",$link);
if(!$result)
{
$errorMsg =mysql_error($link);
return false;
}
else
{
return $link;
}
}
catch (Exception $ex)
{
$errorMsg =mysql_error($link);
return false;
}
}
/*
* this can be used to commit the transaction
* that you have allready begin. to identify
* connection you have to provide the
* link identifier returned by beginTransaction()
*/
public function CommitTransaction($link)
{
try{
$result = mysql_query("COMMIT",$link);
if(!result)
{
$errorMsg =mysql_error($link);
return false;
}
else
{
return true;
}
}
catch (Exception $ex)
{
$errorMsg =mysql_error($link);
return false;
}
}
/*
* use this function to roll back the
* transaction that you have being
* executed.
*/
public function RollBackTransaction($link)
{
try
{
$result = mysql_query("ROLLBACK",$link);
if(!$result)
{
$errorMsg =mysql_error($link);
return false;
}
else
{
return true;
}
}
catch (Exception $ex)
{
$errorMsg =mysql_error($link);
return false;
}
}
/*
* the method can be used to execute
* a MySql Query wich will not return
* scalar array. this can be used to
* execute insert/update queries
* since this only returns boolean value
* indicating query successfully
* performed or not. [if success :true , else : false]
*/
public function Trans_InsertUpdateQuery($query,$link)
{
try
{
$result = mysql_query($qery,$link);
if(!$result)
{
$errorMsg =mysql_error($link);
return false;
}
else
return true;
}
catch (Exception $ex)
{
$errorMsg =mysql_error($link);
return false;
}
}
/*
* this method can be used to perform a
* mySql SELECT queries with the transaction
* need to provide the link identifier
* for the connection currently you have
* being used. if query fails return false
* otherwise resulting array wil be return
*/
public function Trans_GetPopulateResultSet($query,$link)
{
try
{
$result =mysql_query($query,$link);
if(!result)
{
return false;
}
else
{
return $result;
}
}
catch(Exception $ex)
{
throw $ex;
}
}
public function GetNextNo($TableName, $ColumnName)
{
$mysqlQuery = "SELECT IFNULL(MAX($ColumnName),0) FROM $TableName";
$link = $this->ConnectDb();
$result = mysql_query($mysqlQuery,$link);
if(!$result)
{
return false;
$this->CloseConnection($link);
}
else
{
$updateColumn = "UPDATE $TableName SET $ColumnName=".$result++;
if(mysql_query($updateColumn,$link))
{
return $result;
}
else
{
$this->CloseConnection($link);
}
}
}
public function GetNextNoWithTrans($TableName,$ColumnName)
{
$SelectQuery= "SELECT IFNULL(MAX($ColumnName,0) FROM $TableName";
$UpdateQuery ="UPDATE $TableName SET $ColumnName=";
try
{
$link = $this->beginTransaction();
$result = mysql_query($SelectQuery,$link);
if(!$result)
{
$this->RollbackTransaction();
return false;
}
else
{
if(mysql_query($UpdateQuery.$result++,$link))
{
$this->CommitTransaction();
return $result;
}
else
{
$this->RollbackTransaction();
return false;
}
}
}
catch (Exception $ex)
{
$this->RollbackTransaction();
throw $ex;
}
}
/*
*
*
*
**/
public function getTheError()
{
return $errorMsg;
}
//############################################### MySql 4 #####################################################################
/* here region of mysql 4 functions will end and MySql 5 function region is started */
//############################################### MySql 5 #####################################################################
/*
* Executing Stored procedures can only be
* done with the MySql 5 or later versions.
* this requires php to use mysql improved extension
* if this fails try configuring your web server to use
* php_mysqli.dll in php extension
* [ $param ] is optional
*/
public function InsertUpdatewithSP($spName,$param=NULL)
{
$ArrCount =count($param);
$query="";
$linkIdentifier;
if($ArrCount >0)
{
$query = "call".$spName."(";
for ($i=0 ; $i < $ArrCount ; $i++)
{
$query.="'".$param[$i]."',";
}
$query = rtrim($query,',').")";
}
try
{
$linkIdentifier = mysqli_connect($ServerName,$UserName,$password,$Catalog);
if(!$linkIdentifier)
{
$result=mysqli_query($linkIdentifier,$query);
if(!$result){
$errorMsg="Query faild to perform";
return false;
}
else{
return true;
}
mysqli_close($linkIdentifier);
}
else
{
mysqli_close($linkIdentifier);
$errorMsg= "Could not connect to the mysql server";
return false;
}
}
catch (Exception $e)
{
throw new Exception("Could not connect to the server. Your MySql server may not support this feature, try newer version of MySql Server");
}
}
/*
* use this method to execute Stored procedure
* on mysql db and get result set as a output
* this returns false if query fails and
* returns resulting array if query succeed
* $param is optional. you may leave it
* without passing values to it.
*/
public function GetpopulateResultSetwithSP($spName, $param = NULL)
{
$ArrCount =count($param);
$query="";
if($ArrCount >0)
{
$query = "call".$spName."(";
for ($i=0 ; $i < $ArrCount ; $i++)
{
$query.="'".$param[$i]."',";
}
$query = rtrim($query,',').")";
}
try
{
$linkIdentifier = mysqli_connect($ServerName,$UserName,$password,$Catalog);
}
catch (Exception $e)
{
throw new Exception("Could not connect to the server. Your MySql server may not support this feature, try newer version of MySql Server");
}
$result=mysqli_query($linkIdentifier,$query);
mysqli_close($linkIdentifier);
if(!$result)
return false;
else
return $result;
}
/*
* this method connects mysql db
* using [mysql Improved] features.
* most of the times to work with
* stored rocedures you have to use mySqli
*/
public function iConnectDB()
{
try
{
$link = mysqli_connect($ServerName,$UserName,$password,$Catalog);
if(!$link)
{
return false;
}
else
{
return $link;
}
}
catch (Exception $ex)
{
return false;
}
}
}
?>
|
Download
