<?php
/*
MySqlDatabase class is a lightweight class for accessing MySQL databases.

 The class instantiates a database connection from passed parameters and creates a $oDb object.
 
 $DB = new MySqlDatabase( db host, db username , db password, db name )
  
 You execute a query using this syntax:
 $DB->Query("SELECT foo FROM bar");

 for any query:
 $DB->Success() is true if, er the query succeeds.

 for selects:
 $DB->Rows gets you the number of rows returned
 $DB->Field($n, $colname) returns the item at row $n, $colname.
 $DB=>Row($n) returns an array containing the data for row $n

 For inserts:
 $DB->Success() is true if, er the query succeeds.
 $DB->InsertId returns the mysql_insert_id() if any.
 LoganC, Feb 2005.

***UPDATE March 2019 - updating the MySQL PHP connection methods to use MySQLi extnesion. 

- Significant changes to mysql call commands plus methods availabkle.
- Refactored some unecessary loops to preapre data structures loaded from MySQL. 
 
 */
 
 Class MySqlDatabase {
	
 	var $oDb;
 	var $bConnected;
 	var $bSuccess;
 	var $aFieldnames = array();
	var $iFields;
	var $aData;
	var $iRows;
	var $iInsertId;
	var $fStartTime;
	var $fFinishTime;
	var $Set = false;
	var $LOG;
 	
	function __construct($oLog, $sHost, $sUser, $sPass, $sName) {
		
		global $MAGIC_DEBUG;
		$this->LOG = $oLog;
		
		if($MAGIC_DEBUG) {
			$this->_TimerStart();
		}
		
		if(!$oDb = mysqli_connect($sHost . ":3306", $sUser, $sPass)) {
			$this->LOG->LogError("Failed to connect to MySQL database! - " . $sHost, E_USER_ERROR);
			$this->bConnected = false;
			return false;
		}
		
		if(!mysqli_select_db($oDb, $sName)) {
			$this->LOG->LogError("Unable to make SELECT on remote MySql database.", E_USER_WARNING);
			$this->bConnected = false;
			return false;
		}
		if($MAGIC_DEBUG) {
			$this->_TimerFinish();
			//$this->_TimerShow("Connected to MySQL DB in");
		}
		$this->oDb = $oDb;
		$this->bConnected = true;
		return true;
	}

	function Query ($sSql = '') {
		global $MAGIC_DEBUG;
		if(empty($sSql)) {
			$this->bSuccess = false;
			return false;
		}
		if(!$this->bConnected) {
			$this->bSuccess = false;
			return false;
		}
		
		// Magic_Debug.
		if($MAGIC_DEBUG) {
			$this->_TimerStart();
		}
		if(!$oQuery = mysqli_query($this->oDb, $sSql)) {
			$this->LOG->LogError("Query Failed! - " . $sSql, E_USER_ERROR);
			return false;
		}
		
		// Magic_Debug.
		if($MAGIC_DEBUG) {
			$this->_TimerFinish();
			$this->_QueryShow($sSql);
		}
		if((!$oQuery) or (empty($oQuery))) {
			$this->bSuccess = false;
			return false;
		} elseif (is_bool($oQuery)) {	
			// A successful query of a type *other* than
			// SELECT,SHOW,EXPLAIN or DESCRIBE
			// For INSERTs that have generated an id from an AUTO_INCREMENT column.
			$this->iInsertId = mysqli_insert_id($this->oDb);
			$this->bSuccess = true;
			return true;
		} else {
			// A successful SELECT,SHOW,EXPLAIN or DESCRIBE query.		
			$this->bSuccess = true;

			$aResult = array();
			//$aFieldnames = array();
			
			//for($t=0;$t<mysqli_num_fields($oQuery); $t++) {
			//	$aFieldnames[$t] = mysqli_fetch_field_direct($oQuery, $t)->name;
			//}
			//print_r($aFieldnames);
			
			while($row = mysqli_fetch_array($oQuery, MYSQLI_ASSOC)) {
				$aResult[] = $row;
			}

			//if(sizeof($aResult) > 0) {
			//	$this->iRows	= sizeof($aResult);
			//} else {
			//	$this->iRows	= 0;
			//}
			//$this->aFieldnames 	= $aFieldnames;
			//$this->iFields = sizeof($aFieldnames);

			//print_r($aResult);
			return $aResult;
		}
		
	}
	
	function Success() {
		return $this->bSuccess;
	}

	function Field($iRowIndex, $sColumnName) {
		$result = $this->RowArray($iRowIndex);
		return $result[$sColumnName];
	}

	function Row($iRowIndex) {
		return $this->aData[$iRowIndex];
	}
	
	function RowArray($iRowIndex) {
		$result = array();
		$fields = $this->data[$iRowIndex];
		foreach ($fields as $index => $data) {
			$fieldname = $this->aFieldnames[$index];
			$result[$fieldname] = $data;
		}
		return $result;
	}

	function _TimerStart() {
		list($usec, $sec) = explode(" ", microtime());
		$this->fStartTime = ((float)$usec + (float)$sec);
		$this->Set = true;
	}

	function _TimerFinish() {
		list($usec, $sec) = explode(" ", microtime());
		$this->fFinishTime = ((float)$usec + (float)$sec);
	}
	
	function _TimerShow($comment = "Script execution") {
		//print "<style type=\"text/css\">.report_timing {font:8pt Verdana}</style>";
		$timing[] = $this->fFinishTime - $this->fStartTime;
		if(count($timing) == 1) {
			print $comment . "&nbsp;" . sprintf('%01.6f', ($timing[0] * 1000)) . " msecs";
		} else {
			sort($timing);
			$ave = array_sum($timing)/count($timing);
			print "Script execution over " . count($timing) . " loops averages " . $ave . " seconds<br>
			Fastest: " . $timing[0] . " seconds<br>Slowest: " . $timing[count($timing)-1] . " seconds";
		}
	}
	function _QueryShow($query) {
		$timing[] = $this->fFinishTime - $this->fStartTime;
		print $query . "<br>Query execution " . sprintf('%01.6f', ($timing[0] * 1000)) . " msecs";
	}
	function _TimerSet() {
		return $this->Set;
	}
}

?>