Location: PHPKode > scripts > DatabaseAccess > databaseaccess/class.DatabaseAccess.inc.php
<?php

require_once(ADODB_INCLUDE_ROOT.'/adodb/adodb.inc.php');
require_once(ADODB_INCLUDE_ROOT.'/adodb/rsfilter.inc.php');
require_once(ADODB_INCLUDE_ROOT.'/adodb/toexport.inc.php');

/** @file class.DatabaseAccess.inc.php
    @class DatabaseAccess

        This class is used to access DB-related functions. The functions use DB config values which have been included in hrms_header.inc.php.

*/

class DatabaseAccess {

var $_db;

/** This is the constructor method for this class.

        The constructor method is used to instantiate the DBObject.

        @return The static DB object is returned.

*/

function DatabaseAccess() {

	$this->_instantiateDBObject();

}

/** This is a private method to instantiate a DB static object.

        This is private method that will instantiate a DB static object that will be then used by all the functions within this class. The static object allows for transactions to be carried out. trigger_error() will be called if the function is unable to connect to the DB.

*/

function _instantiateDBObject() {

	global $dbhost, $dbuser, $dbpass, $dbname, $dbversion;
	
//	$this->_db = & $GLOBALS["db"];
	$class =& get_class($this->_db);

	if (!preg_match( "/adodb_/", $class)) {

		$this->_db= NewADOConnection($dbversion);

		if (!($this->_db->Connect($dbhost,$dbuser,$dbpass,$dbname)))
			trigger_error("Unable to connect to the database. ", E_USER_ERROR);

	}		
}

/** This method starts a DB transaction.

	This method is used in conjunction with endTransaction.

*/

function startTransaction() {
	
	$this->_instantiateDBObject();
	$this->_db->BeginTrans();

}

/** This method ends a DB transaction.

        This method is used in conjunction with startTransaction.

	@param result A boolean value. If TRUE, the transaction is committed. Else, the transaction is rolledback.

*/

function endTransaction($result) {

	$this->_instantiateDBObject();
	$this->_db->CommitTrans($result);
	
}

/** This method executes a direct SQL query.

        @param query A string containing the SQL query.
        @param debug A boolean value which indicates whether debug information should be printed.
	@return The resultset is returned if the query is successful, else a boolean FALSE is returned.

*/

function execute($query, $debug=FALSE) {

	global $ADODB_FETCH_MODE ;
	$ADODB_FETCH_MODE =ADODB_FETCH_ASSOC;
	$this->_instantiateDBObject();

	if ($debug || SQL_DEBUG)
    		echo "SQL Query Being executed:".$query."\n";
	
	$rs = $this->_db->Execute($query);

	if ($rs === FALSE)
		return FALSE;

	else 
		return $rs;

}

/** Method to replace database records.

        This method will perform an ADOdb replace operation. The replace operation will look at the database records and determine whether an INSERT operation or an UPDATE operation is required. Multi-table operations are not supported yet. 

TODO - figure out how to work with ADOdb replace feature to do multi table replace.

        @param tableName A string containing the name of the table on which the database operation should be carried out.
        @param dataArray An associative array which contains the information that is to be inserted.
	@param whereArray A numerical array containing the restrictions for the WHERE portion of the database query of the table.
	@param returnADOdbValue A boolean value that states whether, instead of return boolean TRUE or boolean FALSE, the operation will return the ADOdb return values (0/1/2). Read the ADOdb manual for more details on the return values.
        @param debug A boolean value which indicates whether debug information should be printed.
	@return If the parameter returnADOdbValue is TRUE, then the value returned is the ADOdb return values (read the manual for more details on this). Otherwise, a boolean TRUE will be returned if the update or insert works correctly and a boolean FALSE will be returned if the update or insert did not succeed.

*/

function replace($tableName, $dataArray, $whereArray, $returnADOdbValue=FALSE, $debug=FALSE) {

	$this->_instantiateDBObject();

	if ($debug || SQL_DEBUG)
		$this->_db->debug = true;

	$val=$this->_db->Replace($tableName, $dataArray, $whereArray, TRUE);

	if ($returnADOdbValue)
		if ($val == "0")
			return FALSE;
		else
			return $val;

	if ($val == 1 || $val==2)
		return TRUE;

	else
		return FALSE;

}

/** Method to insert database records.

	This method will perform an INSERT operation only. Currently it only supports tables with no primary key or a single column primary key (ie no composite key). It supports auto incrementing, which is abstracted using ADOdb's auto increment abstraction feature.

        @param tableName A string containing the name of the table on which the database operation should be carried out.
        @param dataArray An associative array which contains the information that is to be inserted. A special key, autoIncrementKey, will denote the column name that should be autoincremented. This key is only used is autoIncrement is a boolean TRUE.
        @param autoIncrement A boolean value which if TRUE will automatically increment the primary key of the table. If a special key autoIncrementKey exists, the array value for this key will be used as the column name to increment. Else, the default column name id will be incremented.
        @param debug A boolean value which indicates whether debugging information should be printed.
        @return If autoIncrement is a boolean TRUE, then a boolean TRUE is returned if the database operation was successful, FALSE otherwise.

*/

function insert($tableName, $dataArray, $autoIncrement=TRUE, $debug=false) {

	$this->_instantiateDBObject();

	if ($debug || SQL_DEBUG)
		$this->_db->debug = TRUE;
	
	if ($autoIncrement) {

		$id=$this->_db->GenID('sequence_'.$tableName, '2');

		if (isset($dataArray["autoIncrementKey"])) {

			$dataArray[$dataArray["autoIncrementKey"]]=$id;
			unset($dataArray["autoIncrementKey"]);

		} else 
			$dataArray["id"]=$id;

	}

	list($key, $value) = each ($dataArray);

	$query="SELECT * FROM $tableName WHERE $key=-1";
	$rs=$this->_db->execute($query);

	$insertSQL = $this->_db->GetInsertSQL($rs, $dataArray);

	if ($this->_db->Execute($insertSQL))
		if ($autoIncrement)
			return $id;
		else
			return TRUE;
		
	else 
		return FALSE;

}

/** Method to update database records

	This method will perform an UPDATE operation only. It will not work across multiple tables.

        @param tableName A string containing the name of the table on which the database operation should be carried out.
	@param dataArray An associative array which contains the information that is to be updated. The key holds the DB column name and the value contains the updated value of the column.
	@param whereArray An associative array containing the restrictions for WHERE.
	@param debug A boolean value which indicates whether debug information should be printed.
        @return A boolean TRUE is returned if the update operation was successful, FALSE otherwise.

*/

function update ($tableName, $dataArray, $whereArray="", $debug=FALSE) {

	$this->_instantiateDBObject();

	if ($debug || SQL_DEBUG)
		$this->_db->debug = TRUE;

	$rs = $this->select(array($tableName), "", $whereArray);

	if ($rs === FALSE)
		return FALSE;

	$updateSQL = $this->_db->GetUpdateSQL($rs, $dataArray);

	if ($updateSQL=="")
		return TRUE;

	return $this->_db->Execute($updateSQL);

}

/** Method to select database records.

        This method will perform a SELECT operation only.

	@param tableNames A numerical array containing the names of the tables for which the database operation should be carried out on.
	@param dataRequiredArray A numerical array containing the columns to be retrieved. 
	@param whereArray An associative array containing the restrictions for WHERE portion of the database query.
	@param relationArray An associative array containing the relational restrictions for the database query.
	@param debug A boolean value which indicates whether debug information should be printed
.
        @return The resultset is returned if the database operation was successful, FALSE otherwise.

*/

function select ($tableNames, $dataRequiredArray="", $whereArray="", $relationArray="", $debug=FALSE) {

	$this->_instantiateDBObject();

        if ($debug || SQL_DEBUG)
                $this->_db->debug = TRUE;

	$tableNames = implode(",", $tableNames);

	if ($dataRequiredArray == "")
		$dataRequiredArray = " * ";

	else
		$dataRequiredArray = implode(", ", $dataRequiredArray);

	if ($relationArray != "") {

		foreach($relationArray as $key => $value)
			$tempArray[] = $key."=".$value;

		$relationArray = implode(" AND ", $tempArray);

	}

	if ($whereArray != "") {

		unset($tempArray);

		foreach ($whereArray as $key=>$value) {

			if (is_array($value))
				foreach($value as $secondLevelKey => $secondLevelValue)
					$tempArray[] = $secondLevelKey."='".$secondLevelValue."'";

			else
				$tempArray[] = $key."='".$value."'";

		}

		$whereArray = implode(" AND ", $tempArray);
		$whereArray = " WHERE ".$whereArray;

		if ($relationArray != "")
			$whereArray = $whereArray." AND ";

	}

	$query = "SELECT ".$dataRequiredArray." FROM ".$tableNames." ".$whereArray." ".$relationArray;
	$rs = $this->_db->Execute($query);

	if ($rs !== FALSE)
		return $rs;

	else
		return FALSE;

}
	
/** Method to delete database records

        This method will perform a DELETE operation only.

        @param tableName A string containing the name of the table for which the database operation should be carried out on.
	@param whereArray An associative array containing the restrictions for WHERE portion of the database query.
	@param debug A boolean value which indicates whether debug information should be printed.
	@return A boolean TRUE is always returned. This is because a PHP FATAL ERROR would occur if wrong information such as a non-existant table name is used.

*/

function delete ($tableName, $whereArray="", $debug=FALSE) {

        $this->_instantiateDBObject();

        if ($debug || SQL_DEBUG)
                $this->_db->debug = true;

	$query = "DELETE FROM $tableName ";

	if ($whereArray != "") {

		foreach ($whereArray as $key => $value)
			$matchArray[] = $key."='".$value."'";

		$queryMatch .= implode (" AND ", $matchArray);
		$query=$query." WHERE ".$queryMatch;

	}


	$this->_db->Execute($query);
	return TRUE;

}

}

?>
Return current item: DatabaseAccess