<?php

/*/
	(c) 2000-2004 by Locke Enterprises (iLocke.com) - All rights reserved.
	License:  Authorized for use on the Windshiields To Go USA website(s).
	File:  oNDB.phi  [v1.01]
	Purpose:  Export Nathan's database connection class definition.
	Created:  Nathan - 6/14/2004
	Modification History:
		* 6/14/2004 - Nathan - ported old flat routines into an initial mysql version & tied it in with the nRS class from oNRS.phi
		* 6/28/2004 - Nathan - code adjustments
		* 7/3/2004 - Nathan - adjustments to getColumn() and added getColumns()
		* 7/7/2004 - Nathan - converted several functions to be able to return references
		* mm/dd/yyyy - [name] - [comment]
/*/


	define( "NDB_NO_QUOTES", 0 );  // do NOT add quotes for escape()
	define( "NDB_WITH_QUOTES", 1 );  // DO add quotes for escape()
	define( "NDB_AUTO_QUOTE", 2 );  // automatically add quotes for escape()

	if( !isset($NDB_Debugging) || $NDB_Debugging===null ) $NDB_Debugging = false;  // change this line to alter the default debug state

	return true;  // tell the includor that we are all OK


/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/


//-------------------------------------------------------------------------
// nDB:  Class modeled loosely after a ADODB.Connection and including Nathan's many handy data retrieval routines.
//-------------------------------------------------------------------------
class nDB {

	var $hDB = null;
	var $server = null;
	var $database = null;
	var $username = null;
	var $password = null;
	var $connected = null;
	var $affectedRows = null;
	var $escapeAddQuotes = NDB_WITH_QUOTES;
	var $MagicallyQuotedRequest = null;
	var $bHasErrTable = false;
	var $bLogErrors = false;
	var $bDebugging = false;


//-------------------------------------------------------------------------
// nDB:  Constructor. Can automatically open the connection.
//-------------------------------------------------------------------------
function nDB( $database=null, $username=null, $password=null, $server=null, $bDebug=false, $magicQuotes=false ) {

	if( $database!=null && $username!=null && $password!=null ) {
		$this->open( $database, $username, $password, $server );
	}
	$this->bDebugging = $bDebug===true || ($bDebug!==false && $NDB_Debugging===true);
	$this->MagicallyQuotedRequest = $magicQuotes;

} // nDB()


//-------------------------------------------------------------------------
// open:  Create the connection to the database and save it into $hDB.
//-------------------------------------------------------------------------
function open( $database, $username, $password, $server="localhost" ) {

	/// establish our variables...
	$this->server = ($server===null || $server=="") ? "localhost" : $server;
	$this->database = $database;
	$this->username = $username;
	$this->password = $password;
	//echo "opening nDB object ! - ".$this->server.":".$this->database.":".$this->username;

	/// make the connection...
        
	if( $this->hDB = mysql_connect( $this->server, $this->username, $this->password) ) {
		if( mysql_select_db($this->database, $this->hDB) ) {
			$this->connected = true;
		} else {
                    
			/// display an error? (ie: when debugging)
		}
	} else {
		/// display an error? (ie: when debugging)
	}
	return $this->connected;

} // open()


//-------------------------------------------------------------------------
// close:  Just close the database.
//-------------------------------------------------------------------------
function close( $database, $username, $password, $server="localhost" ) {

	if( !mysql_close( $this->hDB ) ) {
		/// display an error? (ie: when debugging)
	}
	$this->hDB = null;
	$this->connected = false;

} // close


//-------------------------------------------------------------------------
// error:  Returns true if there was an error.
//-------------------------------------------------------------------------
function error() {

	return mysql_errno($this->hDB) != 0;

}	// error()


//-------------------------------------------------------------------------
// message:  Returns an error message if present.
//-------------------------------------------------------------------------
function message() {

	return "<b>nDB</b> [MySQL] Error(".mysql_errno($this->hDB).") ".mysql_error($this->hDB)."<br>\n";

}	// message()


//-------------------------------------------------------------------------
// nRS:  Return an nRS object from the specified query on our connection.
//-------------------------------------------------------------------------
function nRS( $SQL ) {

	$oNRS = new nRS( $SQL, $this->hDB );
	return $oNRS;

} // getNRS()


//-------------------------------------------------------------------------
// escape:  Escapes the ticks, backslashes, nullchars, etc. and returns a commandstring-encoded version. Can also automatically add outer quotes. (&ref to minimize overutilizing memory)
//-------------------------------------------------------------------------
function & escape( $value, $quoteMode=NDB_AUTO_QUOTE ) {

	$refValue = "";
	if( $quoteMode==NDB_WITH_QUOTES || ($quoteMode==NDB_AUTO_QUOTE && $this->escapeAddQuotes==NDB_WITH_QUOTES) ) {
		$value = "'".addslashes($value)."'";
		$refValue =& $value;
		return $refValue;
	} else {
		$value =& addslashes($value);
		$refValue =& $value;
		return $refValue;
	}
	
} // escape()


//-------------------------------------------------------------------------
// getField:  Return the value of the specified field from the requested query -- or a specified default. (&ref to minimize overutilizing memory)
//-------------------------------------------------------------------------
function & getField( $field, $SQL="", $defaultValue = "!{:NONE:}" ) {

	/// reasonable default is to get first field...
	if( $SQL=="" ) {
		$SQL = $field;
		$field = 0;
	}

	/// attempt the query...
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultValue == "!{:NONE:}" ) {
			$defaultValue = "ERROR: getField(1)<br>\n";
			return $defaultValue;
		} else {
			return $defaultValue;
		}
	}
	
	/// determine the value retrieved...
	if( $rData = mysql_fetch_array($hData) ) {
		$fieldValue = $rData[$field];
	} else {
		if( $defaultValue == "!{:NONE:}" ) {
			$fieldValue = "ERROR: getField(2)<br>\n";
		} else {
			$fieldValue = $defaultValue;
		}
	}

	/// cleanup...
	mysql_free_result($hData);
	return $fieldValue;
	
} // getField()


