An extremely easy, speedy way to deal with a database from a PHP script. Results are read row by row, as you need them - less demand on memory than loading all result rows into a single monster array =P. You can seek back and forth for the result row you want at any time.
By : See Code
<?php
/*
## Simple Database Abstraction Layer 1.2.1 [lib.sdba.php]
## by Gabe Bauman <gabeb@canada.com>
## Wednesday, April 05, 2000
## extended by Michael Howitz <icemac@gmx.net>
## Thuesday, Jun 15, 2000
##
## extended by Dirk Howard <dirk@idksoftware.com>
## Tuesday, August 28, 2001
##
## Easy way to read and write to any (!) database.
## Subclasses for MySQL (CDBMySQL), Oracle (CDB_OCI8) and
## PostgreSQL (CDB_pgsql) have been written.
##
## Changes in 1.1 from 1.0:
## - added the optional $dbname parameter to the constructor
## of the base class. If specified, it calls SelectDB for you.
## - function results now use 1 and 0 rather than true or false.
## - minor efficiency fixes
##
## Changes in 1.2 from 1.1:
## - added support for: Commit, Rollback, SetAutoCommit
## - added subclass for oracle (OCI8) support
##
## Changes in 1.2.1 from 1.2:
## - added subclass for Postgresql (pgsql) support
##
## Usage:
##
## $sql = new CDB_OCI8 ($DB_HOST, $DB_USER, $DB_PASS);
## $sql -> Query("SELECT Lastname, Firstname FROM people");
## while ($sql -> ReadRow()) {
## print $sql -> RowData["Lastname"] . "," . $sql -> RowData["Firstname"] . "<br>\n";
## }
## $sql -> Close();
##
## If you use this software, please leave this header intact.
## Please send any modifications/additions to the author for
## merging into the distribution (like other DB subclasses!)
*/
class CDBAbstract {
var $_db_linkid = 0;
var $_db_qresult = 0;
var $_auto_commit = false;
var $RowData = array();
var $NextRowNumber = 0;
var $RowCount = 0;
function CDBAbstract () {
die ("CDBAbstract: Do not create instances of CDBAbstract! Use a subclass.");
}
function Open ($host, $user, $pass, $db = "", $autocommit = true) {
}
function Close () {
}
function SelectDB ($dbname) {
}
function Query ($querystr) {
}
function SeekRow ($row = 0) {
}
function ReadRow () {
}
function Commit () {
}
function Rollback () {
}
function SetAutoCommit ($autocommit) {
$this->_auto_commit = $autocommit;
}
function _ident () {
return "CDBAbstract/1.2";
}
}
class CDBMySQL extends CDBAbstract {
function CDBMySQL ($host, $user, $pass, $db = "") {
$this->Open ($host, $user, $pass);
if ($db != "")
$this->SelectDB($db);
}
function Open ($host, $user, $pass, $autocommit = true) {
$this->_db_linkid = mysql_connect ($host, $user, $pass);
}
function Close () {
@mysql_free_result($this->_db_qresult);
return mysql_close ($this->_db_linkid);
}
function SelectDB ($dbname) {
if (@mysql_select_db ($dbname, $this->_db_linkid) == true) {
return 1;
}
else {
return 0;
}
}
function Query ($querystr) {
$result = mysql_query ($querystr, $this->_db_linkid);
if ($result == 0) {
return 0;
}
else {
if ($this->_db_qresult)
@mysql_free_result($this->_db_qresult);
$this->RowData = array();
$this->_db_qresult = $result;
$this->RowCount = @mysql_num_rows ($this->_db_qresult);
if (!$this->RowCount) {
// The query was probably an INSERT/REPLACE etc.
$this->RowCount = 0;
}
return 1;
}
}
function SeekRow ($row = 0) {
if ((!mysql_data_seek ($this->_db_qresult, $row)) or ($row > $this->RowCount-1)) {
printf ("SeekRow: Cannot seek to row %d\n", $row);
return 0;
}
else {
return 1;
}
}
function ReadRow () {
if($this->RowData = mysql_fetch_array ($this->_db_qresult)) {
$this->NextRowNumber++;
return 1;
}
else {
return 0;
}
}
function Commit () {
return 1;
}
function Rollback () {
echo "WARNING: Rollback is not supported by MySQL";
}
function _ident () {
return "CDBMySQL/1.2";
}
}
class CDB_OCI8 extends CDBAbstract {
function CDB_OCI8($host, $user, $pass, $autocommit = true) {
$this->Open ($host, $user, $pass, "", $autocommit);
}
function Open($host, $user, $pass, $db = "", $autocommit = true) {
($this->_db_linkid = OCILogon($user, $pass, $host)) or die("Error on logon:
". OCIError());
$this->_auto_commit = $autocommit;
}
function Close() {
OCIFreeStatement($this->_db_qresult);
OCILogOff($this->_db_linkid) or die ("Error on logoff: ". OCIError());
}
function SelectDB($dbname) {
echo "CDB_OCI8 does not support SelectDB";
return 0;
}
function Query($querystr) {
($result = ociparse($this->_db_linkid, $querystr))
or die("Error in query: ". OCIError());
if ($this->_auto_commit) {
OCIExecute($result, OCI_COMMIT_ON_SUCCESS);
}
else {
OCIExecute($result, OCI_DEFAULT);
}
if ($result == 0) {
return 0;
}
else {
if ($this->_db_qresult)
OCIFreeStatement($this->_db_qresult);
$this->RowData = array();
$this->_db_qresult = $result;
$this->RowCount = OCIRowCount($this->_db_qresult);
if (!$this->RowCount) {
// The query was probably an INSERT/REPLACE etc.
$this->RowCount = 0;
}
return 1;
}
}
function SeekRow ($row = 0) {
die ("CDB_OCI8 does not support SelectDB");
}
function ReadRow() {
if(OCIFetchInto($this->_db_qresult, $this->RowData, OCI_ASSOC)) {
$this->NextRowNumber++;
return 1;
}
else {
return 0;
}
}
function Commit() {
OCICommit($this->_db_linkid);
}
function Rollback() {
OCIRollback($this->_db_linkid);
}
function _ident () {
return "CDB_OCI8/1.0";
}
}
class CDB_pgsql extends CDBAbstract {
var $_php_ver_major;
var $_php_ver_minor;
var $_php_ver_rel;
function CDB_pgsql($host, $user, $pass, $db, $autocommit = true) {
$this->Open( $host, $user, $pass, $db, $autocommit );
}
function Open ($host, $user, $pass, $db = "", $autocommit = true) {
list( $this->_php_ver_major,
$this->_php_ver_minor,
$this->_php_ver_rel ) = explode( ".", phpversion() );
($this->_db_linkid = @pg_connect( "host=$host password=$pass dbname=$db user=$user" )) or
die("Error on logon:");
}
function Close () {
pg_freeresult( $this->_db_qresult );
return pg_close( $this->_db_linkid );
}
function SelectDB ($dbname) {
echo "CDB_pgsql does not support SelectDB";
return 0;
}
function Query ($querystr) {
if (!$this->_auto_commit) {
@pg_exec( $this->_db_linkid, "BEGIN;" );
}
$result = pg_exec( $this->_db_linkid, $querystr );
if ($result == 0) {
return 0;
} else {
if ($this->_db_qresult)
@pg_freeresult( $this->_db_qresult );
$this->RowData = array();
$this->_db_qresult = $result;
$this->RowCount = @pg_numrows( $this->_db_qresult );
if (!$this->RowCount) {
// The query was probably an INSERT/REPLACE etc.
$this->RowCount = 0;
}
$this->NextRowNumber = 0;
return 1;
}
}
function SeekRow ($row = 0) {
$this->NextRowNumber = $row;
return 1;
}
function ReadRow ($arrType = PGSQL_ASSOC) {
if ($this->NextRowNumber >= $this->RowCount)
return 0;
if ($this->_php_ver_major > 3) {
if ($this->RowData = pg_fetch_array( $this->_db_qresult, $this->NextRowNumber, $arrType )) {
$this->NextRowNumber++;
return 1;
} else {
return 0;
}
} else {
if ($this->RowData = pg_fetch_array( $this->_db_qresult, $this->NextRowNumber )) {
$this->NextRowNumber++;
return 1;
} else {
return 0;
}
}
}
function Commit () {
return $this->Query("COMMIT;");
}
function Rollback () {
return $this->Query("ROLLBACK;");
}
function SetAutoCommit ($autocommit) {
$this->_auto_commit = $autocommit;
}
function _ident () {
return "CDB_pgsql/0.1";
}
}
/*
## Example
## $sql = new CDBMySQL("localhost", "username", "password", "dbname");
## $sql -> Query ("SELECT firstname, lastname FROM people");
## while ($sql -> ReadRow()) {
## echo $sql -> RowData["lastname"] . ", ";
## echo $sql -> RowData["firstname"] . "<br>";
## }
*/
?>
| 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!
|
Secure your Web applications with IBM Rational AppScan Standard Edition V7.7, previously known as Watchfire AppScan. This Web application security testing tool automates vulnerability assessments and scans and tests for common Web application vulnerabilities. Visit IBM developerWorks to download a free trial of IBM Rational AppScan Standard Edition V7.7.
FREE! Go There Now!
|
|
|
|
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
FREE! Go There Now!
|
|
|
|
Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
FREE! Go There Now!
|
|
|
|
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
|
|
|
|
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
|
|
|
|
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial of the Rational Host Access Transformation Services (HATS) Toolkit. The HATS toolkit provides a set of plug-ins for the IBM Rational Software Delivery Platform to help you easily extend your legacy applications. HATS makes your 3270 and 5250 applications available as HTML through the most popular Web browsers, while converting your host screens to a Web look and feel and it also enables you to develop new Web, portal, and rich-client applications.
FREE! Go There Now!
|
|
|
|
Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
