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!
|
CakePHP is a stable production-ready, rapid-development aid for building Web sites in PHP. This "Cook up Web sites fast with CakePHP" series shows you how to build an online product catalog using CakePHP.
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!
|
|
|
|
Visit IBM developerWorks to download a free trial of the latest release of IBM Lotus Sametime Standard V8.0. Lotus Sametime Standard V8.0 is a platform for unified communications and collaboration that combines security features with an extensible, open solution including integrated Voice over IP, geographic location awareness, mobile clients, and a robust Business Partner community offering telephony and video integration.
FREE! Go There Now!
|
|
|
|
Whether you are creating new applications or modifying existing ones, managing integration of new components with traditional z/OS elements is a critical part of building and deploying modern applications. Listen to this webcast to see how IBM can help you optimize your development process using an IDE like Rational Developer for System z that integrates with management tools, such as ClearCase to manage your application development on mainframes.
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!
|
|
|
|
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!
|
|
|
|
Hold your calendar on January 30, 2008 for this free webcast on the new i5/OS. Rational's Enterprise Modernization products will be discussed at this webcast as they help to drive the application development environment for this new System i OS. <br />And learn how i5/OS will take you to the next step of efficient, resilient business processing. You will hear about the new i5/OS capabilities as it will be the most significant i5/OS release in years. If you cannot join the webcast on 1/30/08 you can still use this link to listen to the replay.<br />
FREE! Go There Now!
|
|
|
|
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!
|
|
|
|
As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security.
FREE! Go There Now!
|
|
|
|
Learn field-tested SOA principles, methodology, technology and implementation from the global SOA market leader - in a new e-book by an IBM SOA expert. Written by IBM Certified SOA Solution Designer Bobby Woolf, "Exploring IBM SOA Technology & Practice" is the ultimate insider's guide to SOA - a PDF e-book packed cover to cover with IBM's specific advice on how to make your SOA implementation a success.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
