<?

/*

dblib.inc
database abstraction standard library by matto
for postgres, oracle, odbc, mysql
10/2001

DISCLAIMER: ------------------------------------------------------------------------

original version by matto
this software comes "as is" with no guarantees whatsoever.
feel free to do anything you like with your copy. 
but ...
!! please mail improvements & bugfixes to matto@progipark.com !!

CHANGELOG: -------------------------------------------------------------------------

19/11/02 added:		Oracle $DBNAME parameter
			db_column_query
01/08/02 correct:	postgres connection bug workaround
31/07/02 correct:	$_SERVER['PHP_SELF'] / $PHP_SELF (compatibility issue for PHP 4.2.2)
29/03/02 correct:	pass-by-reference in odbc functions
08/03/02 correct:	mysql connect, fetch
31/01/02 correct:	global in errorlog()
24/01/02 added:		no error logging if $LOGFILE is empty
			connect only once per script
11/01/02 added:   	errorlogging built into logfile ... only for oracle so far
			db_manipulate for downward compatibility in my own projects
18/12/01 correct: 	index increment for see below
13/12/01 correct: 	added "&& $assoc" at convert oracle keys to lowercase

TODO: ------------------------------------------------------------------------------

expand logfile functionality to other databases than oracle


DB MANIPULATION FUNCTIONS: ---------------------------------------------------------

array(array)	$data = db_query("select * from mytable");			returns 2-dim array, 1 array (=record) per element
array		$data = db_line_query("select * from mytable where id=1");	returns array = 1 record
string		$data = db_single_query("select name from mytable where id=1");	returns string = 1 field
		db_query("insert ...");						use db_query for other than select statements, too

db_query & db_line_query return records as enumerated arrays:		$name = $data[3][0];
db_aquery & db_line_aquery return records as associative arrays:	$name = $data[3]['name'];

array		db_column_query("select id,name from mytable");			returns 1-dim array (column) extracted from 2-dim result set
										if no 2nd parameter given: first column (column 0)
array		db_column_query("select id,name from mytable", 1);		returns column 1
array		db_column_query("select id,name from mytable", "name");		returns column named "name"


ERRORLOG FUNCTIONALITY: ------------------------------------------------------------

Oracle-only so far.
If a $LOGFILE is specified, all database errors will be logged in that file.

NO-ERRORS FUNCTIONALITY: -----------------------------------------------------------

If a $LIVE_HOST is specified, PHP will not print out any database errors when the scripts are called from that host.
(call it "presentation mode") :)

------------------------------------------------------------------------------------
*/

// General settings: ===============================================================
// Specify your database type here: Postgres, Oracle, ODBC or MySQL (case-sensitive)
$DB =		'MySQL';
$LOGFILE =	'errorlog.txt';

// postgres settings
$P_DBNAME =     '';
$P_HOST =       '';
$P_PORT =       '5432';

// oracle settings
$O_USER =       '';
$O_PASS =       '';
$O_DBNAME =	'';

// ODBC settings
$ODBC_DBNAME =  '';
$ODBC_USER =    '';
$ODBC_PASS =    '';

// mysql settings
$M_DBNAME =     '';
$M_USER =       'root';
$M_HOST =       'localhost';
$M_PASS =       '';

// don't show db errors on this host (e.g.: www)
$LIVE_HOST =        '';
// =================================================================================


// preparing constants --------------
// get hostname
$host = $HTTP_HOST;
$hilf = explode(".", $host);
$host = $hilf[0];

if($host == $LIVE_HOST)
        $errors = false;
else
        $errors = true;