//-------------------------------------------------------------------------
// getFields:  Return the values of the specified fields from the requested query -- or a specified default. (&ref to minimize overutilizing memory)
// Parameters: fieldsDelim = list of tilde~delimited fields to return from the query result array
//-------------------------------------------------------------------------
function & getFields( $fieldsDelim, $SQL="", $defaultArray=null ) {

	/// code for a smart function prototype with just the SQL string...
	if( $SQL == "" ) {
		$SQL = $fieldsDelim;
		$fieldsDelim = "";
	}
	
	/// attempt the query...
	$returnArray = array();
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultArray===null ) {
			$returnArray[] = "ERROR: getFields(1) - Bad query.<br>\n";
			return $returnArray;
		} else {
			return $defaultArray;
		}
	}
	if( mysql_num_rows($hData)<1 ) {
		if( $defaultArray===null ) {
			$returnArray[] = "ERROR: getFields(2) - No data values returned.<br>\n";
			return $returnArray;
		} else {
			return $defaultArray;
		}
	}

	/// get the data (method depends upon needed results); return default values when none found...
	if( $fieldsDelim!="" ) {
		$query = ($rData = mysql_fetch_array($hData, MYSQL_ASSOC));
	} else {
		$query = ($rData = mysql_fetch_array($hData, MYSQL_NUM));
	}
	if( !$query ) {
		if( $defaultArray===null ) {
			$returnArray[] = "ERROR: getFields(3) - Fetch error.<br>\n";
			return $returnArray;
		} else {
			return $defaultArray;
		}
	}

	/// amass and return the requested field values...
	if( $fieldsDelim != "" ) {
		$fields = split( "~", $fieldsDelim );
		foreach( $fields as $field ) {
			$returnArray[] = $rData[$field];
		}
	} else {  // just smartly return the whole first row of data...
		foreach( $rData as $value ) {
			$returnArray[] = $value;
		}
	}
	return $returnArray;

} // getFields()


//-------------------------------------------------------------------------
// getRow:  Return the first (only!) row array of the query specified. (&ref to minimize overutilizing memory)
//-------------------------------------------------------------------------
function & getRow( $SQL, $defaultValue="!{:NONE:}" ) {

	global $g_Config;

	/// attempt the query...
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultValue == "!{:NONE:}" ) {
			$rData = array("ERROR: getRow(1)<br><!--\n\n".$this->message()."\n\n".$SQL."\n\n-->\n");
		} else {
			$rData = array($defaultValue);
		}
		return $rData;
	}
	
	/// set up the row (or the default) as an array...
	if( !($rData = mysql_fetch_array($hData)) ) {
		if( $defaultValue == "!{:NONE:}" ) {
			$rData = array("ERROR: getRow(2)<br><!--\n\n".$this->message()."\n\n".$SQL."\n\n-->\n");
		} else {
			$rData = array($defaultValue);
		}
	} else {
		mysql_free_result($hData);
	}

	/// all done; return the data row...
	return $rData;
	
} // getRow()


