Work at SourceForge, help us to make it a better place! We have an immediate need for a Support Technician in our San Francisco or Denver office.

Close

db_mysqli.ini - php5 version

2005-02-23
2013-04-09
  • The Host Book
    The Host Book
    2005-02-23

    Class posted below has been tested by me for connectivity, query execution and meta data. It uses php5 object oriented syntax and is backwards compartible with all other phplib libraries (it uses the same method signature for all functions and does not remove any function that were present in earlier version of this library.)

    Thanks

    Manish
    ----------------------------------------------------
    <?php
    class DB_Sql{
        /* public: connection parameters */
        var $Host             = "";
        var $Database         = "";
        var $User             = "";
        var $Password         = "";
        
        /* public: configuration parameters */
        var $Auto_Free         = 0;## Set to 1 for automatic mysql_free_result()
        var $Debug             = 0;## Set to 1 for debugging messages.
        var $Halt_On_Error     = "no";## "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
        var $PConnect         = 0;## Set to 1 to use persistent database connections
        var $Seq_Table         = "db_sequence";
        
        /* public: result array and current row number */
        var $Record = array();
        var $Row;
        
        /* public: current error number and error text */
        var $Errno = 0;
        var $Error = "";
        
        /* public: this is an api revision, not a CVS revision. */
        var $type = "mysqli";
        var $revision = "1.2";
        
        /* private: link and query handles */
        var $Link_ID     = 0;
        var $Query_ID     = 0;
        var $objMySQLi    = false;
        var $objResult    = false;
        
        var $locked = false;## set to true while we have a lock
        
        /* public: constructor */
        function DB_Sql($query = ""){
            $this->query($query);
        }
        
        /* public: some trivial reporting */
        function link_id(){
            return $this->Link_ID;
        }
        
        function query_id(){
            return $this->Query_ID;
        }
       
        function connect($Database = "", $Host = "", $User = "", $Password = ""){
            /* Handle defaults */
            if ("" == $Database)     $Database = $this->Database;
            if ("" == $Host)         $Host = $this->Host;
            if ("" == $User)         $User = $this->User;
            if ("" == $Password)     $Password = $this->Password;
            
            /* Establish connection, select database */
            if ($this->objMySQLi == false){
                $this->objMySQLi = new mysqli($this->Host, $this->User, $this->Password, $this->Database);
            }
           
            if ($this->objMySQLi == false){
                $this->Errno = mysqli_connect_errno();
                $this->Error = mysqli_connect_error();
                $this->halt("connect($Host, $User, \$Password) failed.");
                return 0;
            }
           
            return $this->objMySQLi; // Return the mysqli object.
        }
       
        /* public: discard the query result */
        function free(){
            if ($this->objResult != false){
                $this->objResult->free();
            }
        }
       
        /* public: perform a query */
        function query($Query_String){
            /* Check if we have received a reql query string... */
            if ($Query_String == "")         return 0;
            /* Check if we are connected... */
            if ($this->connect() == false)    return 0;
           
            if ($this->Debug) printf("Debug: query = %s<br>\n", $Query_String);
           
            if ($this->objMySQLi != false){
                $this->Row             = 0;
                $this->objResult     = $this->objMySQLi->query($Query_String);
                if ($this->objResult === false){    // If the query failed.
                    $this->Errno = $this->objMySQLi->errno;
                    $this->Error = $this->objMySQLi->error;
                    $this->halt("Invalid SQL: ". $Query_String);
                }
            }
           
            # Will return nada if it fails. That's fine.
            return;
        }
       
        /* public: walk result set */
        function next_record(){
            $stat = false;
            if ($this->objResult === false){
                $this->halt("Function next_record() called with no query pending or a query execution error has ocurred.");
                return 0;
            }
            // Fetch data
            if ($this->objResult != false){
                $this->Record = $this->objResult->fetch_array(MYSQLI_BOTH);
                $this->Row     += 1;
                $this->Errno = $this->objMySQLi->errno;
                $this->Error = $this->objMySQLi->error;
                $stat = is_array($this->Record);
                if (!$stat && $this->Auto_Free){
                    // I don't think this is needed here. This may free memory related with the resultset prematurely.
                    // We need to free memory in the destructor part of this class so memory is automatically freed.
                    // $this->free();
                }
            }
            return $stat;
        }
       
        /* public: position in result set */
        function seek($pos = 0){
            if ($this->objResult != false){
                if ($this->objResult->data_seek($pos) === true){
                    $this->Row = $pos;
                }else{
                    $this->halt("Function seek($pos) failed: result has " . $this->num_rows(). " rows.");
                   
                    // Seek last row for backward compatibility.
                    $this->objResult->data_seek($this->num_rows());
                    $this->Row = $this->num_rows();
                    return 0;
                }
            }else{
                $this->halt("Function seek($pos) called with no result available or an unkown error has ocurred.");
                return 0;
            }
        }
       
        /* public: table locking */
        function lock($table, $mode="write"){
            $this->objMySQLi = $this->connect();
            // Prepare query
            $query = "lock tables ";
            if (is_array($table)){
                while (list($key, $value) = each($table)){
                    if ($key == "read" && $key !=0){
                        $query .="$value read, ";
                    }else{
                        $query .="$value $mode, ";
                    }
                }
                $query = substr($query, 0, -2);
            }else{
                $query .= "$table $mode";
            }
           
            if (($this->objResult = $this->objMySQLi->query($query)) === false){
                $this->Errno = $this->objMySQLi->errno;
                $this->Error = $this->objMySQLi->error;
                $this->halt("Function lock($table, $mode) failed ($query).");
                return 0;
            }
        }
       
        function unlock(){
            $this->objMySQLi = $this->connect();
            // Prepare query
            $query = "unlock tables ";
           
            if (($this->objResult = $this->objMySQLi->query($query)) === false){
                $this->Errno = $this->objMySQLi->errno;
                $this->Error = $this->objMySQLi->error;
                $this->halt("Function unlock() failed ($query).");
                return 0;
            }
        }
       
        /* public: evaluate the result (size, width) */
        function affected_rows(){
            return $this->objMySQLi->affected_rows();
        }
       
        function num_rows(){
            return $this->objMySQLi->num_rows();
        }
       
        function num_fields(){
            return $this->objResult->field_count;
        }
       
        /* public: shorthand notation */
        function nf(){
            return $this->num_rows();
        }
       
        function np(){
            print $this->num_rows();
        }
       
        function f($Name){
            return $this->Record[$Name];
        }
       
        function p($Name){
            print $this->Record[$Name];
        }
       
        /* public: sequence numbers */
        function nextid($seq_name){
            if ($this->lock($this->Seq_Table)){
                $this->query(sprintf("select nextid from %s where seq_name = '%s'", $this->Seq_Table, $seq_name));
                if ($this->next_record()){
                    $currentid = $this->Record['nextid'];
                }else{
                    // Lets try to insert a new sequence.
                    $currentid = 0;
                    $this->query(sprintf("insert into %s values('%s', %s)", $this->Seq_Table, $seq_name, $currentid));
                }
                // Update the nextid for this sequence
                $nextid = $currentid + 1;
                $this->query(sprintf("update %s set nextid = '%s' where seq_name = '%s'", $this->Seq_Table, $nextid, $seq_name));
                $this->unlock();
               
                // Return the nextid.
                return $nextid;
            }else{
                $this->halt("Cannot lock ". $this->Seq_Table . " - has it been created?");
                return 0;
            }
        }
       
        /* public: return table metadata */
        function metadata($table='',$full=false){
            $return = array();
            if ($table){
                $i=0;
                $this->query(sprintf("SHOW COLUMNS FROM %s", $table));
                while ($this->next_record()){
                    $arrFType                 = explode("(", $this->Record[1]); // Get field type.
                    $strFType                = $arrFType[0];
                    $strFLength                = substr($arrFType[1], 0, strlen($arrFType[1]) - 1);
                    $arrStrFLength            = explode(",", $strFLength);
                    if (sizeof($arrStrFLength) > 1){
                        $intFLength = 0;
                        for ($j = 0; $j < sizeof($arrStrFLength); $j++){
                            $intFLength += $arrStrFLength[$j];
                        }
                    }else{
                        $intFLength = $arrStrFLength[0];
                    }
                    // Populate data structure.
                    $return[$i]["table"]     = $table;
                    $return[$i]["name"]     = $this->Record[0];
                    $return[$i]["type"]     = $strFType;
                    $return[$i]["len"]         = $intFLength;
                    $return[$i]["flags"]     = $this->Record[0];
                    $i++;
                }
                return $return;
            }else{
                $this->halt("Function metadata() called without specifying table.");
            }
        }
       
        /* private: error handling */
        function halt($msg){
            if (is_object($this->objMySQLi)){
                $this->Errno = $this->objMySQLi->errno;
                $this->Error = $this->objMySQLi->error;
            }
            if ($this->Halt_On_Error == "no") return;
            $this->haltmsg($msg);
            if ($this->Halt_On_Error != "report") die("Session halted.");
        }
       
        function haltmsg($msg){
            printf("</td></tr></table><b>Database error:</b> %s<br>\n", $msg);
            printf("<b>MySQL Error</b>: %s (%s)<br>\n", $this->Errno, $this->Error);
        }
       
        function table_names(){
            $this->query("SHOW TABLES");
            $i=0;
            while ($this->next_record()){
                $return[$i]["table_name"]        = $this->Record[0];
                $return[$i]["tablespace_name"]    = $this->Database;
                $return[$i]["database"]            = $this->Database;
                $i++;
            }
            return $return;
        }
    }
    ?>