// functions ------------------------
function db_query($sql, $assoc=false)
{
        global $DB, $errors, $conn;

        if($DB == 'Postgres')
                global $P_DBNAME, $P_HOST, $P_PORT;
        elseif($DB == 'Oracle')
                global $O_USER, $O_PASS, $O_DBNAME;
        elseif($DB == 'ODBC')
                global $ODBC_DBNAME, $ODBC_USER, $ODBC_PASS;
        elseif($DB == 'MySQL')
                global $M_USER, $M_HOST, $M_PASS, $M_DBNAME;
        else
                die("db_query: No valid database type specified.");

        // connect ----------
	if(!$conn)
	{
	        if($DB == 'Postgres')
        	{
			if(is_null($conn))      // postgres connection bug workaround
			{
	                	if($errors)
        	                	$conn = pg_connect("host=$P_HOST dbname=$P_DBNAME port=$P_PORT");
	        	        else
        	        	        $conn = @pg_connect("host=$P_HOST dbname=$P_DBNAME port=$P_PORT");
			}
	        }
        	elseif($DB == 'Oracle')
	        {
        	        if($errors)
                	        $conn = ocilogon($O_USER, $O_PASS, $O_DBNAME);
	                else
        	                $conn = @ocilogon($O_USER, $O_PASS, $O_DBNAME);
	        }
        	elseif($DB == 'ODBC')
	        {
        	        if($errors)
                	        $conn = odbc_connect($ODBC_DBNAME, $ODBC_USER, $ODBC_PASS);
	                else
        	                $conn = @odbc_connect($ODBC_DBNAME, $ODBC_USER, $ODBC_PASS);
	        }
        	elseif($DB == 'MySQL')
	        {
        	        if($errors)
                	{
                        	$conn = mysql_connect($M_HOST, $M_USER, $M_PASS);
	                        mysql_select_db($M_DBNAME, $conn);
        	        }
                	else
	                {
        	                $conn = @mysql_connect($M_HOST, $M_USER, $M_PASS);
                	        @mysql_select_db($M_DBNAME, $conn);
	                }
        	}
	}

        if(!$conn)
                die("<b>Error:</b> Database not available");

        // prepare ----------
        if($DB == 'Oracle')
        {
                if($errors)
                        $stmt = ociparse($conn, $sql);
                else
                        $stmt = @ociparse($conn, $sql);
        }

        // execute ----------
        if($DB == 'Postgres')
        {
                if($errors)
                        $stmt = pg_exec($conn, $sql);
                else
                        $stmt = @pg_exec($conn, $sql);
        }
        elseif($DB == 'Oracle')
        {
                if($errors)
                {
                        $stmt = ociparse($conn, $sql);
                        ociexecute($stmt);
	                $err = OCIError($stmt);
        	        $errm = $err["message"];
                	if($errm)
                        	errorlog($sql."\n".$errm);
                }
                else
                {
                        $stmt = @ociparse($conn, $sql);
                        @ociexecute($stmt);

	                $err = OCIError($stmt);
        	        $errm = $err["message"];
                	if($errm)
                        	errorlog($sql."\n".$errm);
                }
        }
        elseif($DB == 'ODBC')
        {
                if($errors)
                {
                        $stmt = odbc_exec($conn, $sql);
                }
                else
                {
                        $stmt = odbc_exec($conn, $sql);
                }
        }
        elseif($DB == 'MySQL')
        {
                if($errors)
                {
                        $stmt = mysql_query($sql, $conn);
                }
                else
                {
                        $stmt = @mysql_query($sql, $conn);
                }
        }

        // read in data (only if SELECT) -----
        if(strtolower(substr($sql, 0, 6)) == 'select')
        {
                $zeilen = array();
                $result = array();

                if($DB == 'Postgres')
                {
                        if($assoc)
                                $type = PGSQL_ASSOC;
                        else
                                $type = PGSQL_NUM;

                        if($errors)
                                $anz = pg_numrows($stmt);
                        else
                                $anz = @pg_numrows($stmt);

                        for($i=0; $i<$anz; $i++)
                        {
                                if($errors)
                                        $result = pg_fetch_array($stmt, $i, $type);
                                else
                                        $result = @pg_fetch_array($stmt, $i, $type);

                                $zeilen[$i] = $result;
                                $result = array();
                        }

                        //pg_close($conn);
                }
                elseif($DB == 'Oracle')
                {
                        if($assoc)
                                $type = OCI_ASSOC;
                        else
                                $type = OCI_NUM;

                        $i = 0;
                        if($errors)
                        {
                                while(OCIFetchInto($stmt, &$result, $type))
                                {
                                        $zeilen[$i] = $result;
                                        $result = array();
                                        $i++;
                                }
                                //ocilogoff($conn);
                        }
                        else
                        {
                                while(@OCIFetchInto($stmt, &$result, $type))
                                {
                                               $zeilen[$i] = $result;
                                               $result = array();
                                        $i++;
                                }
                                //@ocilogoff($conn);
                        }

                }
                elseif($DB == 'ODBC')
                {
                        if($errors)
                        {
                                $i = 0;
                                if($assoc)
                                {
                                        while($result = odbc_fetch_array($i, $stmt))
                                        {
                                                       $zeilen[$i] = $result;
                                                       $result = array();
                                                $i++;
                                        }
                                }
                                else
                                {
                                        while(odbc_fetch_into($stmt, &$result))
                                        {
                                                       $zeilen[$i] = $result;
                                                       $result = array();
                                                $i++;
                                        }
                                }
                        }
                        else
                        {
                                $i = 0;
                                if($assoc)
                                {
                                        while($result = @odbc_fetch_array($i, $stmt))
                                        {
                                                       $zeilen[$i] = $result;
                                                       $result = array();
                                                $i++;
                                        }
                                }
                                else
                                {
                                        while(@odbc_fetch_into($stmt, &$result))
                                        {
                                                       $zeilen[$i] = $result;
                                                       $result = array();
                                                $i++;
                                        }
                                }
                        }

                        //odbc_close($conn);
                }
                elseif($DB == 'MySQL')
                {
                        if($assoc)
                                $type = MYSQL_ASSOC;
                        else
                                $type = MYSQL_NUM;

                        if($errors)
                        {
				$i = 0;
                                while($result = mysql_fetch_array($stmt, $type))
                                {
                                        $zeilen[$i] = $result;
                                        $result = array();
					$i++;
                                }

                                //mysql_close($conn);
                        }
                        else
                        {
				$i = 0;
                                while($result = @mysql_fetch_array($stmt, $type))
                                {
                                        $zeilen[$i] = $result;
                                        $result = array();
					$i++;
                                }

                                //@mysql_close($conn);
                        }
                }

                // Oracle: convert keys to lowercase
                if($DB == 'Oracle' && $assoc)
                {
                        $zeilen2 = array(array());
                        $i = 0;
                        foreach($zeilen as $zeile)
                        {
                                while(list($key, $val) = each($zeile))
                                {
                                        $zeilen2[$i][strtolower($key)] = $val;
                                }
				$i++;
                        }

                        $zeilen = array();
                        $zeilen = $zeilen2;
                }

                return $zeilen;
        }
        else
        {
                // if not a SELECT
                return $stmt;
        }
}

