Next Previous Contents

17. Appendix G Microsoft SQL Server DB Wrapper Example

Submitted by: Joe Thong [email protected] Site URL: http://phpdb.linuxbox.com

Description: A PHP database wrapper for various database servers with a powerful Recordset for result data manipulation. Database results are flushed automatically by phpDB.

To get this file, in the web-browser, save this file as 'Text' type as phpDB-mssql.lib


<?php
/*
Name: phpDB Microsoft SQL Server module
Version: 1.02bR6
Description: A PHP database wrapper for various 
        database servers with a powerful
        Recordset for result data manipulation.  Database 
        results are flushed automatically by phpDB.  
*/
// Define this module, to prevent double class declaration
if (!defined("_PHPDB_ABSTRACT_LAYER")) 
{
        define("_PHPDB_ABSTRACT_LAYER", 1 );
}
else
        return;

//------------------------------
         Class Name: phpDB
//------------------------------
class phpDB 
{
        // public variables
        var $version = '1.02bR6'; // Version number of phpDB

        // This variable keeps what database type is going 
        // to be used.  Current supported database server 
        // are MySQL, MSQL, SQL Server, PostgreSQL and Sybase
        var $databaseType = ''; 
        var $databaseName = ''; // Specifies which database is going to be used

        // The hostname of the database server, port 
        // number is optional.  e.g: "db.devNation.com"
        var $hostname = ''; 

        var $username = ''; // to connect to the database server
        var $password = '';     // Password for the username
        
        // Private variables ----- starts with underscore

        // An array of executed querys. For results cleanup purposes
        var $_queryIDList = array();

        // The returned link identifier whenever a 
        // successful database connection is made
        var $_connectionID      = -1;
                
        // A variable which was used to keep the returned last 
        // error message.  The value will then returned 
        // by the errorMsg() function
        var $_errorMsg = ''; 
        // This variable keeps the last created result link identifier
        var $_queryID = -1;     

        // A boolean variable to state whether its a 
        // persistent connection or normal connection
        var     $_isPersistentConnection = false;       
        
        // Holds the newly created result object, returned 
        // via the execute() method
        var $_tempResultObj = '';       

        //      A constructor function for the phpDB object.  
        // When initializing, specify the dbType i.e: "mysql",
        // "msql", "postgresql", "mssql", and "sybase"
        function phpDB($dbType = "mssql") 
        {
                switch ($dbType) 
                {
                        case "mysql":
                        case "msql":
                        case "postgresql":
                        case "mssql":
                        case "sybase":                                          
                        case "informix":                                                
                                $this->databaseType = $dbType;
                                break;
                        default:
                                return false;
                }
        }

        // Returns: A positive link identifier on success, 
        // or false on error.   Connect to the server with 
        // the provided arguments. The connection to the server 
        // will be closed when the script terminates, unless 
        // close() function is called beforehand.
        function connect($argHostname = "", $argUsername = "", 
                $argPassword = "", $argDatabaseName = "") 
        {
                $boolDBSelected;
                if ($argHostname != "") {
                        $this->hostname = $argHostname;
                }
                if ($argUsername != "") {
                        $this->username = $argUsername;
                }
                if ($argPassword != "") {
                        $this->password = $argPassword;
                }
                if ($argDatabaseName != "") {
                        $this->databaseName = $argDatabaseName;
                }                               
                
                $this->_connectionID = @mssql_connect($this->hostname, $this->username, $this->password);
                
                if ($this->databaseName && $this->_connectionID) {
                        $boolDBSelected = @mssql_select_db($this->databaseName);
                        if(!$boolDBSelected) {  /*      If DB selection fails   */
                                @mssql_close($this->_connectionID);     /*      Close the current connection    */
                                return false;   
                        }
                }               
                return $this->_connectionID;
        }

        // Returns: A positive link identifier on success, or 
        // false on error Connect to the server with the provided
        // arguments. The connection to the server will not be closed 
        // when the script terminates. Instead it will be kept for 
        // later future use
        function pconnect($argHostname = "", $argUsername = "", 
                $argPassword = "", $argDatabaseName = "") 
        {
                $boolDBSelected;
                if ($argHostname != "") {
                        $this->hostname = $argHostname;
                }
                if ($argUsername != "") {
                        $this->username = $argUsername;
                }
                if ($argPassword != "") {
                        $this->password = $argPassword;
                }
                if ($argDatabaseName != "") {
                        $this->databaseName = $argDatabaseName;
                }                               
                
                $this->_connectionID = @mssql_pconnect($this->hostname, $this->username, $this->password);
                if ($this->_connectionID) {
                        $this->_isPersistentConnection = true;                          
                }
                
                if ($this->databaseName && $this->_connectionID) {
                        $boolDBSelected = @mssql_select_db($this->databaseName);
                        if(!$boolDBSelected) {  /*      if DB selection fails   */
                                return false;   /*      Persistent connection can't be closed   */
                        }
                }               
                return $this->_connectionID;
        }
        
