Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

db_mysqli.ini

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

    Hello:

    I have been looking for mysqli features in phplib for some time. It seems like there is no initiative within phplib community towards mysqli or have I been looking in the wrong place?

     
    • The Host Book
      The Host Book
      2005-02-21

      <?php
      /*
      * Session Management for PHP3
      *
      * Copyright (c) 1998-2000 NetUSE AG
      *                    Boris Erdmann, Kristian Koehntopp
      *
      * $Id: db_mysqli.inc,v 1.11 2002/08/07 19:33:57 layne_weathers Exp $
      *
      */

      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     = "mysql";
        var $revision = "1.2";

        /* private: link and query handles */
        var $Link_ID  = 0;
        var $Query_ID = 0;

        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;
        }

        /* public: connection management */
        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 ( 0 == $this->Link_ID ) {
         
            if(!$this->PConnect) {
              $this->Link_ID = mysqli_connect($Host, $User, $Password);
            } else {
              $this->Link_ID = mysqli_pconnect($Host, $User, $Password);
            }
            if (!$this->Link_ID) {
              $this->halt("connect($Host, $User, \$Password) failed.");
              return 0;
            }

            if (!mysqli_select_db($this->Link_ID, $Database)) {
              $this->halt("cannot use database ".$Database);
              return 0;
            }
          }
         
          return $this->Link_ID;
        }

        /* public: discard the query result */
        function free() {
            mysqli_free_result($this->Query_ID);
            $this->Query_ID = 0;
        }

        /* public: perform a query */
        function query($Query_String) {
          /* No empty queries, please, since PHP4 chokes on them. */
          if ($Query_String == "")
            /* The empty query string is passed on from the constructor,
             * when calling the class without a query, e.g. in situations
             * like these: '$db = new DB_Sql_Subclass;'
             */
            return 0;

          if (!$this->connect()) {
            return 0; /* we already complained in connect() about that. */
          };

          # New query, discard previous result.
          if ($this->Query_ID) {
            $this->free();
          }

          if ($this->Debug)
            printf("Debug: query = %s<br>\n", $Query_String);

          $this->Query_ID = mysqli_query($this->Link_ID, $Query_String);
          $this->Row   = 0;
          $this->Errno = mysqli_errno($this->Link_ID);
          $this->Error = mysqli_error($this->Link_ID);
          if (!$this->Query_ID) {
            $this->halt("Invalid SQL: ".$Query_String);
          }

          # Will return nada if it fails. That's fine.
          return $this->Query_ID;
        }

        /* public: walk result set */
        function next_record() {
          if (!$this->Query_ID) {
            $this->halt("next_record called with no query pending.");
            return 0;
          }

          $this->Record = mysqli_fetch_array($this->Query_ID);
          $this->Row   += 1;
          $this->Errno  = mysqli_errno($this->Link_ID);
          $this->Error  = mysqli_error($this->Link_ID);

          $stat = is_array($this->Record);
          if (!$stat && $this->Auto_Free) {
            $this->free();
          }
          return $stat;
        }

        /* public: position in result set */
        function seek($pos = 0) {
          $status = mysqli_data_seek($this->Query_ID, $pos);
          if ($status)
            $this->Row = $pos;
          else {
            $this->halt("seek($pos) failed: result has ".$this->num_rows()." rows.");

            /* half assed attempt to save the day,
             * but do not consider this documented or even
             * desireable behaviour.
             */
            mysqli_data_seek($this->Query_ID, $this->num_rows());
            $this->Row = $this->num_rows();
            return 0;
          }

          return 1;
        }

        /* public: table locking */
        function lock($table, $mode = "write") {
          $query = "lock tables ";
          if(is_array($table)) {
            while(list($key,$value) = each($table)) {
              // text keys are "read", "read local", "write", "low priority write"
              if(is_int($key)) $key = $mode;
              if(strpos($value, ",")) {
                $query .= str_replace(",", " $key, ", $value) . " $key, ";
              } else {
                $query .= "$value $key, ";
              }
            }
            $query = substr($query, 0, -2);
          } elseif(strpos($table, ",")) {
            $query .= str_replace(",", " $mode, ", $table) . " $mode";
          } else {
            $query .= "$table $mode";
          }
          if(!$this->query($query)) {
            $this->halt("lock() failed.");
            return false;
          }
          $this->locked = true;
          return true;
        }
       
        function unlock() {

          // set before unlock to avoid potential loop
          $this->locked = false;

          if(!$this->query("unlock tables")) {
            $this->halt("unlock() failed.");
            return false;
          }
          return true;
        }

        /* public: evaluate the result (size, width) */
        function affected_rows() {
          return mysqli_affected_rows($this->Link_ID);
        }

        function num_rows() {
          return mysqli_num_rows($this->Query_ID);
        }

        function num_fields() {
          return mysqli_num_fields($this->Query_ID);
        }

        /* public: shorthand notation */
        function nf() {
          return $this->num_rows();
        }

        function np() {
          print $this->num_rows();
        }

        function f($Name) {
          if (isset($this->Record[$Name])) {
            return $this->Record[$Name];
          }
        }

        function p($Name) {
          if (isset($this->Record[$Name])) {
            print $this->Record[$Name];
          }
        }

        /* public: sequence numbers */
        function nextid($seq_name) {
          /* if no current lock, lock sequence table */
          if(!$this->locked) {
            if($this->lock($this->Seq_Table)) {
              $locked = true;
            } else {
              $this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
              return 0;
            }
          }
         
          /* get sequence number and increment */
          $q = sprintf("select nextid from %s where seq_name = '%s'",
                     $this->Seq_Table,
                     $seq_name);
          if(!$this->query($q)) {
            $this->halt('query failed in nextid: '.$q);
            return 0;
          }
         
          /* No current value, make one */
          if(!$this->next_record()) {
            $currentid = 0;
            $q = sprintf("insert into %s values('%s', %s)",
                       $this->Seq_Table,
                       $seq_name,
                       $currentid);
            if(!$this->query($q)) {
              $this->halt('query failed in nextid: '.$q);
              return 0;
            }
          } else {
            $currentid = $this->f("nextid");
          }
          $nextid = $currentid + 1;
          $q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
                     $this->Seq_Table,
                     $nextid,
                     $seq_name);
          if(!$this->query($q)) {
            $this->halt('query failed in nextid: '.$q);
            return 0;
          }
         
          /* if nextid() locked the sequence table, unlock it */
          if($locked) {
            $this->unlock();
          }
         
          return $nextid;
        }

        /* public: return table metadata */
        function metadata($table = "", $full = false) {
          $count = 0;
          $id    = 0;
          $res   = array();

          /*
           * Due to compatibility problems with Table we changed the behavior
           * of metadata();
           * depending on $full, metadata returns the following values:
           *
           * - full is false (default):
           * $result[]:
           *   [0]["table"]  table name
           *   [0]["name"]   field name
           *   [0]["type"]   field type
           *   [0]["len"]    field length
           *   [0]["flags"]  field flags
           *
           * - full is true
           * $result[]:
           *   ["num_fields"] number of metadata records
           *   [0]["table"]  table name
           *   [0]["name"]   field name
           *   [0]["type"]   field type
           *   [0]["len"]    field length
           *   [0]["flags"]  field flags
           *   ["meta"][field name]  index of field named "field name"
           *   This last one could be used if you have a field name, but no index.
           *   Test:  if (isset($result['meta']['myfield'])) { ...
           */

          // if no $table specified, assume that we are working with a query
          // result
          if ($table) {
            $this->connect();
            $id = mysqli_list_fields($this->Database, $table);
            if (!$id) {
              $this->halt("Metadata query failed.");
              return false;
            }
          } else {
            $id = $this->Query_ID;
            if (!$id) {
              $this->halt("No query specified.");
              return false;
            }
          }

          $count = mysqli_num_fields($id);

          // made this IF due to performance (one if is faster than $count if's)
          if (!$full) {
            for ($i=0; $i<$count; $i++) {
              $res[$i]["table"] = mysqli_field_table ($id, $i);
              $res[$i]["name"]  = mysqli_field_name  ($id, $i);
              $res[$i]["type"]  = mysqli_field_type  ($id, $i);
              $res[$i]["len"]   = mysqli_field_len   ($id, $i);
              $res[$i]["flags"] = mysqli_field_flags ($id, $i);
            }
          } else { // full
            $res["num_fields"]= $count;
         
            for ($i=0; $i<$count; $i++) {
              $res[$i]["table"] = mysqli_field_table ($id, $i);
              $res[$i]["name"]  = mysqli_field_name  ($id, $i);
              $res[$i]["type"]  = mysqli_field_type  ($id, $i);
              $res[$i]["len"]   = mysqli_field_len   ($id, $i);
              $res[$i]["flags"] = mysqli_field_flags ($id, $i);
              $res["meta"][$res[$i]["name"]] = $i;
            }
          }
         
          // free the result only if we were called on a table
          if ($table) {
            mysqli_free_result($id);
          }
          return $res;
        }

        /* public: find available table names */
        function table_names() {
          $this->connect();
          $h = mysqli_query("show tables", $this->Link_ID);
          $i = 0;
          while ($info = mysqli_fetch_row($h)) {
            $return[$i]["table_name"]      = $info[0];
            $return[$i]["tablespace_name"] = $this->Database;
            $return[$i]["database"]        = $this->Database;
            $i++;
          }
         
          mysqli_free_result($h);
          return $return;
        }

        /* private: error handling */
        function halt($msg) {
          $this->Error = mysqli_error($this->Link_ID);
          $this->Errno = mysqli_errno($this->Link_ID);

          if ($this->locked) {
            $this->unlock();
          }

          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);
        }

      }
      ?>

       
    • The Host Book
      The Host Book
      2005-02-21

      <h2>metadata function needs rewrite</h2>

      Attached db_mysqli.ini file works for the most part. Meta_data related functions are now obsolete in mysqli so this function needs rewrite using "SHOW COLUMNS FROM TABLE" query.

      <b>Any takers?</b>

       
    • Layne Weathers
      Layne Weathers
      2005-02-22

      hostbook,

      The mysqli features are of interest to me as well - I've been using bind variables in Oracle for some time now and appreciate how they make my code more readable as well as the performance gains they provide.

      The primary barrier to adding mysqli to PHPLib (at least, this is the reason I haven't worked on it) is that it is PHP 5 only while the rest of PHPLib is built for PHP 3 and PHP 4. It may surprise you that some people, myself included, are still happily running 4 (and even 3 if I understand correctly) and have not even installed PHP 5 in a dev environment.

      That being said, I will make it a personal goal to install PHP 5 and test your class in the next month. If I can get that done and the class works as expected (I assume it would need to run in PHP 4 compatible mode to be useful with the rest of PHPLib) I will commit it for you unless someone else beats me to it.

       
    • The Host Book
      The Host Book
      2005-02-22

      Hi Layne:

      Thanks for looking into this. I think, it may be a well worth upgrade for some folks. I still have to figure out a better replacement for metadata function and related code but beyond that basic connectivity will work in the class posted earlier. I will ofcourse post any updated code so you can take a look.

      Manish