function db_line_query($sql)
{
        $result = db_query($sql);

        if(is_array($result))
                return $result[0];
        else
                return $result;
}

function db_single_query($sql)
{
        $result = db_line_query($sql);

        if(is_array($result))
                return $result[0];
        else
                return $result;
}

function db_aquery($sql)
{
        return db_query($sql, true);
}

function db_line_aquery($sql)
{
        $result = db_aquery($sql);

        if(is_array($result[0]))
                return $result[0];
        else
                return $result;
}

function db_single_aquery($sql)
{
        $result = db_line_aquery($sql);

        if(is_array($result[0]))
                return $result[0];
        else
                return $result;
}

function db_column_query($sql, $index=0)
{
	$resarray = array();

	if(is_numeric($index))
		$data = db_query($sql);
	else
		$data = db_aquery($sql);

	for($i=0; $i<count($data); $i++)
	{
		$resarray[] = $data[$i][$index];
	}

	return $resarray;
}

// odbc specific (workaround)
function odbc_fetch_array($rownum, $res)
{
        $i = 0;
        $fCount = odbc_num_fields($res);
        odbc_fetch_row($res, $rownum);

        while($i < $fCount)
        {
                $i++;
                $fName = odbc_field_name($res, $i);
                $myrow[$fName] = odbc_result($res, $i);
        }

        $i=0;
        return $myrow;
}

function errorlog($text)
{
	global $LOGFILE, $PHP_SELF;

	// compatibility between php versions
	if($PHP_SELF == '')
		$PHP_SELF = $_SERVER['PHP_SELF'];

	if($LOGFILE != '')
	{
	        global $QUERY_STRING, $LOGFILE;

        	$entry = date("[d.m.Y H:i:s]")." ".$PHP_SELF."?$QUERY_STRING :\n$text\n";

	        $bisher = @file($LOGFILE);
        	array_push($bisher, $entry);

	        $fp = @fopen($LOGFILE, "w+");
        	@fwrite($fp, implode("", $bisher));
	        @fclose($fp);
	}
}

// for downward compatibility in my own projects
function db_manipulate($sql)
{
	db_query($sql);
}

?>