        //      Returns: true on success, false on error Select the 
        // database name to be used
        function selectDB($dbName) 
        {
                $this->databaseName = $dbName;
                if ($this->_connectionID) {
                        return @mssql_select_db($dbName);               
                }
                else {
                        /*      No database selected    */
                        return false;
                }                       
        }

        // Returns: the Recordset object disregard success or 
        // failure Send the sql statement to the database server
        function execute($sql = "") 
        {
                $this->_queryID = @mssql_query($sql, $this->_connectionID);
                // Instantiate an object without considering whether 
                // the query return any results or not
                $this->_tempResultObj = new Recordset($this->_queryID);
                $this->_insertQuery($this->_queryID);
                return $this->_tempResultObj;
        }

        // Returns: the last error message from previous database
        // operation Note: This function is NOT available for
        // Microsoft SQL Server
        function errorMsg() 
        {
                $this->_errorMsg = "errorMsg() is not available for Microsoft SQL Server";
            return $this->_errorMsg;
        }

        /*      Returns: true on success, false on failure
                Close the database connection.  */      
        function close() {
                if ($this->_queryIDList && sizeof($this->_queryIDList > 0)) {
                        while(list($_key, $_resultID) = each($this->_queryIDList)) {
                                @mssql_free_result($_resultID);
                        }
                }
                // If its not a persistent connection, then 
                // only the connection needs to be closed
                if ($this->_isPersistentConnection != true) {   
                        return @mssql_close($this->_connectionID);
                }
                else {
                        return true;
                }
        }
        
        // A PRIVATE function used by the constructor function of 
        // the query object.  insert the successful returned 
        // query id to the query id list.  Used for later results
        // cleanup.  A private function that's never meant to be 
        // used directly
        function _insertQuery($query_id) {
                $this->_queryIDList[] = $query_id;
        }               
}

//---------------------------------------
         Class Name: Recordset
//---------------------------------------
class Recordset 
{
        /*      public variables        */
        var $fields;
        // indicates that the current record position is 
        // before the first record in a Recordset object
        var $BOF = null;
        // indicates that the current record position is 
        // after the last record in a Recordset object
        var $EOF = null;

        // Private variables
        var $_numOfRows = -1; // NEVER change the value!  READ-ONLY!
        var $_numOfFields = -1; // NEVER change the value!  READ-ONLY!
        
        // Holds anything that was returned from the 
        // database specific functions
        var $_tempResult = '';  
        // This variable keeps the result link identifier
        var $_queryID = -1;     
        // This variable keeps the current row in the Recordset
        var $_currentRow = -1;

        // Returns: query id on success and false if 
        // failed Constructor function
        function Recordset($queryID) 
        {
                $this->_queryID = $queryID;
                if ($queryID) {
                        $this->_numOfRows = @mssql_num_rows($this->_queryID);
                        $this->_numOfFields = @mssql_num_fields($this->_queryID);
                }
                else {
                        $this->_numOfRows = 0;
                        $this->_numOfFields = 0;
                }
                // If result set contains rows  
                if ($this->_numOfRows > 0 && $this->_currentRow == -1) {
                        $this->_currentRow = 0;
                        $this->fields = @mssql_fetch_array($this->_queryID);
                        $this->EOF = false;
                        $this->BOF = false;
                }
                return $this->_queryID;
        }
                
        // Returns: true if successful, false if fail Set 
        // the Recordset pointer to a specified field offset. 
        // If the next call to fetchField() won't include a 
        // field offset, this field would be returned
        function fieldSeek($fieldOffset = -1) {
                $this->_tempResult = @mssql_field_seek($this->_queryID, $fieldOffset);
                return $this->_tempResult;
        }

        // Returns: an object containing field information. 
        // Get column information in the Recordset object. 
        // fetchField() can be used in order to obtain information
        // about fields in a certain query result. If the field 
        // offset isn't specified, the next field that wasn't yet 
        // retrieved by fetchField() is retrieved
        function fetchField($fieldOffset = -1) {
                if ($fieldOffset != -1) {
                        $this->_tempResult = @mssql_fetch_field($this->_queryID, $fieldOffset);
                }
                // The $fieldOffset argument is not provided thus its -1
                else if ($fieldOffset == -1) {  
                        $this->_tempResult = @mssql_fetch_field($this->_queryID);
                }
                return $this->_tempResult;
        }