//-------------------------------------------------------------------------
// getColumn:  Return an array of the requested field/column values from the query specified. Defaults to column index 0. (&ref to minimize overutilizing memory)
//-------------------------------------------------------------------------
function & getColumn( $field, $SQL="", $defaultValue=null ) {

	/// smart single-parameter prototyping...
	if( $SQL=="" ) {
		$SQL = $field;
		$field = 0;
	}

	/// attempt the query...
	$columnArray = array();
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultValue===null ) {
			$columnArray[] = "ERROR: getColumn(1)<!--\n".$this->message()."\n\n".$SQL."\n\n--><br>\n";
		} else {
			$columnArray[] = $defaultValue;
		}
		return $columnArray;
	}
	
	/// amass the column into a 1D array...
	while( $rData = mysql_fetch_array($hData, MYSQL_BOTH) ) {
		$columnArray[] = $rData[$field];
	}

	/// all done; return the column...
	mysql_free_result($hData);
	return $columnArray;

} // getColumn()


//-------------------------------------------------------------------------
// getColumns:  Return an associative array of the requested value field indexed by the specified index field. Defaults to index/value columns to indeces 0/1 respectively.
//-------------------------------------------------------------------------
function & getColumns( $keyField, $valueField=null, $SQL="", $defaultValue=null ) {

	/// smart prototyping for less parameters...
	if( $SQL=="" ) {
		$SQL = $keyField;
		if( isset($valueField) ) $defaultValue = $valueField;
		$keyField = 0;
		$valueField = 1;
	}

	/// attempt the query...
	$columnsArray = array();
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultValue===null ) {
			$columnsArray[] = "ERROR: getColumn(1)<!--\n".$this->message()."\n\n".$SQL."\n\n--><br>\n";
			return $columnsArray;
		} else {
			$columnsArray[] = $defaultValue;
			$refVariable = array($defaultValue);
			return $refVariable;
		}
	}
	
	/// associate the columns key=>value as requested...
	while( $rData = mysql_fetch_array($hData) ) {
		$columnsArray[$keyField] = $rData[$valueField];
	}

	/// all done; return the assoc array...
	mysql_free_result($hData);
	return $columnsArray;
	
} // getColumn()


//-------------------------------------------------------------------------
// getCount:  Return the count of rows matching the specified criteria.
//-------------------------------------------------------------------------
function getCount( $table, $where="" ) {

	$where = isset($where) && $where!="" ? "WHERE $where" : "";
	$SQL = "SELECT Count(*) FROM ".$table." ".$where;

	if( !($hData = mysql_query( $SQL, $this->hDB )) ) return "ERROR: getCount(1)<br>\n";
	$theCount = ($rData = mysql_fetch_array($hData)) ? $rData["Count(*)"] : "ERROR: getCount(2)<br>\n";
	mysql_free_result($hData);

	return $theCount;
	
} // getCount()


//-------------------------------------------------------------------------
// getFieldList:  Return the count of rows matching the specified criteria.
//-------------------------------------------------------------------------
function & getFieldList( $SQL, $defaultValue="!{:NONE:}" ) {

	/// attempt the query...
	$rArray = array();
	if( !($hData = mysql_query( $SQL, $this->hDB )) ) {
		if( $defaultValue == "!{:NONE:}" ) {
			$rArray[] = "ERROR: getField(1)<br>\n";
			return $rArray;
		} else {
			$rArray[] = $defaultValue;
			return $rArray;
		}
	}
	
	/// determine the value retrieved...
	while( $rData = mysql_fetch_row($hData) ) {
		$rArray[] = $rData[0];
	}

	/// all done...
	mysql_free_result($hData);
	return $rArray;
	
} // getFieldList()


//-------------------------------------------------------------------------
// execute:  Execute the statement on the database.
// Note: If the source file/routine is set, it is assumed that error reporting is on.
//-------------------------------------------------------------------------
function & execute( $SQL, $source="", $bShowError=true, $bLogError=false ) {

	global $PHP_SELF;
	

	$this->affectedRows = null;
	if( !mysql_query( $SQL, $this->hDB ) ) {
		if( isset($source)  &&  $source != "" ) {
			if( $bShowError ) {
				echo "<br><b><font color=#C00000>dbExecute ERROR:</font></b> ($PHP_SELF / $source) ".mysql_errno($this->hDB)." - ".mysql_error($this->hDB)."<br>\n<!--\n:\n".$SQL."\n:\n-->\n";
			}
			if( $bLogError ) {
				$CMD = "INSERT INTO err SET stamp=Now(), err=".$this->escape("$source: ERROR(".mysql_errno($this->hDB).") = ".mysql_error($this->hDB)."\n<br>\n".$SQL, NDB_WITH_QUOTES)."";
				mysql_query( $CMD, $this->hDB );
			}
		}
		$refVariable = false;
		return $refVariable;
	} else {
		$this->affectedRows = mysql_affected_rows($this->hDB);
		$refValue = true;
		return $refValue;
	}

} // execute()


