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!
|
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
|
|
|
|
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
|
|
|
|
Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services.
FREE! Go There Now!
|
|
|
|
Learn how to do more with your reusable assets with the free Rational Asset Manager eKit. The eKit includes demos on how Rational Asset Manager tracks and audits your assets in order to utilize them for reuse. Plus you’ll find white papers and a Webcast that discuss the challenges of a Service Oriented Architecture and how Rational Asset Manager can provide quick and effective solutions.
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
FREE! Go There Now!
|
|
|
|
Join this Rational Talks to You teleconference on December 4 at 1:00 pm ET to discuss how Rational Method Composer can help meet your compliance objectives. Get your questions answered!
FREE! Go There Now!
|
|
|
|
Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
FREE! Go There Now!
|
|
|
|
Join the IBM Watchfire team for an informative discussion on techniques and best practices to proactively manage Web application security and how to effectively build application security testing into the software development lifecycle (SDLC). In this Software Delivery Platform webcast you will learn: How to better understand potential web application security vulnerabilities, best practices and how to effectively integrate application security testing into the software development lifecycle, the importance of detecting and removing software vulnerabilities during application development.
FREE! Go There Now!
|
|
|
|
User communities play an important role in communication and collaboration around products, solutions and other areas of special interest to members. Successful communities are able to provide the right mix of content and services to deliver a value proposition that resonates with each audience. Join Tom Inman, VP of Marketing for Information and Platform Solutions as he introduces the new LeverageINFORMATION community. During this webcast, learn about the value provided by the community and how customers and partners derive value from the community in addressing their own technical and business challenges.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