        // Returns: true if there still rows available, or false 
        // if there are no more rows.  Moves to the next row in a 
        // specified Recordset object and makes that record the current 
        // row and the data corresponding to the row will be retrieved
        // into the fields collection.  Note: Unlike the moveRow() method, 
        // when _currentRow is getNumOfRows() - 1, EOF will immediately be 
        // true.  If row number is not provided, the function will point 
        // to the first row automatically
        function nextRow() 
        {
                if ($this->getNumOfRows() > 0) {                        
                        $this->fields = array();
                        $this->_currentRow++;
                        $this->fields = @mssql_fetch_array($this->_queryID);
                        // This is not working.  True all the time
                        if ($this->fields) {    
                                $this->_checkAndChangeEOF($this->_currentRow - 1);
                                return true;
                        }
                }
                $this->EOF = true;
                return false;
        }       
                        
        // Returns: true on success, false on failure moveRow() 
        // moves the internal row pointer of the Recordset object 
        // to point to the specified row number and the data 
        // corresponding to the row will be retrieved into the fields 
        // collection.  If row number is not provided, the function will 
        // point to the first row automatically
        function moveRow($rowNumber = 0) 
        {
                if ($rowNumber == 0) {
                        return $this->firstRow();
                }
                else if ($rowNumber == ($this->getNumOfRows() - 1)) {
                        return $this->lastRow();
                }
                if ($this->getNumOfRows() > 0 && $rowNumber < $this->getNumOfRows()) {       
                        $this->fields = null;
                        $this->_currentRow = $rowNumber;
                        if(@mssql_data_seek($this->_queryID, $this->_currentRow)) {
                                $this->fields = @mssql_fetch_array($this->_queryID);
                                /*      This is not working.  True all the time */
                                if ($this->fields) {
                                        // No need to call _checkAndChangeEOF() because
                                        // the possibility of moving to the last row has 
                                        // been handled by the above code
                                        $this->EOF = false; 
                                        return true;
                                }
                        }
                }
                $this->EOF = true;
                return false;
        }

        // Returns: true on success, false on failure firstRow() moves
        // the internal row pointer of the Recordset object to the first 
        // row and the data corresponding to the row will be retrieved 
        // into the fields collection
        function firstRow() 
        {
                if ($this->getNumOfRows() > 0) {
                        $this->fields = array();
                        $this->_currentRow = 0;
                        if (@mssql_data_seek($this->_queryID, $this->_currentRow)) {
                                $this->fields = @mssql_fetch_array($this->_queryID);
                                $this->EOF = false;
                                /*      This is not working.  True all the time */
                                if ($this->fields) {    
                                        return true;    
                                }
                        }
                }
                $this->EOF = true;
                return false;           
        }

        // Returns: true on success, false on failure lastRow() moves 
        // the internal row pointer of the Recordset object to the last
        // row and the data corresponding to the row will be retrieved 
        // into the fields collection
        function lastRow() 
        {
                if ($this->getNumOfRows() > 0) {        
                        $this->fields = array();        
                        $num_of_rows = $this->getNumOfRows();
                        $this->_tempResult = @mssql_data_seek($this->_queryID, --$num_of_rows);
                        if ($this->_tempResult) {
                                /*      $num_of_rows decemented at above        */
                                $this->_currentRow = $num_of_rows;
                                $this->fields = @mssql_fetch_array($this->_queryID);
                                /*      This is not working.  True all the time */
                                if ($this->fields) {
                                        /*      Special case for making EOF false.      */
                                        $this->EOF = false;     
                                        return true;
                                }
                        }
                }
                $this->EOF = true;
                return false;
        }

        // close() only needs to be called if you are worried about using
        // too much memory while your script is running. All associated 
        // result memory for the specified result identifier will 
        // automatically be freed
        function close() {
                $this->_tempResult = @mssql_free_result($this->_queryID);               
                return $this->_tempResult;
        }

        // Returns: the number of rows in a result set.  Get 
        // number of rows in result
        function getNumOfRows() {
                return $this->_numOfRows;
        }

        /*      Returns: the number of fields in a result set. 
        Get number of fields in result. */
        function getNumOfFields() {
                return $this->_numOfFields;
        }       

        /*      Check and change the status of EOF.     */              
        function _checkAndChangeEOF($currentRow) {
                if ($currentRow >= ($this->_numOfRows - 1)) {
                        $this->EOF = true;
                }               
                else {
                        $this->EOF = false;             
                }
        }
}
?>


Next Previous Contents