php.ComLanka.com PHP Tutorial ,PHP source code, PHP online example

Algorithms Arrays Passwords Uncategorized
Commerce Uncategorized
Databases Abstraction Layers Informix LDAP MS SQL MySQL ODBC PostgreSQL Uncategorized
Date & Time Calculators Conversion Translation Uncategorized
Discussion Chat Forums Guestbooks Polls Uncategorized
Email Validation Uncategorized
External Uncategorized
Files and Directories Uncategorized
Games Uncategorized
Graphics 3-D Graphs Resizing Thumbnails Uncategorized
HTML Forms Menus & Navigation Source Viewers Tables Templates Uncategorized
HTTP Authentication Cookies File Uploading Uncategorized
I18N Uncategorized
Math Uncategorized
Miscellaneous Uncategorized
Searching and Trees Uncategorized
Security Uncategorized
Text Uncategorized
Utilities Calendars Counters Link Checkers Weather Uncategorized
XML Uncategorized

php sample source codes
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. <br> <br> Includes an abstract base class, and a subclass to work with a MySQL db. Write your own subclass of CDBAbstract to work with any PHP-supported database software! <br> <br> <b>Changes in 1.2: (by Michael Howitz)</b><br> - added support for: Commit, Rollback, SetAutoCommit<br> - added subclass for oracle (OCI8) support<br> <br> <b>Changes in 1.2.1: (by Dirk Howard)</b><br> added subclass for Postgresql (pgsql) support<br> <?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>"; ## } */ ?>
Oracle Database abstract layer <? ################################ # Oracle Database Abastract Layer. v2.1 # Feranndo Labombarda - fllabombarda@hotmail.com ################################ # - EXAMPLE - # $db = new database(); # $rs = $db->Select("SELECT COL FROM TB_SECURITY"); # while ($db->Fetch($rs)) { # $value = $db->Result($rs,"COL"); # } # ################################ class database { var $DB_Name; // banco. var $DB_User; // usuario. var $DB_Pass; // senha. var $DB_ID; // conection_id. var $query; // Guarda query no m todo prepare var $result; // RESULT var $dbh; // Handle do banco var $erro; function database() { $this->dbh = $this->Connect(); } /** * @return void * @desc Destructor, when finish shutdown connection */ function _database() { $this->DBClose(); } /** * @return Connection * @desc Start DB Connection */ function Connect() { $this->DB_Name = ""; $this->DB_User = "portaleds"; $this->DB_Pass = "************"; $this->erro = "Error opening Database."; if (!$this->dbh) { return $this->dbh = @OCILogon( $this->DB_User, $this->DB_Pass, $this->DB_Name); } else { return $this->dbh; } } /** * @return null * @param String $query * @desc Log SQL commands into default error log file EX: Apache, "error_log" */ function logSQL($sql){ $fp = fopen("/tmp/sql.log","a+"); flock($fp, LOCK_EX); fwrite($fp,$sql.";\n"); fflush($fp); fclose($fp); } /** * @return resultset * @param String $query, connection $dbh * @desc Execute a SELECT querys */ function Select($query) { $result = OCIParse($this->dbh, $query); $this->logSQL($query); if (!OCIExecute($result)) error_log($_SERVER["PHP_SELF"]." S -> ".$query."\n",0); return $result; } /** * @return int rowcount * @param String $query * @desc Execute DML query into Database */ function Dml($query) { $rs = @OCIParse($this->dbh, $query); if (!OCIExecute($rs)) error_log($_SERVER["PHP_SELF"]." D -> ".$query,0); @OCICommit($this->dbh); $affected = @OCIRowcount($rs); $this->freeStatement($rs); return $affected; } /** * @return boolean * @paramr resultset $rs * @desc Close cursor, free resources */ function freeStatement($rs) { return ocifreestatement($rs); } /** * @return row * @param resultset $result * @desc Fetch ResultSet */ function Fetch($result) { $row=null; OCIFetchinto($result, $row); return $row; } function NumRow($result, $garbage) { $this->number = OCIFetchStatement($result, $garbage); return $this->number; } function Result($result, $CAMPO) { $this->result = @OCIResult($result, $CAMPO); return $this->result; } function NumCol($result) { $this->number = @OCINumCols($result); return $this->number; } /** * @return true * @desc Commit connection */ function Commit() { @OCICommit($this->dbh); return true; } /** * @return true * @desc Close DB Connection */ function DBClose() { @OCILogoff (); return true ; } function ServerVersion(){ return OCIServerVersion($this->dbh); } }
This is a class i have been working on. The most important things for me where portability and fast but reliable working.<br> I would very much appriciate any feedback and pointing out any bugs or bad programing decidions.<br> I allmost forgot: the class was tested with odbc under win2k and mysql under red hat 7.0. It would be nice if someone could test it with ms sql server (the support for it was inplemented just with the reffrence from the php manual).<br> I'm currently working on postgreSql support, so v1.1 could be expected soon. <?php // *********************************************************************************************************** // // Name: Database PHP Object // Version: 1.0 // Description: A PHP wrapper for working with various databases // Compatibility: PHP3, PHP4 // // Author: Simon Belak // E-mail: Simon_Belak@yahoo.com // // Last Modified: 01/12/2001 // // Copyright (c) Simon Belak // All rights reserved. // // This Software is distributed under the GPL. For a list of your obligations and rights // under this license please visit the GNU website at http://www.gnu.org/ // // *********************************************************************************************************** class DbObject { // *********************************************************************************************************** // *** (private) variables *** // *********************************************************************************************************** var $mDbType = ''; // Server (currently supported: MySQL,ODBC and MSSQL) var $mCaseFolding = TRUE; // Set this to false for easier field accessing, but with a bit of a preformance hit // see function GetField for details var $mLink_ID = 0; // Link identifier of the currently opened connection var $mQuery_ID = 0; // Result indenifier of the last query var $EOF = TRUE; // End Of File (recordset) var $BOF = TRUE; // Begining Of File (reocrdset) var $mRecord = array(); // Current record var $mCurrentRow = -1; // Current row var $mNumRows = -1; // Number of rows in the recordset var $mNumFields = -1; // Number of fields in the recordset var $mFieldNames = array(); // Names of fields in the recordset // *********************************************************************************************************** // *** Constructor *** // *********************************************************************************************************** function DbObject($dbType='',$host='',$dbName='',$user='',$password='') { if (''==$dbType && ''==$this->mDbType) { $this->_Halt('Server is not selected'); } else if (''==$dbType) { $dbType=$this->mDbType; } else { $this->mDbType=$dbType; } // if $this->Connect($host,$dbName,$user,$password); } // DbObject - constructor // *********************************************************************************************************** // *** Public functions *** // *********************************************************************************************************** function SelectServer($dbType='',$host='',$dbName='',$user='',$password='') { if (''==$dbType) { $this->_Halt('Server is not selected'); return FALSE; } return $this->_ResetConnection($dbType,$host,$dbName,$user,$password); } // SelectServer function SelectHost($host='',$dbName='',$user='',$password='') { if (''==$host) { $this->_Halt('Host is not selected'); return FALSE; } // if return $this->_ResetConnection($this->mDbType,$host,$dbName,$user,$password); } // SelectHost function SelectDb($dbName='',$user='',$password='') { if (''==$dbName) { $this->_Halt('Database name is not set'); return FALSE; } // if $this->FreeResult(); if (FALSE==$this->_db_select_db($dbName,$user,$password)) { $this->_Halt('Error using database: '.$dbName); return FALSE; } // if return TRUE; } // SelectDb function Connect($host='',$dbName='',$user='',$password='') { if ( 0 == $this->mLink_ID ) { $this->mLink_ID=$this->_db_connect($host,$user,$password); if (FALSE==$this->mLink_ID) { $this->_Halt('mLink_ID == FALSE, connect failed'); return FALSE; } // if if ($dbName!='') { $this->SelectDb($dbName,$user,$password); } } // if return $this->mLink_ID; } // Connect function Close() { $status=TRUE; if ( 0 != $this->mLink_ID && FALSE != $this->mLink_ID) { $this->FreeResult(); $status=$this->_db_close(); $this->mLink_ID = 0; } // if return $status; } // Close function FreeResult() { $status=TRUE; if (0 != $this->mQuery_ID && FALSE != $this->mQuery_ID) { $this->mRecord = array(); $this->mCurrentRow = -1; $this->mNumRows = -1; $this->mNumFields = -1; $this->mFieldNames = array(); $this->BOF=TRUE; $this->EOF=TRUE; $status=$this->_db_free_result(); $this->mQuery_ID = 0; } // if return $status; } // FreeResult function Query($query='') { $this->Connect(); $this->FreeResult(); $this->mQuery_ID = $this->_db_query($query); if (FALSE==$this->mQuery_ID) { $this->_Halt('Invalid SQL: '.$query); return FALSE; } else if (1==$this->mQuery_ID) { $this->mQuery_ID=0; } else { $this->mNumRows = $this->_db_num_rows(); $this->mNumFields = $this->_db_num_fields(); $this->mFieldNames = $this->_SetFieldNames(); $this->BOF=TRUE; if (0 < $this->mNumRows) { $this->EOF=FALSE; } else { $this->EOF=TRUE; } // if } // if return $this->mQuery_ID; } // Query function GoToRecord($pos=0) { $status=TRUE; if ($pos > $this->mNumRows-1) { $this->mCurrentRow = -1; $this->EOF=TRUE; $status=FALSE; } else if ($pos < 0) { $this->mCurrentRow = -1; $this->BOF=TRUE; $status=FALSE; } else { if ($pos !=$this-> mCurrentRow+1) // Optimization: if we move one record forward, data_seek isn't needed { if (FALSE == $this->_db_data_seek($pos)) // Unexpected error { return FALSE; } // if } // if $this->mCurrentRow = $pos; $this->mRecord = $this->_db_fetch_array(); $status = is_array($this->mRecord); // Unexpected error $this->BOF=FALSE; $this->EOF=FALSE; } // if return $status; } // GoToRecord function GetField($index=0) { if (FALSE != $this->EOF || FALSE != $this->BOF) { return FALSE; } // if if (FALSE == $this->mCaseFolding && FALSE != is_string($index)) { $index = strtoupper($index); reset($this->mFieldNames); while (list(,$name)=each($this->mFieldNames)) { if (strtoupper($name) == $index) { $index=$name; BREAK; } // if } // while } // if if (FALSE==isset($this->mRecord[$index])) { return FALSE; } // if return $this->mRecord[$index]; } // GetField function NextRecord() { return $this->GoToRecord($this->mCurrentRow + 1); } // NextRecord function PreviousRecord() { return $this->GoToRecord($this->mCurrentRow-1); } // PreviousRecord function GoToRecordRelative($pos=0) { return $this->GoToRecord($this->mCurrentRow+$pos); } // GoToRecordRelative function FirstRecord() { return $this->GoToRecord(0); } // FirstRecord function LastRecord() { return $this->GoToRecord($this->mNumRows-1); } // LastRecord function GetNumFields() { if (-1==$this->mNumFields) { $this->mNumFields = $this->_db_num_fields(); } // if return $this->mNumFields; } // GetNumFields function GetNumRows() { if (-1==$this->mNumRows) { $this->mNumRows = $this->_db_num_rows(); } // if return $this->mNumRows; } // GetNumRows function GetFieldNames($pos=-1) { if (1 > count($this->mFieldNames)) { $this->_SetFieldNames(); } // if if (-1< $pos && count($this->mFieldNames) > $pos) { return $this->mFieldNames[$pos]; } else { return $this->mFieldNames; } // if } // GetFieldNames function SetCaseFolding($val=TRUE) { $this->mCaseFolding=$val; return TRUE; } // SetCaseFolding // *********************************************************************************************************** // *** Private functions *** // *********************************************************************************************************** function _Halt($msg='') { $this->Close(); printf("</td></tr></table><b>Error:</b> %s<br>\n", $msg); // we escape the html table so that Netscape (and other browsers) can display this page properly die('Session halted.'); return TRUE; } // _Halt function _ResetConnection($dbType='',$host='',$dbName='',$user='',$password='') { $this->Close(); $this->mDbType=$dbType; $this->Connect($host,$dbName,$user,$password); if ( 0 == $this->mLink_ID || FALSE == $this->mLink_ID) { return FALSE; } // if return TRUE; } // _ResetConnection function _SetFieldNames() { for ($i=0;$i<$this->mNumFields;$i++) { $this->mFieldNames[]=$this->_db_field_name($i); } // for return $this->mFieldNames; } // _SetFieldNames // *********************************************************************************************************** // *** Functios that unify database accessing *** // *********************************************************************************************************** function _db_connect($host='',$user='',$password='') { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_connect($host, $user, $password); case 'ODBC': // odbc_connect doesn't accept the argument host, and is therefore (in functionalaty) the same as select_db, // so we just skip the connect step and call odbc_connect in select_db step return TRUE; case 'MSSQL': return mssql_connect($host, $user, $password); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_connect function _db_close() { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_close($this->mLink_ID); case 'ODBC': return odbc_close($this->mLink_ID); case 'MSSQL': return mssql_close($this->mLink_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_close function _db_select_db($dbName='',$user='',$password='') { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_select_db($dbName,$this->mLink_ID); case 'ODBC': // See _db_connect case "ODBC" for details $this->mLink_ID=odbc_connect($dbName, $user, $password,SQL_CUR_USE_ODBC); if (FALSE==$this->mLink_ID) { return FALSE; } else { return TRUE; } // if case 'MSSQL': return mssql_select_db($dbName,$this->mLink_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_select_db function _db_free_result() { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_free_result($this->mQuery_ID); case 'ODBC': return odbc_free_result($this->mQuery_ID); case 'MSSQL': return mssql_free_result($this->mQuery_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_free_result function _db_query($query='') { switch (strtoupper($this->mDbType)) { case 'MYSQL': { $temp_query_id=mysql_query($query,$this->mLink_ID); // Becouse for queries DELETE, INSERT, REPLACE and UPDATE mysql doesn't return a result indenifier // We (in those cases) set the result indenifier to 1, so we can distict it from a failed query if (FALSE==$temp_query_id && 0 < mysql_affected_rows($this->mLink_ID)) { $temp_query_id = 1; } // if return $temp_query_id; } // case 'MySQL' case 'ODBC': return odbc_exec($this->mLink_ID,$query); case 'MSSQL': return mssql_query($query,$this->mLink_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_query function _db_fetch_array() { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_fetch_array($this->mQuery_ID); case 'ODBC': // A unfied result: { // Record is returned in an array with numeric keys and field names for keys // Row and field counting start is set to 0 (ODBC default is 1) if (FALSE != odbc_fetch_row($this->mQuery_ID,$this->mCurrentRow+1)) { $resultArray = array(); for ($i=0;$i < odbc_num_fields($this->mQuery_ID);$i++) { $name=odbc_field_name($this->mQuery_ID,$i+1); $resultArray[$name]=null; unset($resultArray[$name]); // isset() function now returns false, but the field name key stays if (FALSE != odbc_result($this->mQuery_ID,$i+1)) { $resultArray[$i]=($resultArray[$name] = odbc_result($this->mQuery_ID,$i+1)); } // if } // for return $resultArray; } else { return FALSE; } // if } // case 'ODBC' case 'MSSQL': return mssql_fetch_array($this->mQuery_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_fetch_array function _db_data_seek($pos=0) { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_data_seek($this->mQuery_ID, $pos); case 'ODBC': // No need to move the internal pointer becouse we provide the optional row_number argument for odbc_fatch_row return TRUE; case 'MSSQL': return mssql_data_seek($this->mQuery_ID, $pos); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_data_seek function _db_num_rows() { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_num_Rows($this->mQuery_ID); case 'ODBC': { $numRows=odbc_num_Rows($this->mQuery_ID); // Some ODBC drivers don't support odbc_num_rows for results of an SELECT query and return an error (-1) if (-1==$numRows) { // Optimization: first we roughly estemate the number of rows // than the last 9 are counted for ($i=0;FALSE != odbc_fetch_Row($this->mQuery_ID,$i+10);$i+=10); for ($numRows=$i+1;FALSE !=odbc_fetch_Row($this->mQuery_ID,$numRows+1);$numRows++); odbc_fetch_Row($this->mQuery_ID,$this->mCurrentRow); // We set the internal pointer back to where we started } // if return $numRows; } // case 'ODBC' case 'MSSQL': return mssql_num_Rows($this->mQuery_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_num_rows function _db_num_fields() { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_num_fields($this->mQuery_ID); case 'ODBC': return odbc_num_fields($this->mQuery_ID); case 'MSSQL': return mssql_num_fields($this->mQuery_ID); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_num_fields function _db_field_name($fieldNumber=0) { switch (strtoupper($this->mDbType)) { case 'MYSQL': return mysql_field_name($this->mQuery_ID , $fieldNumber); case 'ODBC': return odbc_field_name($this->mQuery_ID , $fieldNumber+1); case 'MSSQL': return mssql_field_name($this->mQuery_ID , $fieldNumber); default: $this->_Halt('Server is not selected'); return FALSE; } // switch } // _db_field_name } // class // *********************************************************************************************************** // // *** Class DbObject *** // // Public variables: 0 // // Private variables: 11 // // $mDbType Server (currently supported: MySQL,ODBC and MSSQL) // // $mCaseFolding Set this to false for easier field accessing, but with a bit of a preformance hit // see function GetField for details // // $mLink_ID Link identifier of the currently opened connection // $mQuery_ID Result indenifier of the last query // // $EOF End Of File (recordset) // $BOF Begining Of File (reocrdset) // // $mRecord Current record // $mCurrentRow Current row // // $mNumRows Number of rows in the recordset // $mNumFields Number of fields in the recordset // $mFieldNames Names of fields in the recordset // // Custom cunstuctor: yes (DbObject) // // Public functions: 18 // // SelectServer($dbType='',$host='',$dbName='',$user='',$password='') // SelectHost($host='',$dbName='',$user='',$password='') // SelectDb($dbName='',$user='',$password='') // // Connect($host='',$dbName='',$user='',$password='') // Close() // // FreeResult() // // Query($query='') // // GoToRecord($pos=0) // GoToRecordRelative($pos=0) // NextRecord() // PreviousRecord() // FirstRecord() // LastRecord() // // GetField($index=0) // // GetNumFields() // GetNumRows() // GetFieldNames($pos=-1) // // SetCaseFolding($val=TRUE) // // Private functions: 3 // // _Halt($msg='') // _ResetConnection($dbType='',$host='',$dbName='',$user='',$password='') // _SetFieldNames() // // Special: yes - Functios that unify database accessing // // _db_connect($host='',$user='',$password='') // _db_close() // _db_select_db($dbName='',$user='',$password='') // _db_free_result() // _db_query($query='') // _db_fetch_array() // _db_data_seek($pos=0) // _db_num_rows() // _db_num_fields() // _db_field_name($fieldNumber=0) // // // // *** Example: *** // // 1. $db1 = new DbObject("Mysql","host","database","user","password"); // // 2. $db1->Query("select * from mytable1"); // // 3. while ($db1->NextRecord()) // 4. { // 5. for ($i=0;$i<$db1->GetNumFields();$i++) // 6. { // 7. echo $db1->GetField($i); // 8. } // 9. } // // 10. $db1->FirstRecord(); // 11. echo $db1->GetField("fieldname1"); // // 12. $db1->SetCaseFolding(FALSE); // 13. echo $db1->GetField("fiELDnaME1"); // // *********************************************************************************************************** ?>
db_driver is an abstraction layer equiped with table & database creation/alteration.<br> <br> Makes it real easy to manage your databases. <?php /* ------------------------------------------------------------------------------------------------ db_driver.PHP Version 1.0 ------------------------------------------------------------------------------------------------ This driver is an abstraction layer for accessing a database server in a generic way. This file is the MySQL 4 version. License Information ------------------- Copyright (C) 2003-2005 Galistudio & Fabien Papleux. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, you can get a copy at http://www.gnu.org/licenses/gpl.txt Contact Information ------------------- Should you have any questions / remarks / suggestions, please don't hesitate to contact me at info@galistudio.com or at http://www.galistudio.com. SYNOPSIS -------- $link_id = function db_connect ($server, $user, $pwd); $result = function db_close ($link_id = 0); $error_text = function db_error ($link_id = 0); $error_number = function db_errno ($link_id = 0); $result = function db_select ($db_name, $link_id = 0); $result = function db_create ($db_name, $link_id = 0); $result = function db_drop_database ($db_name, $link_id = 0); $result = function db_table_exists ($table_name, $database_name); $result = function db_drop_table ($table_name, $database_name); $result = function db_create_table ($table_name, $database_name); NOTE: The table creation function automatically creates the first field called 'ID', a autoincrement integer primary index $result_id = function db_query ($query, $link_id = 0); $rows = function db_num_rows ($result_id); $row = function db_fetch_row ($result_id); $array = function db_fetch_array ($result_id); $id = function db_insert_id ($link_id); */ // ------------------------------------------------------------------------------------------------ // CONSTANTS DEFINITIONS // ------------------------------------------------------------------------------------------------ define("DB_DRIVER", 1); define("DB_DRIVER_VERSION", "MySQL - 1.0Beta"); // ------------------------------------------------------------------------------------------------ // DB_CONNECT // ------------------------------------------------------------------------------------------------ function db_connect ($server, $user, $pwd) { return @mysql_connect($server, $user, $pwd); } // ------------------------------------------------------------------------------------------------ // DB_CLOSE // ------------------------------------------------------------------------------------------------ function close($link_id = 0) { if ($link_id) return @mysql_close($link_id); else return @mysql_close(); } // ------------------------------------------------------------------------------------------------ // DB_ERROR // ------------------------------------------------------------------------------------------------ function db_error ($link_id = 0) { if ($link_id) return @mysql_error($link_id); else return @mysql_error(); } // ------------------------------------------------------------------------------------------------ // DB_ERRNO // ------------------------------------------------------------------------------------------------ function db_errno ($link_id = 0) { if ($link_id) return @mysql_errno($link_id); else return @mysql_errno(); } // ------------------------------------------------------------------------------------------------ // DB_SELECT // ------------------------------------------------------------------------------------------------ function db_select ($db_name, $link_id = 0) { if ($link_id) return @mysql_select_db($db_name, $link_id); else return @mysql_select_db($db_name); } // ------------------------------------------------------------------------------------------------ // DB_CREATE // ------------------------------------------------------------------------------------------------ function db_create ($db_name, $link_id = 0) { if ($link_id) return @mysql_create_db($db_name, $link_id); else return @mysql_create_db($db_name); } // ------------------------------------------------------------------------------------------------ // DB_DROP // ------------------------------------------------------------------------------------------------ function db_drop_database ($db_name, $link_id = 0) { if ($link_id) return @mysql_drop_db($db_name, $link_id); else return @mysql_drop_db($db_name); } // ------------------------------------------------------------------------------------------------ // DB_TABLE_EXISTS // ------------------------------------------------------------------------------------------------ function db_table_exists($table_name, $database_name) { $found = 0; $result_id = mysql_list_tables($database_name); while ($record = mysql_fetch_row($result_id)) if (strtoupper($table_name) == strtoupper($record[0])) $found = 1; return $found; } // ------------------------------------------------------------------------------------------------ // DB_DROP_TABLE // ------------------------------------------------------------------------------------------------ function db_drop_table($table_name, $database_name) { $success = FALSE; if (db_table_exists($table_name, $database_name)) { if (db_select($database_name)) { $sql = "DROP TABLE `{$database_name}`.`{$table_name}`"; $success = db_query($sql); } } return $success; } // ------------------------------------------------------------------------------------------------ // DB_CREATE_TABLE // ------------------------------------------------------------------------------------------------ function db_create_table($table_name, $database_name) { if (mysql_select_db($database_name)) { $sql = "CREATE TABLE `{$database_name}`.`{$table_name}` ( `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);"; db_query($sql); } if (db_errno() == 0) return TRUE; else return FALSE; } // ------------------------------------------------------------------------------------------------ // DB_QUERY // ------------------------------------------------------------------------------------------------ function db_query ($query, $link_id = 0) { if ($link_id) return @mysql_query($query, $link_id); else return @mysql_query($query); } // ------------------------------------------------------------------------------------------------ // DB_QUERY // ------------------------------------------------------------------------------------------------ function db_num_rows ($result_id) { return @mysql_num_rows($result_id); } // ------------------------------------------------------------------------------------------------ // DB_FETCH_ROW // ------------------------------------------------------------------------------------------------ function db_fetch_row ($result_id) { return @mysql_fetch_row ($result_id); } // ------------------------------------------------------------------------------------------------ // DB_FETCH_ARRAY // ------------------------------------------------------------------------------------------------ function db_fetch_array ($result_id) { return @mysql_fetch_array ($result_id); } // ------------------------------------------------------------------------------------------------ // DB_INSERT_ID // ------------------------------------------------------------------------------------------------ function db_insert_id ($link_id) { return @mysql_insert_id($link_id); }
ezSQL is a widget that makes it ridiculously easy for you to use mySQL or Oracle8 database(s) within your PHP scripts (more db's coming soon). // Note: In all these examples no other code // is required other than including "ez_sql.php" // available from http://php.justinvincent.com //---------------------------------------------------- // Example 1 //---------------------------------------------------- // Print list of products directly from the db within a foreach loop.. foreach( $db->get_col("SELECT product FROM products") as $item ) { echo "$item"; } //---------------------------------------------------- // Example 2 //---------------------------------------------------- // Select multiple records from the database and print them out.. $users = $db->get_results("SELECT name, email FROM users"); foreach ( $users as $user ) { // Access data using object syntax echo $user->name; echo $user->email; } //---------------------------------------------------- // Example 3 //---------------------------------------------------- // Get one row from the database and print it out.. $user = $db->get_row("SELECT name,email FROM users WHERE id = 2"); echo $user->name; echo $user->email; //---------------------------------------------------- // Example 4 //---------------------------------------------------- // Get one variable from the database and print it out.. if ( $u_count = $db->get_var("SELECT count(*) FROM users")) { echo "There were $u_count users!"; }
MySQL abstraction layer class. Easy to use. More functions than usual MySQL PHP functions to make it easier to port to another DB server platform. <?php /* ------------------------------------------------------------------------------------------ dbdriver class version 1.0Beta ------------------------------------------------------------------------------------------ the dbdriver class is a Database abstract class developed for MySQL. Object is designed to work with the other PHP supported DB systems. You need to adjust the functions. The reason for such many details in the methods is because not all functions are supported accross the bord and the SQL language can vary from a platform to another. Therefore, by being quite specific about each action, we help the developper of the main application to concentrate on building a generic code... License Information ------------------- Copyright (C) 2003 Fabien Papleux, fpa Technology Consulting This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, you can get a copy at http://www.gnu.org/licenses/gpl.txt Contact Information ------------------- Should you have any questions / remarks / suggestions, please don't hesitate to contact me at dev_support@fpatech.com Synopsis -------- Constants: Constants are defined outside the scope of the class and are global. DBDRIVER = 1 : Indicates that the DBDRIVER Module is loaded CNT_AUTOINCREMENT = 1 : Constraint -> Auto Increment CNT_PRIMARYKEY = 2 : Constraint -> Primary Key CNT_UNIQUE = 4 : Constraint -> Unique CNT_NOTNULL = 8 : Constraint -> Not Null CNT_INDEXED = 16 : Constraint -> Indexed Properties: $server : server name, with port# if necessary $link_id : ID for the current connection $user_name : Name of the user $user_pwd : User Password $current_db : name of current database $current_table : bane of the current table. Usually used in conjunction w/ structural work. $result_id : ID for the last result-set Methods: Constructor and structural methods: dbdriver($server_addr, $user, $pwd) classprint() connect($server_addr, $user, $pwd) close() db_create($database) db_select($database) db_exists($database) db_drop() table_create($table) table_select($table) table_exists($table) table_drop([$table]) table_rename($table, $new_name) field_create($field_name, $type, $size, [$constraints], [$table_name]) field_exists($field_name, [$table_name]) field_drop($field_name, [$table_name]) All other methods: query($query) data_seek($rownumber) num_rows() fetch_row() errno() error() Known Bugs ---------- Future Development ------------------ 1) An export function that exports a result_set to a file and returns a filename to download. 2) An import function capable of loading a comma-separated file and/or an XML file and either create a table with it or update an existing one. ------------------------------------------------------------------------------------------ */ // CONSTANTS DEFINITIONS // ------------------------------------------------------------------------------------------ define("DBDRIVER", 1); define("CNT_AUTOINCREMENT", 1); define("CNT_PRIMARYKEY", 2); define("CNT_UNIQUE", 4); define("CNT_NOTNULL", 8); define("CNT_INDEXED", 16); class dbdriver { // PROPERTIES DECLARATION // ------------------------------------------------------------------------------------------ var $server; var $link_id; var $user_name; var $user_pwd; var $current_db; var $current_table; var $result_id; // CONSTRUCTOR // The constructor will initialize the properties and attempt to connect to the serer. // Should it fail, $link_id will be Null // ------------------------------------------------------------------------------------------ function dbdriver ($server_addr = "localhost", $user = "", $pwd = "") { $this->result_id = 0; $this->current_table = ""; $this->current_db = ""; $this->link_id = 0; $this->connect($server_addr, $user, $pwd); } // CLASSPRINT // The purpose of the Print method is to send to the client all the information about // the object for testing and debugging purposes // ------------------------------------------------------------------------------------------ function classprint() { echo("<b>printing information about dbdriver object</b><br><br>"); echo("object properties:<br>"); echo("Server: $this->server<br>"); echo("link_id: {$this->link_id}<br>"); echo("user_name: {$this->user_name}<br>"); echo("user_pwd: {$this->user_pwd}<br>"); echo("current_db: {$this->current_db}<br>"); echo("current_table: {$this->current_table}<br>"); echo("result_id: {$this->result_id}<br><br>"); if ($this->link_id) echo("driver is connected to server<br>"); else echo("driver is not connected to server<br>"); } // CONNECT // The connect method connects to a server and returns the link_id upon successful // connection. Returns False on fail. // ------------------------------------------------------------------------------------------ function connect ($server_addr, $user, $pwd) { $this->server = $server_addr; $this->user_name = $user; $this->user_pwd = $pwd; $this->link_id = mysql_connect($this->server, $this->user_name, $this->user_pwd); return $this->link_id; } // CLOSE // The close method closes the server connection. It always returns 1 // ------------------------------------------------------------------------------------------ function close() { if ($this->link_id) { $this->current_db = ""; $this->current_table = ""; $result_id = 0; mysql_close($this->link_id); } return 1; } // DB_CREATE // Creates a database on current server // ------------------------------------------------------------------------------------------ function db_create ($db_name) { $result = 0; if ($this->link_id) { $result = mysql_query("CREATE DATABASE {$db_name}"); if ($result) return $this->db_select($db_name); } return $result; } // DB_SELECT // Selects a database on the current server. Returns False if DB cannot be found. // ------------------------------------------------------------------------------------------ function db_select($database) { if ($this->link_id) { if (mysql_select_db($database, $this->link_id)) { $this->current_db = $database; $this->result_id = 0; return 1; } } return 0; } // DB_EXISTS // Returns True if specified database exists on current server. False if not. // As for the other similar functions, this is useful to check on the version of a database // in order to build auto-upgrading applications // ------------------------------------------------------------------------------------------ function db_exists($database) { $original_db = $this->current_db; $result = 0; if ($this->link_id) { $result = mysql_select_db($database, $this->link_id); $this->result_id = 0; if ($original_db != "") mysql_select_db($original_db, $this->link_id); } return $result; } // DB_DROP // Removes the current database from the server // ------------------------------------------------------------------------------------------ function db_drop () { if (($this->link_id) && ($this->current_db != "")) { $current_db = ""; $result_id = 0; return mysql_query("DROP DATABASE {$db_name}"); } return 0; } // TABLE_CREATE // Creates an empty table in the current database and sets default table to newly created. // Automatically creates a BIGINT PRIMARY KEY AUTO_INCREMENT field called 'ID' for ease. // ------------------------------------------------------------------------------------------ function table_create($table) { if ($this->link_id && ($this->current_db != "")) { $result = mysql_query("CREATE TABLE $table (ID BIGINT AUTO_INCREMENT PRIMARY KEY);", $this->link_id); if ($result) { $this->current_table = $table; return 1; } } return 0; } // TABLE_SELECT // Selects a table as the current table in current DB. Return False upon failure. // ------------------------------------------------------------------------------------------ function table_select($table) { if (($this->link_id) && ($this->current_db != "") && ($this->table_exists($table))) { $this->current_table = $table; return 1; } return 0; } // TABLE_EXISTS // Checks whether specified table exists in current database. // ------------------------------------------------------------------------------------------ function table_exists($table) { $found = 0; if (($this->link_id) && ($this->current_db != "")) { $result_id = mysql_list_tables($this->current_db); while ($record = mysql_fetch_row($result_id)) { if (strtoupper($table) == strtoupper($record[0])) { $found = 1; } } } return $found; } // TABLE_DROP // Drops the current table from the current database. Returns False on failure. // ------------------------------------------------------------------------------------------ function table_drop($table = "") { if ($this->link_id && ($this->current_db != "")) { if ($table != "") $this->current_table = $table; $result = mysql_query("DROP TABLE IF EXISTS $this->current_table"); if ($result) $this->current_table = ""; return $result; } else return 0; } // TABLE_RENAME // Renames a table // ------------------------------------------------------------------------------------------ function table_rename($table, $new_name) { if ($this->link_id && ($this->current_db != "")) { $result = mysql_query("ALTER TABLE $table RENAME TO $new_name"); if ($result) $this->current_table = $new_name; return $result; } else return 0; } // FIELD_CREATE // Creates a field in current table or any specified table. constraints can be any // sum of the CNT_ constants. For instance, a UNIQUE and INDEXED field would be // sent a $constraints of CNT_UNIQUE+CNT_INDEXED. // For fields which size is preset, use 0 for the $size parameter if you need to send // any constraints. // ------------------------------------------------------------------------------------------ function field_create($field_name, $type, $size = 0, $constraints = 0, $table_name = "") { if ($this->link_id && ($this->current_db != "") && (($this->current_table != "") || ($table_name != ""))) { if ($table_name == "") $table_name = $this->current_table; $nullnotnull = "NULL "; $autoincrement = " "; $primarykey = " "; $unique = " "; $indexed = " "; $sql = ""; if ($constraints >= 16) { $indexed = "INDEX $field_name "; $constraints -= 16; } if ($constraints >= 8) { $nullnotnull = "NOT NULL "; $constraints -= 8; } if ($constraints >= 4) { $unique = "UNIQUE "; $constraints -= 4; } if ($constraints >= 2) { $primarykey = "PRIMARY KEY "; $constraints -= 2; } if ($constraints == 1) { $autoincrement = "AUTO_INCREMENT "; $constraints -= 1; } $sql = "ALTER TABLE $table_name ADD COLUMN $field_name $type "; if ($size) $sql = $sql . "(" . $size . ") "; $sql = $sql . $nullnotnull . $autoincrement . $primarykey . $unique; echo $sql . "<br>"; $result = mysql_query($sql, $this->link_id); if ($result) { if ($sql2 != "") mysql_query($sql2, $this->link_id); if ($sql3 != "") mysql_query($sql3, $this->link_id); } return $result; } else return 0; } // FIELD_EXISTS // Checks whether specified field exists in current or specified table. // ------------------------------------------------------------------------------------------ function field_exists($field_name, $table_name = "") { $found = 0; $t = 0; if ($this->link_id && ($this->current_db != "") && (($this->current_table != "") || ($table_name != ""))) { if ($table_name != "") $this->current_table = $table_name; $result_id = mysql_list_fields( $this->current_db, $this->current_table, $this->link_id); for ($t = 0; $t < mysql_num_fields($result_id); $t++) { if (strtoupper($field_name) == strtoupper(mysql_field_name($result_id, $t))) { $found = 1; } } } return $found; } // FIELD_DROP // Drops the specified field from the current or specified table in current database // ------------------------------------------------------------------------------------------ function field_drop($field_name, $table_name = "") { $result = 0; if ($this->link_id && ($this->current_db != "") && (($this->current_table != "") || ($table_name != ""))) { if ($table_name != "") $this->current_table = $table_name; $result = mysql_query("ALTER TABLE $this->current_table DROP INDEX $field_name", $this->link_id); $result = mysql_query("ALTER TABLE $this->current_table DROP COLUMN $field_name", $this->link_id); } return $result; } // QUERY // Executes the specified query in current database // ------------------------------------------------------------------------------------------ function query($sql) { $this->return_id = 0; if ($this->link_id) $this->result_id = mysql_query($sql, $this->link_id); return $this->result_id; } // DATA_SEEK // Seeks a certain row within the current resultset. // ------------------------------------------------------------------------------------------ function data_seek($rownumber) { if ($this->result_id) return mysql_data_seek($this->result_id, $rownumber); return 0; } // NUM_ROWS // Seeks a certain row within the current resultset. // ------------------------------------------------------------------------------------------ function num_rows() { if ($this->result_id) return mysql_num_rows($this->result_id); return 0; } // FETCH_ROW // Returns next row in current result set // ------------------------------------------------------------------------------------------ function fetch_row() { if ($this->result_id) return mysql_fetch_row($this->result_id); return 0; } // ERROR // Returns last MySQL error message // ------------------------------------------------------------------------------------------ function error() { return mysql_error(); } // ERRNO // Returns last MySQL error number // ------------------------------------------------------------------------------------------ function errno() { return mysql_errno(); } } ?>
Slight changes to Simple Data Abstraction Class by ICEMAC. Changes to handle Commits / Rollbacks in MYSQL transactional tables. class DataLayerAbstract { public $connection; public $resultSet; public $autoCommit = 0; public $rowData; public $nextRowNumber = 0; public $rowCount = 0; public function __construct() { die("DataLayerAbstract: Do not create instances of DataLayerAbstract! Use a subclass instead."); } public public function Open($host, $user, $pass, $db = "", $autocommit = 0) { } public function Close() { } public function SelectDB($dbname) { } public function Query($querystr) { } public function SeekRow($row = 0) { } public function ReadRow() { } public function Begin() { } public function Commit() { } public function Rollback() { } public function SetAutoCommit($autoCommit) { $this->autoCommit = $autoCommit; } public function _ident() { return "DataLayerAbstract/1.2"; } } class DataLayerMySQL extends DataLayerAbstract { public function __construct($host, $user, $pass, $db = null) { $this->Open($host, $user, $pass); if ($db != null) { $this->SelectDB($db); } } public function Open($host, $user, $pass, $autocommit = 0) { $this->connection = mysql_connect($host, $user, $pass); } public function Close() { @ mysql_free_result($this->dbQResult); return mysql_close($this->connection); } public function SelectDB($dbname) { return (mysql_select_db($dbname, $this->connection)); } public function Query($querystr) { $result = mysql_query($querystr, $this->connection); if (!$result) { return 0; } else { if (isset($this->dbQResult)) mysql_free_result($this->dbQResult); $this->rowData = array(); $this->resultSet = $result; $this->rowCount = mysql_num_rows($this->resultSet); if (!$this->rowCount) { $this->rowCount = 0; } return 1; } } public function SeekRow($row = 0) { if ((!mysql_data_seek($this->resultSet, $row)) or ($row > $this->rowCount - 1)) { printf("SeekRow: Cannot seek to row %dn", $row); return 0; } else { return 1; } } public function ReadRow() { if ($this->rowData = mysql_fetch_array($this->resultSet)) { $this->nextRowNumber++; return 1; } else { return 0; } } public function Begin() { mysql_query("SET AUTOCOMMIT=0"); mysql_query("BEGIN"); // For MySQL 3.x // mysql_query("START TRANSACTION"); // For MySQL 4 and above } public function Commit() { mysql_query("COMMIT"); } function Rollback() { mysql_query("ROLLBACK"); } public function _ident() { return "DataLayerMySQL/1.2"; } }
This DBClass allows a coder not only to easily run SQL queries and catch/log errors in case of failure but also to easily get result data epresented into php arrays or html tables with a built-in pagination control. <? /********************************************************************/ /* Filename : db.class.php */ /* Created : 20/01/06 */ /* Created By : Massimiliano Wosz */ /* Version : 1.01 */ /********************************************************************/ /********************************************************************/ /* This DBClass allows a coder not only to easily run SQL */ /* queries and catch/log errors in case of failure but also to */ /* easily get result data represented into php arrays or */ /* html tables with a built-in pagination control. */ /********************************************************************/ /********************************************************************/ /* Additions and modifications are allowed as long as this */ /* message remains. */ /* If you do make any change please contact me at this address. */ /* */ /* massimiliano.wosz@evolvetechnologies.it */ /* */ /********************************************************************/ /********************************************************************/ /* CUT'N'PASTE SAMPLES */ /******************************************************************** //Create new DB object $database=new db("localhost","root","root","test"); //Simple values iteration. echo $database->get_db_value("select field from table"); while($database->next_value()) echo $database->get_db_value(); //Simple rows iteration. print_r($database->get_db_row("select * from table")); while($database->next_value()) print_r($database->get_db_row()); //Get_rowno sample. //this sample will print out the row number 2 of the //current_result resource. $database->get_db_row("select * from table"); print_r($database->get_rowno(2)); //fill_result_array sample. //this sample will print the resulting array filled //with all the values from the query using the fild n.1 //as array value and the field n.0 as array key. print_r($database->fill_result_array("select * from table",1,0)); //pagination sample. //this sample will set the number of row displayed per page to 5, //the current page to che $_REQUEST['page'] value //and print out the results. $database->set_pagination(5,$_REQUEST['page']); print_r($database->get_db_row("select * from table")); while($database->next_value()) print_r($database->get_db_row()); //print_result_table with print_navigation_command sample. //this sample will set the number of row displayed per page to 5, //the current page to che $_REQUEST['page'] value //and print out the results as an html table. //additional navigation commands are added by the function //print_navigation_command to navigate throught records. $database->set_pagination(5,$_REQUEST['page']); //$headers=array("FIELD1","FIELD2"); //echo $database->print_result_table("select * from table",$headers); echo $database->print_result_table("select * from table"); echo $database->print_navigation_command(); /********************************************************************/ /********************************************************************/ /* Turn off error reporting */ /********************************************************************/ error_reporting(0); class db{ /********************************************************************/ /* Connection variables /********************************************************************/ protected $connection; //Connection link protected $host; //DB Hostname protected $user; //DB Username protected $password; //DB Password protected $dbname; //Database name /********************************************************************/ /* Current pointers /********************************************************************/ protected $current_result; //Current result resource protected $current_rowno=0; //Current row /********************************************************************/ /* Last error variables /********************************************************************/ public $last_error; //Last error public $last_db_error; //Last db error /********************************************************************/ /* Error stacks /********************************************************************/ public $error_dump=array(); //array of all error public $db_error_dump=array(); //array of all db error /********************************************************************/ /* Pagination variables /********************************************************************/ public $current_page; //Current page diplayed public $page_size=0; //Number of record per page public $total_record; //Total record for a query /********************************************************************/ /* ENGINE variables /********************************************************************/ public $db_engine; //database engine /********************************************************************/ /* Constructor. */ /* Parameters: */ /* $host = Database hostname DEFAULT = localhost */ /* $user = Database username DEFAULT = root */ /* $password = Database password DEFAULT = localhost */ /* $dbname = Database name DEFAULT = [BLANK] */ /* $dbengine = Database engine DEFAULT = mysql */ /* */ /* Creates the DB class object, set the internal Connection */ /* variables and tries to connect to the database engine. */ /* If a "database name" (optional) is passed too it tries */ /* to select that database. */ /********************************************************************/ function db($host="localhost",$user="root",$password="root",$dbname="",$dbengine="mysql") { if(!include("db/$dbengine.inc.php")) { $this->set_error("FATAL ERROR: Unknown DB engine.",""); die($this->last_error); } $this->host=$host; $this->user=$user; $this->password=$password; $this->connect(); if($dbname!="") { $this->dbname=$dbname; $this->select_db($this->dbname); } } /********************************************************************/ /* set_error($error,$db_error) */ /* Parameters: */ /* - $error = Error string DEFAULT = - */ /* - $db_error = db_error DEFAULT = - */ /* */ /* Sets the last_error variables and the db_last_error */ /* variables and pushes into the error stacks the two values */ /* passed. */ /********************************************************************/ protected function set_error($error,$db_error) { $this->last_error=$error; $this->last_db_error=$db_error; array_push($this->error_dump,$error); array_push($this->db_error_dump,$db_error); } /********************************************************************/ /* connect() */ /* Parameters: */ /* - None - */ /* */ /* Connects the DB object to the database specified in the */ /* Connection variables and fills error variables in case */ /* of fault. */ /********************************************************************/ protected function connect() { $this->connection=@connect($this->host,$this->user,$this->password) or $this->set_error("ATTENZIONE:Impossibile connettersi al server.",error()); } /********************************************************************/ /* select_db($dbname) */ /* Parameters: */ /* $dbname = Database name DEFAULT = [BLANK] */ /* */ /* Selects the database to be used for the queries using the */ /* the "database name" passed as parameter and fills error */ /* variables in case of fault. */ /********************************************************************/ function select_db($dbname) { @select_db($dbname,$this->connection) or $this->set_error("ATTENZIONE:Database '$dbname' non trovato.",error()); } /********************************************************************/ /* query($query,$dbname="") */ /* Parameters: */ /* - $query = Sql string to execute DEFAULT = - */ /* - $dbname = Database name DEFAULT = [BLANK] */ /* */ /* Executes the sql string passed in the parameter $query, */ /* sets the internal current_recult pointer to the result obtained */ /* with the query executed and fills error variables in case */ /* of fault. */ /* If "Pagination variables" are set it modifies the query passed */ /* adding the sql LIMIT command and sets the total_record variable */ /********************************************************************/ function query($query,$dbname="") { if($dbname=="") $dbname=$this->dbname; if($this->page_size!=0) { $this->total_record=num_rows(db_query($dbname,$query)); $start=$this->page_size*($this->current_page-1); $query.=" LIMIT $start,$this->page_size"; } $this->current_result = @db_query($dbname,$query) or $this->set_error("ATTENZIONE:Errore durante l'esecuzione dell'istruzione.",error()); } /********************************************************************/ /* get_db_value() */ /* Parameters: */ /* - $query = Sql string to execute DEFAULT = [BLANK] */ /* */ /* Extract and return as string the value requested in the $query */ /* command. If more than one values are extracted by the */ /* command only the first value will be returned. */ /* */ /* Calling the method with a blank query will return the string */ /* value of the current_result resource */ /* - used after calling next_value / prev_value() */ /********************************************************************/ function get_db_value($query="") { if($query!="") $this->query($query,$this->dbname); $row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error()); return $row[0]; } /********************************************************************/ /* get_db_row() */ /* Parameters: */ /* - $query = Sql string to execute DEFAULT = [BLANK] */ /* */ /* Extract and return as an array the first row selected with */ /* the $query command. */ /* */ /* Calling the method with a blank query will return the current */ /* row selected */ /* - used after calling next_value / prev_value() */ /********************************************************************/ function get_db_row($query="") { if($query!="") $this->query($query,$this->dbname); $row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error()); return $row; } /********************************************************************/ /* next_value() */ /* Parameters: */ /* - none - */ /* */ /* Increments the pointer to the current row and the pointer */ /* to the db result resource and fills error variables in */ /* case of fault. */ /* */ /********************************************************************/ function next_value() { $this->current_rowno++; if(@data_seek($this->current_result,$this->current_rowno) or $this->set_error("ATTENZIONE:Raggiunta l'ultima riga.",error())) return true; else { $this->current_rowno=0; return false; } } /********************************************************************/ /* prev_value() */ /* Parameters: */ /* - none - */ /* */ /* Decrements the pointer to the current row and the pointer */ /* to the db result resource and fills error variables in */ /* case of fault. */ /* */ /********************************************************************/ function prev_value() { $this->current_rowno--; if(@data_seek($this->current_result,$this->current_rowno) or $this->set_error("ATTENZIONE:Raggiunta la prima riga.",error())) return true; else { $this->current_rowno=0; return false; } } /********************************************************************/ /* numrows() */ /* Parameters: */ /* - none - */ /* */ /* Returns the number of rows selected in the current_result */ /* db resource. */ /* */ /********************************************************************/ function numrows() { return @num_rows($this->current_result); } /********************************************************************/ /* get_rowno($rowno) */ /* Parameters: */ /* - $rowno = Number of the row to return DEFAULT = - */ /* */ /* Extract and return as an array the "$rowno" row from the */ /* current_result. */ /* */ /********************************************************************/ function get_rowno($rowno) { @data_seek($this->current_result,$rowno) or $this->set_error("ATTENZIONE:Impossibile trovare la riga richiesta.",error()); $this->current_rowno=$rowno; $row=@fetch_array($this->current_result) or $this->set_error("ATTENZIONE:Errore durante la serializzazione dei dati.",error()); return $row; } /********************************************************************/ /* fill_result_array($query,$value,$key="") */ /* Parameters: */ /* */ /* */ /********************************************************************/ function fill_result_array($query,$value,$key="") { $return=array(); $row=$this->get_db_row($query); if($key=="")//autoincrement indexes $return[$this->current_rowno]=$row[$value]; else //use key passed $return[$row[$key]]=$row[$value]; while($this->next_value()) { $row=$this->get_db_row(); if($key=="")//autoincrement indexes $return[$this->current_rowno]=$row[$value]; else //use key passed $return[$row[$key]]=$row[$value]; } return $return; } /********************************************************************/ /* set_pagination($page_size,$current_page=1) */ /* Parameters: */ /* */ /* */ /********************************************************************/ function set_pagination($page_size,$current_page=1) { if($current_page=="")$current_page=1; $this->page_size=$page_size; $this->current_page=$current_page; } /********************************************************************/ /* print_navigation_command() */ /* Parameters: */ /* */ /* */ /********************************************************************/ function print_navigation_command() { if($this->numrows()>0) { $navigation="<table><tr>"; $query_string=$_SERVER['QUERY_STRING']; $pos=strpos($query_string,"page="); $part1=substr($query_string,0,$pos); $start_secondaparte=($pos+5+strlen($this->current_page)); $part2=substr($query_string,$start_secondaparte); if($this->page_size!=0) { $total_pages=floor($this->total_record/$this->page_size); //if($this->current_page>$total_pages) //$this->current_page=$total_pages; //print first button if($this->current_page!=1) $navigation.="<td><a href='?".$part1."page=1".$part2."'>first</a></td>"; else $navigation.="<td>first</td>"; //print prev button $prev=$this->current_page-1; if($prev>0) $navigation.="<td><a href='?".$part1."page=$prev".$part2."'>prev</a></td>"; else $navigation.="<td>prev</td>"; //print commands before current page $page_before=$this->current_page-5; if($page_before<=0)$page_before=1; while ($page_before>0 && $page_before<$this->current_page) { $navigation.="<td><a href='?".$part1."page=$page_before".$part2."'>$page_before</a></td>"; $page_before++; } //print commands for current page $navigation.="<td><b>".$this->current_page."</b></td>"; //print commands after current page $page_after=$this->current_page+1; //if($page_after>$total_pages)$page_after=$total_pages; while ($page_after<=$total_pages && $page_after<=$this->current_page+5) { $navigation.="<td><a href='?".$part1."page=$page_after".$part2."'>$page_after</a></td>"; $page_after++; } //print next button $next=$this->current_page+1; if($next<=$total_pages) $navigation.="<td><a href='?".$part1."page=$next".$part2."'>next</a></td>"; else $navigation.="<td>next</td>"; //print last button if($this->current_page!=$total_pages) $navigation.="<td><a href='?".$part1."page=$total_pages".$part2."'>last</a></td>"; else $navigation.="<td>last</td>"; } $navigation.="</tr></table>"; } return $navigation; } /********************************************************************/ /* print_result_table($query,$custom_headers=array()) */ /* Parameters: */ /* */ /* */ /********************************************************************/ function print_result_table($query,$custom_headers=array(),$table_class="",$header_class="") { $table="<table border=1 "; if($table_class!="") $table.="class='$table_class'"; $table.=">"; $this->query($query); $field=0; $table.="<tr "; if($header_class!="") $table.="class='$header_class'"; $table.=">"; while ($field < @num_fields($this->current_result)) { if(count($custom_headers)>0) { /**************************************/ /* Print custom headers /**************************************/ $table.="<td><b>".$custom_headers[$field]."</b></td>"; } else { /**************************************/ /* Print table headers /**************************************/ $header=fetch_field($this->current_result,$field); $table.="<td><b>".$header->name."</b></td>"; } $field++; } $table.="</tr>"; $row=@fetch_row($this->current_result); $table.="<tr>"; foreach ($row as $value) $table.="<td>$value</td>"; $table.="</tr>"; while($this->next_value()) { $row=@fetch_row($this->current_result); $table.="<tr>"; foreach ($row as $value) $table.="<td>$value</td>"; $table.="</tr>"; } $table.="<table>"; return $table; } } ?> /* This section should be in a file named mysql.inc.php */ <? function connect($hostname,$username,$password="") { return mysql_connect($hostname,$username,$password); } function select_db($database_name) { return mysql_select_db($database_name); } function db_query($database_name,$query) { return mysql_db_query($database_name,$query); } function fetch_array($result) { return mysql_fetch_array($result); } function data_seek($result,$row_number) { return mysql_data_seek($result,$row_number); } function num_rows($result) { return mysql_num_rows($result); } function error() { return mysql_error(); } function num_fields($result) { return mysql_num_fields($result); } function fetch_field($result) { return mysql_fetch_field($result); } function fetch_row($result) { return mysql_fetch_row($result); } ?> /* This section should be in a file named mssql.inc.php */ <? function connect($hostname,$username,$password="") { return mssql_connect($hostname,$username,$password); } function select_db($database_name) { return mssql_select_db($database_name); } function db_query($database_name,$query) { mssql_select_db($database_name); return mssql_query($query); } function fetch_array($result) { return mssql_fetch_array($result); } function data_seek($result,$row_number) { return mssql_data_seek($result,$row_number); } function num_rows($result) { return mssql_num_rows($result); } function error() { return mssql_get_last_message(); } function num_fields($result) { return mssql_num_fields($result); } function fetch_field($result) { return mssql_fetch_field($result); } function fetch_row($result) { return mssql_fetch_row($result); } ?>
I needed a quick way to switch quickly between MySQL and MS SQL Server for applications, so I wrote two classes, that provide a modular, consistent API that can be used for either database. Changing out your DB is as easy as dropping in the proper DB_whatever.php file into the dir that DB.php lives in, then changing the $dbtype var before you require() DB.php <?php /* Copyright (c) 2000, Jason Costomiris All rights reserved. Don't be scared, it's just a BSD-ish license. Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met: 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer. 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution. 3. All advertising materials mentioning features or use of this software must display the following acknowledgement: This product includes software developed by Jason Costomiris. 4. The name of the author may not be used to endorse or promote products derived from this software without specific prior written permission. THIS SOFTWARE IS PROVIDED BY THE AUTHOR COPYRIGHT HOLDERS AND CONTRIBUTORS ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. */ /* This section should be in a file named DB.php */ class DB_base { // get server/instance function setinst($instance){ $this->instance = $instance; } // get username function setuser($user){ $this->user = $user; } // get password function setpass($pass){ $this->pass = $pass; } // get database name function dbname($dbname){ $this->dbname = $dbname; } // Use persistent connections? // Only call this if you want persistent connections function persist(){ $this->persist = 1; } } $file = join("", array("DB_", $dbtype, ".php")); require($file); ?> <?php /* This section should be in a file named DB_mysql.php */ class DB extends DB_base { // Connect or pconnect. function connect(){ if($this->persist){ $this->conn = mysql_pconnect($this->instance, $this->user, $this->pass); } else { $this->conn = mysql_connect($this->instance, $this->user, $this->pass); } mysql_select_db($this->dbname, $this->conn); return($this->conn); } // close function close(){ if($this->persist) { $ret = 1; } else { $ret = mysql_close($this->conn); } return($ret); } // query function query($query){ $this->result = mysql_query($query); return($this->result); } // numrows function numrows(){ $this->numrows = mysql_num_rows($this->result); return($this->numrows); } // affected rows function affrows(){ $this->affrows = mysql_affected_rows($this->result); return($this->affrows); } // seek function seek($row){ $seek = mysql_data_seek($this->result, $row); return($seek); } // fetch object function fobject(){ $object = mysql_fetch_object($this->result); return($object); } // fetch array function farray(){ $array = mysql_fetch_array($this->result); return($array); } // free function free(){ $free = mysql_free_result($this->result); return($free); } } ?>
An object orientated database absraction layer. Meant for use with mySQL. Supports persistant and non persistant connections, error handling and customised logging. Designed to separate application from database logic. Returns all required elements, result resources, rows and field sizes and names. <?PHP /************************* Author: Simon Johnson Date: 01/07/2002 **************************/ /************************* This class was written specifically for my needs, acts as an abstraction, to separate database connectivity from application logic, returns very little in the way of output, unless specified in the query method - Nice formatted table(Easy reference), Do not use as output, just a guide, the table is poor for large datasets! I didnt want textual output everywhere,so have used logs to display relavent info, alot more graceful! PLEASE REMEBER TO SET REQUIRED FILE PERMISSIONS ON SERVER FOR LOGS. YOU MUST HAVE ROOT ACCESS TO ALLOW AUTO CREATE OF LOGS *************************/ /************************* PRIVATE: Private attributes not supported in php, so leave out get accessors where this key word is. You cen enforce change using set if required **************************/ /*TODO: Create separate file writer object for error handling*/ /*TODO: Support transactions and pointers.+better rows handlers*/ class Dbase { //CLASS ATTRIBUTES /************* If you want to explicitly set connection details do so below ************/ var $hostname; var $username; var $password; var $dbname; /***************************************** To turn error and action reporting on, set to one, else leave blank. If it is set on, create a directory /logs, in the same filesystem as the class. Create two files, action_log.txt and dbase_log.txt. *******************************************/ var $err_rep=1; var $dbtype; var $qryrows; var $qryfields; var $tblname; var $error; var $conn; //PRIVATE var $action; var $querystring; var $qryresult; var $try; //PRIVATE /******************************************* FULL SET OF ACCESSOR METHODS TO ENCOURAGE ENCAPSULATION AND DATA HIDING ********************************************/ /******* SETTERS ********/ function setDbname($newdbname) { $this->dbname=$newdbname; } function setTry($newtry) { $this->try=$newtry; } function setDbtype($newdbtype) { $this->dbtype=$newdbtype; } function setHostname($newhostname) { $this->hostname=$newhostname; } function setUsername($newusername) { $this->username=$newusername; } function setPassword($newpassword) { $this->password=$newpassword; } function setQryrows($newqryrows) { $this->qryrows=$newqryrows; } function setQryfields($newqryfields) { $this->qryfields=$newqryfields; } function setTblname($newtblname) { $this->tblname=$newtblname; } function setError($newerror) { $this->error=$newerror; $date = date("j m Y"); if ($this->err_rep=="1") { $filelocation="dbase_log.txt"; $newfile = fopen($filelocation,"a+"); $add = "\n"."$this->error: $date"; fwrite($newfile, $add); fclose($newfile); } else { //Do nothing } } function setConn($newconn) { $this->conn=$newconn; } function setAction($newaction) { $this->action=$newaction; $date = date("G:i : j m Y"); if ($this->err_rep=="1") { $filelocation="action_log.txt"; $newfile = fopen($filelocation,"a+"); $add = "\n"."$this->action: $date"; fwrite($newfile, $add); fclose($newfile); } else { //Do nothing - This needs addressing, not brilliant coding! } } function setQuerystring($newquerystring) { $this->querystring=$newquerystring; } function setQryresult($newqryresult) { $this->qryresult=$newqryresult; } function setErr_rep($newerr_rep) { $this->err_rep=$newerr_rep; } /******** GETTERS ********/ function getDbname() { return $this->dbname; } function getDbtype() { return $this->dbtype; } function getHostname() { return $this->hostname; } function getUsername() { return $this->username; } function getPassword() { return $this->password; } function getQryrows() { return $this->qryrows; } function getQryfields() { return $this->qryfields; } function getTblname() { return $this->tblname; } function getError() { return $this->error; } function getAction() { return $this->action; } function getQuerystring() { return $this->querystring; } function getQryresult() { return $this->qryresult; } function getErr_rep() { return $this->err_rep; } /****************************** OPERATOR FUNCTIONS ******************************/ function SetUp($hostname,$username,$password,$dbname) { /******************************* INCASE SOMETHING GOES WRONG WITH FILE WRITER, CHECK LOG SET-UP ********************************/ if ($this->err_rep==1) { if (file_exists("action_log.txt")) { $this->setAction("NEW INSTANCE"); } else { echo"No files Found, first run detected, attempting to create log files, re-instantiate object for populated firstrun logs"; } } else {} $this->setHostname($hostname); $this->setUsername($username); $this->setPassword($password); $this->setDbname($dbname); } function Connect($type)//THIS PARAMETRE IS REQUIRED!! EITHER 1 or 0 /***************** Will return a message if theres an error, get the message in the child like so: '$message=$con->Connect()' Or use the accessor '$message=$con->GetError()', or just check the log! ********************/ { if(($this->username=="")||($this->password=="")||($this->hostname=="")) { $this->setError("You have failed to insert either your' username, hostname or password"); } else { switch($type) { case "1": $this->conn = @mysql_pconnect($this->hostname,$this->username,$this->password); break; default: $this->conn = @mysql_connect($this->hostname,$this->username,$this->password); break; } if ($this->conn) { $this->setAction("Connection Made to Database on $this->hostname"); $this->setTry(mysql_select_db($this->dbname)); if($this->try) { $this->setAction("Successfully communicating with database: $this->dbname"); } else { $this->setAction("Connection made but cannot talk to database"); $this->setError("This certainly shouldn't happen!!"); } } else { $this->setAction("Database Connection Attempt Failed See Error log"); $this->setError("Database Connection Error,reason: Connection remains in scope"); } }return $this->error; } function Disconnect() { if($this->conn) { $this->try=mysql_close($this->conn); $this->setConn(mysql_free_result($this->qryresult)); if($this->try) { $this->setAction("Database Connection closed"); } else { $this->setAction("Attempted to close Database Connection however it is Persistant"); $this->setError("Database Remains Open"); } } else { $this->setAction("Database connection could not be made, so disconnection is irrelavent"); $this->setError("Database Remains Closed"); } } function Query($string, $display) { if($this->conn) { $this->setQuerystring($string); $this->setQryresult(mysql_db_query($this->dbname, $this->querystring)); $this->setQryrows(mysql_num_rows($this->qryresult)); $this->setQryfields(mysql_num_fields($this->qryresult)); if($this->qryresult) { $this->setAction("Query Execution Success"); if ($display=="1") { $this->setAction("Attempting to display query results"); $c=$this->qryfields; $r=$this->qryrows; echo"<table border=1><tr>"; for ($i = 0; $i < $c; $i++) { $cols = mysql_field_name($this->qryresult, $i); $cols=strtoupper($cols); echo "<td border=1><b><font face=arial size=1>$cols</b><br>"; for ($g = 0; $g < $r; $g++) { $rows = mysql_result($this->qryresult, $g, $cols); echo "<font face=arial size=1><br>$rows<br><br>"; } } echo"</font></table>"; } else { $this->setAction("No Display of query requested, populating result resource"); } } else { $this->setAction("Query Execution Failure"); $this->setError("Query Execution Error"); } } else { $this->setAction("Credentials could not be verified or connection instance not called"); $this->setError("Bad credentials or connection instance not called"); } } } ?>
Abstract Layer class to run with Oracle Database. <? ################################ # Oracle Database Abastract Layer. v2.1 # Feranndo Labombarda - fllabombarda@hotmail.com ################################ # - EXAMPLE - # $db = new database(); # $rs = $db->Select("SELECT COL FROM TB_SECURITY"); # while ($db->Fetch($rs)) { # $value = $db->Result($rs,"COL"); # } # ################################ class database { var $DB_Name; // banco. var $DB_User; // usuario. var $DB_Pass; // senha. var $DB_ID; // conection_id. var $query; // Guarda query no m todo prepare var $result; // RESULT var $dbh; // Handle do banco var $erro; function database() { $this->dbh = $this->Connect(); } /** * @return void * @desc Destructor, when finish shutdown connection */ function _database() { $this->DBClose(); } /** * @return Connection * @desc Start DB Connection */ function Connect() { $this->DB_Name = ""; $this->DB_User = "portaleds"; $this->DB_Pass = "eds123"; $this->erro = "Error opening Database."; if (!$this->dbh) { return $this->dbh = @OCILogon( $this->DB_User, $this->DB_Pass, $this->DB_Name); } else { return $this->dbh; } } /** * @return null * @param String $query * @desc Log SQL commands into default error log file EX: Apache, "error_log" */ function logSQL($sql){ $fp = fopen("/tmp/sql.log","a+"); flock($fp, LOCK_EX); fwrite($fp,$sql.";\n"); fflush($fp); fclose($fp); } /** * @return resultset * @param String $query, connection $dbh * @desc Execute a SELECT querys */ function Select($query) { $result = OCIParse($this->dbh, $query); $this->logSQL($query); if (!OCIExecute($result)) error_log($_SERVER["PHP_SELF"]." S -> ".$query."\n",0); return $result; } /** * @return int rowcount * @param String $query * @desc Execute DML query into Database */ function Dml($query) { $rs = @OCIParse($this->dbh, $query); if (!OCIExecute($rs)) error_log($_SERVER["PHP_SELF"]." D -> ".$query,0); @OCICommit($this->dbh); $affected = @OCIRowcount($rs); $this->freeStatement($rs); return $affected; } /** * @return boolean * @paramr resultset $rs * @desc Close cursor, free resources */ function freeStatement($rs) { return ocifreestatement($rs); } /** * @return row * @param resultset $result * @desc Fetch ResultSet */ function Fetch($result) { $row=null; OCIFetchinto($result, $row); return $row; } function NumRow($result, $garbage) { $this->number = OCIFetchStatement($result, $garbage); return $this->number; } function Result($result, $CAMPO) { $this->result = @OCIResult($result, $CAMPO); return $this->result; } function NumCol($result) { $this->number = @OCINumCols($result); return $this->number; } /** * @return true * @desc Commit connection */ function Commit() { @OCICommit($this->dbh); return true; } /** * @return true * @desc Close DB Connection */ function DBClose() { @OCILogoff (); return true ; } function ServerVersion(){ return OCIServerVersion($this->dbh); } }
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. <br> In addition to "normal" stuff that you'd expect from a db abstraction layer, it also supports transactions and comprehensive error checking/management.<br> Revision History: *** see class header for revision history *** <?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 ?>
mysql_fetch_assoc "converts" one row from the table into an array. mysql_insert_array "converts" an array into one row from the table. <?php function mysql_insert_array($my_array) { // Version 1.0 // Copyright: you may do whatever you wish (and can ;-) ) with this code // Find all the keys from the array $my_array $keys = array_keys($my_array); // Find the number of the keys $keys_number = count($keys); // Generate the column name for the $sql query $column_names = "("; $values = "("; for ($i = 0; $i < $keys_number; $i++) { $column_name = $keys[$i]; // We don't add "," after the last one if ($i == ($keys_number - 1)) { $column_names .= "`$keys[$i]`"; $values .= "'$my_array[$column_name]'"; } else { $column_names .= "`$keys[$i]`" . ", "; $values .= "'$my_array[$column_name]'" . ", "; } } // Proper end the column name and the value $column_names .= ")"; $values .= ")"; echo "Values = $values<br>"; // We compose the query $sql = "insert into `test` "; $sql .= $column_names; $sql .= ' values '; $sql .= $values; echo $sql; $result = mysql_query($sql); if (!$result) { die ('Invalid query: ' . mysql_error()); } } ?>