/PHP - Server/helper/DBOperation.php

<?php 
include("config.php");


//print_r($_SESSION);
/*
if(empty($_SESSION['userlog']['id'])){
			header("Location: login.php");
		}
*/
//require_once("../config/config.php");
/**
*	PHP Class for handling database operation with MySql Database.
*/
class DBOperation
{
	// Class Variable 
	var $dbCon;				//	Holds pointer to Database Connection
	var $operationError;	// 	Stores error occur during performing database operation.
	/*
		Contstructor Making connection with database using the default values.
	*/
	function DBOperation()	
	{
		
		$this->initializeConnection(DB_NAME, DB_HOST, DB_USERNAME, DB_PASSWORD);
		
		//$this->initializeConnection("DATABASE NAME", "SERVER NAME", "USER NAME", "PASSWORD");
	}
	
	/*
		Function with parameters DatabaseName, MySQL ServerName, MySQL User, Password to access user previledges.
		Make connection with database using the parameters.
	*/	
	function initializeConnection($dbName, $server, $userName, $password)
	{	
		
		// Get Connection
		$this->dbCon = mysql_connect($server, $userName, $password) or die("Cannot create connection");
		// Select a database to work on.
		mysql_select_db($dbName, $this->dbCon) or die("not connected");
	}
	/**
		Function to Create table.
		Parameter 
			$tableName : Name of table to create
			$fieldValueType : Array with keyvalue pair of Column Name and their Datatype.
		Check for isError after calling function to get result of operation.	
	*/
	function createTable($tableName,$fieldValueType)
	{
		$this->operationError = "";			
		$nameType = "";
		
		while($array_cell = each($fieldValueType))
		{
			$nameType = $nameType . "`" . $array_cell['key'] . "` " . $array_cell['value'] . ",";
		}
		$nameType = substr($nameType,0,strlen($nameType)-1);	
		$query = "create table $tableName ($nameType)";

		$result = mysql_query($query);
		
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}	
	}
	/**
		Function to drop table
		Parameter 
			$tableName : Name of table to be dropped.
		Check for isError after calling the function to get result of operation.	
	*/
	function removeTable($tableName)
	{
		$this->operationError = "";
		mysql_query("drop table $tableName");
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}	
	}
	
	/**
		Function to insert record into table
		Parameter 
			$tableName : Name of table to be dropped.
			$nameValueArray : ColumnName and Corresponding values to be inserted.
			User "'" with values for string type column e.g. "'New Record Field'".
		Check for isError after calling the function to get result of operation.
	*/
	function insertRecords($tableName, $nameValueArray)
	{
		$name = "";
		$values = "";
		$this->operationError = "";
		if(is_array($nameValueArray))
		{
			while($array_cell = each($nameValueArray))
			{
				$name = $name . "`" . $array_cell['key'] . "`,";
				$values = $values ."'". $array_cell['value'] . "',";
			}
			$name = substr($name,0,strlen($name)-1);	
			$values = substr($values,0,strlen($values)-1);
			$query = "insert into $tableName ($name) values($values)";
            
            //echo "<BR><BR>Query =".$query."<BR><BR>";
            
			$result = mysql_query($query);
			return $result;
		}
		else
		{
			$this->operationError = "No value to insert";
		}
	}
	/*
	Function to remove records from table
		Parameter 
			$tableName : Name of table to be dropped.
			$nameValueConditionArray : Name of columns and correponding values for limiting the deletion of records
			$flagOrAnd :  0 => OR, 1=> AND
		Check for isError after calling the function to get result of operation.	
	*/	
	function deleteRecords($tableName, $nameValueConditionArray, $flagOrAnd)
	{	
		$currentName = "";
		$currentValue = "";
		$this->operationError = "";
		$condition = "";
		if(is_array($nameValueConditionArray))
		{
			while($array_cell = each($nameValueConditionArray))
			{
				$condition = "`" . $array_cell['key'] . "`" . " = "  . $array_cell['value'];
			}
		}	
		if($condition == "")
			$query = "delete from $tableName";
		else
			$query = "delete from $tableName where $condition ";	
		//print($query);
		$result = mysql_query($query);
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}
	}
	
	/*
	Function to update records of table
		Parameter 
			$tableName : Name of table to be dropped.
			$nameValueArray : Name of columns and correponding values to update
			$conditionArray : Name of columns and correponding values for limiting the updation of records
			$flagOrAnd :  0 => OR, 1=> AND
		Check for isError after calling the function to get result of operation.	
	*/
	function updateRecords($tableName, $nameValueArray, $conditionArray, $flagOrAnd)
	{			
		$setCondition = "";
		$this->operationError = "";
		while($array_cell = each($nameValueArray))
		{
			$setCondition = $setCondition . "`" . $array_cell['key'] . "` = " . $array_cell['value'] . ",";
		}
		$setCondition = substr($setCondition, 0, strlen($setCondition)-1);
			
		if(isset($flagOrAnd))
		{
			if($flagOrAnd == 1)
				$flagOrAnd = "and";
			else
				$flagOrAnd = "or";
		}
		else
			$flagOrAnd = "or";	

		$whereCondition = "";
		if (is_array($conditionArray))
		{
			while($array_cell = each($conditionArray))
			{
				$whereCondition = $whereCondition . " `" . $array_cell['key'] . "` = " . $array_cell['value'] . " " . $flagOrAnd;
			}
			$whereCondition = substr($whereCondition, 0, strlen($whereCondition) - strlen($flagOrAnd));
		}		
		if($whereCondition == "") 
		{
			$query = "update $tableName set $setCondition";
		}
		else
		{
			$query = "update $tableName set $setCondition where $whereCondition";
		}
		$result = mysql_query($query);
		
		//print("<BR><BR>".$query);
	
		
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			
			return;
		}
		
	}
	/*
		Function to get records from table
		Parameter 
			$tableName : Name of table to be dropped.
			$requiredFields : Name of columns for which data is to be fetched, leave empty if all columns are required
			$fieldNameCondition : Name of columns and correponding values for filtering the selection of records
			$limitFieldsCondition: limit the number of record to be fetched ( default = 20)
			$flagOrAnd :  0 => OR, 1=> AND	( default = 0 )
		Check for isError after calling the function to get result of operation.	
		Return-Type : recordset if query executed successfully.
	*/
	function getRecords($tableName, $requiredFields, $fieldNameCondition, $limitFieldsCondition, $flagOrAnd)
	{
		$this->operationError = "";

		if(isset($limitFieldsCondition) && $limitFieldsCondition !="")
		{
			$limitFieldsCondition = $limitFieldsCondition;	
		}
		else
		{
			$limitFieldsCondition = "";
		}		
		//if(isset($limitFieldsCondition) || $limitFieldsCondition == "")
		//	$limitFieldsCondition = "0, 20";
	
		$whereCondition = "";
		if(isset($flagOrAnd))
		{
			if($flagOrAnd == 1)
				$flagOrAnd = "and";
			else
				$flagOrAnd = "or";
		}
		else
			$flagOrAnd = "or";	
			
		if(is_array($fieldNameCondition)) 
		{
			while($array_cell = each($fieldNameCondition))
			{
				$colName = $array_cell['key'];
				if(strpos($colName," ",0) > 0)
					$colName = "`" . $array_cell['key']. "`";
				$whereCondition = $whereCondition . " " . $colName . " = " . $array_cell['value'] . " " . $flagOrAnd;
			}
			$whereCondition = substr($whereCondition, 0,(strlen($whereCondition) - strlen($flagOrAnd)));
		}

		$requireName = "";
		if(is_array($requiredFields))
		{
			while($array_cell = each($requiredFields))
			{
				$colName = $array_cell['value'];
				$colValue = $array_cell['key'];
				/*if(strpos($colName, " ", 0) > 0)
					$colName = "`" . $colName . "`";
				if(strpos($colValue, " ", 0) > 0)
					$colValue = "`" . $colValue . "`";
				*/		
				$requireName = $requireName . $colName . " as " . $colValue . ",";
			}
			if($requireName == "")
				$requireName = " *  ";
		}
		else
		{
			$requireName = " *  ";
		}
		
		//$whereCondition = substr($whereCondition,0,strlen($whereCondition)-1);	
		$requireName = substr($requireName,0,strlen($requireName)-1);
		if ($whereCondition == "") 
		{
			if ($limitFieldsCondition == "")
			{
				$query = "select $requireName from $tableName ";
			}
			else
			{
				$query = "select $requireName from $tableName limit $limitFieldsCondition";
			}
		}
		else
		{
			if ($limitFieldsCondition == "")
			{
				$query = "select $requireName from $tableName where $whereCondition ";
			}
			else
			{
				$query = "select $requireName from $tableName where $whereCondition limit $limitFieldsCondition";
			}		
			
		}
		//print("<BR>Query " . $query."<BR>");
	
		$result = mysql_query($query);
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}
		return $result;
	}
	
	/**
		Count the number of records fetched.
		Paramter : $recordset : Recordset
		Return-Type : Integer => Number of records.
	*/
	function recordCount($recordSet)
	{
		return mysql_num_rows($recordSet);
	}
	
	/**
		Check whether the recordset is empty or not.
		Paramter : $recordset : Recordset
		Return-Type : Boolean => false if records available.
	*/
	function isEmpty($recordSet)
	{
	//	return count($recordSet) == 0;
		return mysql_num_rows($recordSet) == 0;
	}
	
	/**
		Execute query
		Paramter : 
			$query => SQL-Text to perform database operation.
			$queryType => Type of query
		Return-Type : based on Result of operaion.
		Check for isError after calling the function
	*/
	function executeQuery($query,$queryType)
	{
		$this->operationError = "";
		$result = mysql_query($query);
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}
		$arr = mysql_fetch_array($result);
		
		return $arr;
	}
	
	/**
		Check whether there is error in sql operation performed.
		Return-Type : Boolean => true if error occurs.
	*/
	function isError()
	{
		return $this->operationError;
	}
	/**
		Print the error message
	*/
	function printError()
	{
		print "<br> Error : " .  $this->operationError;
	}
	
	/**
		Check whether the table is empty or not.
		Parameter : $tableName => Name of table.
		Return-Type : Boolean => True if table is empty
		Check for isError after calling the function
	*/
	function isTableEmpty($tableName)
	{
		$this->operationError = "";
		$query = "select count(*) as numCount from $tableName";
		$result = mysql_query($query);
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}
		return $result['numCount'] == 0;
	}
	
	
	function numField($result)
	{
		return	mysql_num_fields($result);
	} 

	/*
		Close database connection.
	*/
	function closeConnection()
	{	
		mysql_close($this->dbCon);
	}	
	
	function getInsertId()
	{
		return mysql_insert_id();
	
	}
	
	
