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!
|
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
|
|
|
|
This whitepaper presents the benefits of successfully introducing static analysis into your organization using IBM Rational Software Analyzer. Additionally, it identifies some common pitfalls that can hinder the effective use of static analysis tooling as well as presents 10 simple strategies designed to help you quickly realize the value of static analysis using Rational Software Analyzer.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of WebSphere Business Modeler Advanced V6.1.1, IBM’s premier business process modeling and analysis tool for business users that offers process modeling, simulation, and analysis capabilities. IBM WebSphere Business Modeler helps you visualize, understand, and document business processes for continuous improvement.
FREE! Go There Now!
|
|
|
|
As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br />
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!
|
|
|
|
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!
|
|
|
|
Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Tester for SOA Quality V7.0.1, a functional and regression testing tool that enables the creation, comprehension, modification and execution of testing GUI-less Web services.
FREE! Go There Now!
|
|
|
|
You can now evaluate IBM Rational Asset Manager V7.0 online without installing or configuring it on your own system! Rational Asset Manager helps create, modify, govern, find, and reuse any type of development assets, including SOA and systems development assets. Rational Asset Manager helps you reduce software development costs and improve quality by facilitating the reuse of all types of software development-related assets. Visit developerWorks to learn more about this product and register to explore its capabilities online.
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!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
