Version: 1.3
Type: Function
Category: Databases
License: GNU General Public License
Description: Plug this include into your pages, configure the essentials (DB type: MySQL/Postgres/ODBC/Oracle; access data), and you can use simple db manipulation functions. Error printing can be turned off for a specific host; Oracle error logging provided.
Instructions inside. Please feel free to improve.
<? /* 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 [email protected] !! 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$textn"; $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); } ?>
