Class that provides an abstraction layer above the PHP PostgreSQL API. This centralizes all the database connection info such as host, port, username and password. Same class can be used to connect to multiple databases, local and remote, on different ports.
In addition to "normal" stuff that you'd expect from a db abstraction layer, it also supports transactions and comprehensive error checking/management.
By : tmchow
<?php
///////////////////////
// Author: Trevin Chow
// Email: t1@mail.com
// Date: February 21, 2000
// Last Updated: August 14, 2001
//
// Description:
// Abstracts both the php function calls and the server information to POSTGRES
// databases. Utilizes class variables to maintain connection information such
// as number of rows, result id of last operation, etc.
//
// Sample Usage:
// include("include/dblib.php");
// $db = new phpDB();
// $db->connect("foobar");
// $db->exec("SELECT * from TREVIN");
// while ($db->nextRow()) {
// $rs = $db->fobject();
// echo "$rs->description : $rs->color : $rs->price <br>\n";
// }
//
// Modification History:
// Modification History:
// - v1.04b, 08/11/2001, Trevin Chow, t1@mail.com
//
// Fixed following bugs:
// * added in call to moveFirst() in exec() to ensure that on subsequent calls to exec
// we are moving row pointer to beginning again. Previously, row pointer wasn't being
// updated.
//
// - v1.04a, 08/09/2001, Trevin Chow, t1@mail.com
//
// Fixed following bugs introduced in v1.03:
// * connect() function had problem with setting password. Instead of using $this->password, was using $this->userName again for some reason.
//
// - v1.04, 07/29/2001, Trevin Chow, t1@mail.com
//
// Added Following function(s):
// * currRow() - return current row
//
// - v1.03, 05/18/2001, Lee Pang, wleepang@hotmail.com
//
// Added the following functions:
// * moveNext() - same as nextRow(), better syntax for VB/ASP converts like myself.
// * movePrevious() - like nextRow(), just in the opposite direction.
// * recordCount() - same as numRows(), better syntax for VB/ASP converts
// * columnCount() - same as numFields, better syntax
// * querySafe() - removes "\r" and "\n" and replaces "\'" with "'" in input query
// * sqlSafe() - replaces "\'" with "\'\'"
//
// Added more comprehensive error handling:
// * internal error code $errorCode
// * in connect()
// * in errorMsg()
//
// Modified following functions:
// * connect() - generates connection string based on available data
//
// Fixed the following bugs:
// * Syntax error in numAffected() - if ($this->result = null) ... to if ($this->result == null) ...
///////////////////////
class phpDB {
// set when connect() is called, defined in set_db_info()
var $hostName = '';
var $port = '';
var $userName = '';
var $password = '';
var $databaseName = '';
var $connectionID = -1;
var $row = -1; // a row counter, needed to loop through records in postgres.
var $result = null; // point to result set.
var $errorCode = 0; // internal error code
////////////////////////////////////////////
// Core primary connection/database function
////////////////////////////////////////////
// Set appropriate parameters for database connection
function set_db_info($DataBaseReference){
switch ($DataBaseReference){
case "someName":
$this->hostName = "localhost";
$this->port = "5432";
$this->userName = "nobody" ;
$this->password = "";
$this->databaseName = "someDBname";
break;
case "test":
$this->hostName = "";
$this->port = "";
$this->userName = "";
$this->password = "" ;
$this->databaseName = "test";
break;
default:
// FATAL ERROR - DB REFERENCE UNDEFINED
}
}
// connection function
function connect($DataBaseReference){
if (isset($DataBaseReference)) {
$this->set_db_info($DataBaseReference);
// build connection string based on internal settings.
$connStr = '';
($this->hostName != '') ? ($connStr .= "host=" . $this->hostName . " ") : ($connStr = $connStr);
($this->port != '') ? ($connStr .= "port=" . $this->port . " ") : ($connStr = $connStr);
($this->databaseName != '') ? ($connStr .= "dbname=" . $this->databaseName . " ") : ($connStr = $connStr);
($this->userName != '') ? ($connStr .= "user=" . $this->userName . " ") : ($connStr = $connStr);
($this->password != '') ? ($connStr .= "password=" . $this->password . " ") : ($connStr = $connStr);
$connStr = trim($connStr);
$connID = @pg_connect($connStr);
if ($connID != "") {
$this->connectionID = $connID;
$this->exec("set datestyle='ISO'");
$this->exec("set time zone 'PST'");
return $this->connectionID ;
} else {
// FATAL ERROR - CONNECTI0N ERROR
$this->errorCode = -1;
$this->connectionID = -1;
return 0;
}
} else {
// FATAL ERROR - FUNCTION CALLED WITH NO PARAMETERS
$this->connectionID = -1;
return 0;
}
}
// standard method to close connection
function close() {
if ($this->connectionID != "-1") {
$this->RollbackTrans(); // rollback transaction before closing
$closed = pg_close($this->connectionID);
return $closed;
} else {
// connection does not exist
return null;
}
}
// function to execute sql queries
function exec($query){
if ($this->connectionID != "-1") {
$this->result = @pg_exec($this->connectionID, $query);
if ($this->numRows() > 0) $this->moveFirst();
return $this->result;
}
else return 0;
}
// get last error message for db connection
function errorMsg() {
if ($this->connectionID == "-1") {
switch ($this->errorCode) {
case -1:
return "FATAL ERROR - CONNECTION ERROR: RESOURCE NOT FOUND";
break;
case -2:
return "FATAL ERROR - CLASS ERROR: FUNCTION CALLED WITHOUT PARAMETERS";
break;
default:
return null;
}
} else {
return pg_errormessage($this->connectionID);
}
}
////////////////////
// Cursor movement
////////////////////
// move pointer to first row of result set
function moveFirst() {
if ($this->result == null) return false;
else {
$this->setRow(0);
return true;
}
}
// move pointer to last row of result set
function moveLast() {
if ($this->result == null) return false;
else {
$this->setRow($this->numRows()-1);
return true;
}
}
// point to the next row, return false if no next row
function moveNext() {
// If more rows, then advance row pointer
if ($this->row < $this->numRows()-1) {
$this->setRow($this->row +1);
return true;
}
else return false;
}
// point to the previous row, return false if no previous row
function movePrevious() {
// If not first row, then advance row pointer
if ($this->row > 0) {
$this->setRow($this->row -1);
return true;
}
else return false;
}
// point to the next row, return false if no next row
function nextRow() {
// If more rows, then advance row pointer
if ($this->row < $this->numRows()-1) {
$this->setRow($this->row +1);
return true;
}
else return false;
}
// can be used to set a pointer to a perticular row
function setRow($row){
$this->row = $row;
}
///////////////////////
// Result set related
///////////////////////
// used to pull the results back
function fobject() {
if ($this->result == null || $this->row == "-1") return null;
else {
$object = pg_fetch_object($this->result,$this->row);
return $object;
}
}
// another method to obtain results
function farray(){
if ($this->result == null || $this->row == "-1") return null;
else {
$arr = pg_fetch_array($this->result,$this->row);
return $arr;
}
}
// return number of affected rows by a DELETE, UPDATE, INSERT
function numAffected() {
if ($this->result == null) return 0; // no result to return result from!
else return pg_cmdtuples ($this->result);
}
// get the number of rows in a result
function numRows(){
if ($this->result == null) return 0;
else {
$this->numrows = pg_numrows($this->result);
return $this->numrows;
}
}
// return current row
function currRow(){
return $this->row;
}
function recordCount() {
return $this->numRows();
}
// get the number of fields in a result
function numFields() {
if ($this->result == null) return 0;
else return pg_numfields ($this->result);
}
function columnCount() {
return $this->numFields();
}
// get last OID (object identifier) of last INSERT statement
function lastOID() {
if ($this->result == null) return null;
else return pg_getlastoid ($this->result);
}
// get result field name
function fieldname($fieldnum) {
if ($this->result == null) return null;
else return pg_FieldName($this->result, $fieldnum);
}
////////////////////////
// Transaction related
////////////////////////
function beginTrans() {
return @pg_exec($this->connectionID, "begin");
}
function commitTrans() {
return @pg_exec($this->connectionID, "commit");
}
// returns true/false
function rollbackTrans() {
return @pg_exec($this->connectionID, "rollback");
}
////////////////////////
// SQL String Related
////////////////////////
function querySafe($string) {
// replace \' with '
$string = str_replace("\'", "'", $string);
// replace line-break characters
$string = str_replace("\n", "", $string);
$string = str_replace("\r", "", $string);
return $string;
}
function sqlSafe($string) {
// replace \' with \'\'
// use this function only for text fields that may contain "'"'s
$string = str_replace("\'", "\'\'", $string);
return $string;
}
} // end class phpDB
?>
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More Database Code Articles
More By Codewalkers
 developerWorks - FREE Tools!
|
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
|
|
|
|
Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
FREE! Go There Now!
|
|
|
|
WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
FREE! Go There Now!
|
|
|
|
Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
FREE! Go There Now!
|
|
|
|
Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
FREE! Go There Now!
|
|
|
|
Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository.
FREE! Go There Now!
|
|
|
|
Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
FREE! Go There Now!
|
|
|
|
This Fall, IBM Rational talks to you directly through a special teleconference series giving you access to the best minds in IBM Rational - product experts and market thought leaders who will answer your questions during these pre-scheduled telephone conference calls. Register today!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for connectivity. The SOA Sandbox for connectivity provides a trial environment with the tooling and components to help you explore how to effectively connect your infrastructure and integrate all of the people, processes and information in your company. Use the hosted sandbox to explore SOA techniques that streamline connecting existing IT assets together, as well as learn how to connect them to new business logic.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