//	function getLocateRecord($tableName, $requiredFields, $fieldNameCondition, $limitFieldsCondition, $flagOrAnd,$fieldName)
	function getLocateRecord($tableName,$requiredFields,$fieldNameCondition,$delimiter,$fieldName,$limitFieldsConditionString,$flagOrAnd)
	{
		$this->operationError = "";
		$searchWordString = "";
		$limitFieldsCondition="";
		
		$searchWordArr  = array();
		$searchWordArr = explode($delimiter,$fieldNameCondition);
		
		
		if(isset($limitFieldsConditionString) && $limitFieldsConditionString !="")
		{
			$limitFieldsCondition = $limitFieldsConditionString;	
		}
		else
		{
			$limitFieldsCondition = "";
		}
	
		$whereCondition = "";
		if(isset($flagOrAnd))
		{
			if($flagOrAnd == 1)
				$flagOrAnd = "and";
			else
				$flagOrAnd = "or";
		}
		else
			$flagOrAnd = "or";	
			
		if(is_array($searchWordArr)) 
		{
			$whereCondition = " Visible=1 and";
			//$whereCondition = $whereCondition." (Category_ID in ('Select Category_ID FROM category_info WHERE (Category_Visible = 1)'))) and";
			for($i=0;$i<sizeof($searchWordArr);$i++)
			{
				$searchWordString = $searchWordArr[$i];
				//$whereCondition = $whereCondition . " " . $colName . " = " . $array_cell['value'] . " " . $flagOrAnd;
				$whereCondition = $whereCondition . " Locate('".$searchWordString. "',".$fieldName.") ". $flagOrAnd;
			}
			$whereCondition = substr($whereCondition, 0,(strlen($whereCondition) - strlen($flagOrAnd)));
		}

		$requireName = "";
		if(is_array($requiredFields))
		{
			while($array_cell = each($requiredFields))
			{
				$colName = $array_cell['value'];
				$colValue = $array_cell['key'];
			}
			if($requireName == "")
				$requireName = " *  ";
		}
		else
		{
			$requireName = " *  ";
		}
		
		//$whereCondition = substr($whereCondition,0,strlen($whereCondition)-1);	
		$requireName = substr($requireName,0,strlen($requireName)-1);
		if ($whereCondition == "") 
		{
			if ($limitFieldsCondition == "")
			{
				$query = "select $requireName from $tableName";
			}
			else
			{
				$query = "select $requireName from $tableName limit $limitFieldsCondition";
			}
		}
		else
		{
			if ($limitFieldsCondition == "")
			{
				$query = "select $requireName from $tableName where $whereCondition";
			}
			else
			{
				$query = "select $requireName from $tableName where $whereCondition limit $limitFieldsCondition";
			}
		}
		//print("<BR>Query " . $query."<BR>");
	
		$result = mysql_query($query);
		if(mysql_errno($this->dbCon))
		{
			$this->operationError = mysql_error($this->dbCon);
			return;
		}
		return $result;
	
	}
}
?>