//-------------------------------------------------------------------------
// dbExecute:  Legacy support.
//-------------------------------------------------------------------------
function & dbExecute( $SQL, $source="", $bShowError=true, $bLogError=false ) {

	return $this->execute( $SQL, $source, $bShowError, $bLogError );

} // dbExecute()


//-------------------------------------------------------------------------
// getLastInsertID:  A shortcut to get the last inserted id.
//-------------------------------------------------------------------------
function getLastInsertID( $table="" ) {
	
	if( $table ) {
		return intval( $this->getField(0,"SELECT Last_Insert_ID() FROM $table LIMIT 1") );
	} else {
		return intval( $this->getField(0,"SELECT Last_Insert_ID()") );
	}

} // getLastInsertID()


//-------------------------------------------------------------------------
// LogError:  Save the specified error into the 
//-------------------------------------------------------------------------
function LogError( $msg, $filename="", $routine="", $type="mysql" ) {

	global $PHP_SELF;
	
	if( $type=="mysql" ) {
		$SQL = "INSERT INTO err SET script=".$this->escape($PHP_SELF, NDB_WITH_QUOTES).", file=".$this->escape($filename, NDB_WITH_QUOTES).", routine=".$this->escape($routine, NDB_WITH_QUOTES).", err=".$this->escape(mysql_errno($this->hDB).": ".mysql_error($this->hDB)."\n<br>FROM SQL:\n<br>\n".$msg, NDB_WITH_QUOTES)."";
	} else {
		$SQL = "INSERT INTO err SET script=".$this->escape($PHP_SELF, NDB_WITH_QUOTES).", file=".$this->escape($filename, NDB_WITH_QUOTES).", routine=".$this->escape($routine, NDB_WITH_QUOTES).", err=".$this->escape($msg, NDB_WITH_QUOTES)."";
	}
	if( !mysql_query($SQL, $this->hDB) ) {
		echo "LogError() ERROR:<br><br>\n\n" . mysql_errno($this->hDB) . ": " . mysql_error($this->hDB)."\n\n<!--\n".$SQL."\n-->\n";  // double-safe error reporting...
	}

} // LogError()


//-------------------------------------------------------------------------
// LogError_old:  Old log the specified error into the err table.
//-------------------------------------------------------------------------
function LogError_old( $file, $routine, $msg="", $isMysql=false ) {

	global $PHP_SELF;
	
	if( $isMysql ) {
		$SQL = "INSERT INTO err SET stamp=Now(), script='".str_replace("'","''",$PHP_SELF)."', file='".str_replace("'","''",$file)."', routine='".str_replace("'","''",$routine)."', err='".str_replace("'","''",$msg)."'";
	} else {
		$SQL = "INSERT INTO err SET stamp=Now(), script='".str_replace("'","''",$PHP_SELF)."', file='".str_replace("'","''",$file)."', routine='".str_replace("'","''",$routine)."', err='MySQL INSERT ERROR.\n".mysql_errno($this->hDB)." = ".str_replace("'","''",mysql_error($this->hDB))."\n\nFROM SQL:\n".str_replace("'","''",$msg)."'";
	}
	mysql_query( $SQL, $this->hDB );

} // LogError_old()


//-------------------------------------------------------------------------
// getArrayFromQuery:  Returns an array of all of the rows of data from the specified query.
//-------------------------------------------------------------------------
function & getArrayFromQuery( $SQL ) {

	/// attempt the query...
	$theArray = array();
	if( !($hData = mysql_query( $SQL, $this->hDB)) ) {
		$theArray[] = "getArrayFromQuery() ERROR: ".$this->message();
		return $theArray;
	}

	/// retrieve all rows...
	while( $rData = mysql_fetch_array($hData) ) {
		$theArray[] = $rData;
	}
	
	/// all done...
	return $theArray;
	
} // getArrayFromQuery()


//-------------------------------------------------------------------------
// debugPrint: Print a message only if debugging is on.
//-------------------------------------------------------------------------
function debugPrint( $msg, $num=0 ) {

	if( !$this->bDebugging ) return false;
	echo "Error" . ($num==0 ? "" : "[".$num."]") . ": ".$msg."\nMySQL error (".mysql_error($this->hDB).") = ".mysql_error($this->hDB)."\nFrom query: '".$SQL."'<br>\n";

} // debugPrint()


} // nDB class