[PHP] 91th's DBConnector

[admLoki]

Hi 2 all again :] .
Our team uses our database connector which i wrote 1-2 months ago .
It's useful(as i think ;]) and can be used in your scripts .
Thx for advance :] .

<?php

/**
* @version 1.6 beta
* date 13:29 27.11.2007
*/

class Database {
  /** var to keep the sql query code **/
  public $_sql; 
  /** var to keep connector resource **/
  private $_resource;
  /** var to keep last query results **/
  private $_cursor;
  /** var to keep table prefix **/
  public $_tableprefix;
  /** var to keep query counts **/
  public $_ticker = 0;
  /** var to keep limit value **/
  var $_limit_value = 0;
  /** var to keep limit offset **/
  var $_limit_offset = 0;
  /** var to keep affected rows **/
  var $_affected = 0;
  /** safe transactions? **/
  var $_safe_transaction = 0;
  /** debug variable **/
  var $_sql_queries = array();
  /** var version **/
  private $version = '1.6 beta';
  
  /**
  * database class constructor
  * @param host
  * @param username
  * @param password
  * @param database name
  * @param table prefix
  * @param pconnect
  */
  public function Database( $host='localhost', $username, $password, $dbname, $tableprefix, $pconn = '0' ) {
          // checking for base functions
          if( $pconn ) {
            // checking for persistent connection function
            if( !function_exists( "mysql_pconnect" ) ) {
                $this->showError("No mysql functions exists .");
            } else {
              if(!($this->_resource = mysql_pconnect( $host, $username, $password ))) {
                $this->showError("Cant connect to database .");              
              }
            }
          } else {
            // checking for normal connection function
            if( !function_exists( "mysql_connect" ) ) {
                $this->showError("No mysql functions exists .");
            } else {
              if(!($this->_resource = mysql_connect( $host, $username, $password ))) {
                $this->showError("Cant connect to database .");
              }
            }
          }
      if(!(mysql_select_db( $dbname, $this->_resource ))) {
            $this->showError("Cannot select database .");
          }
          $this->_tableprefix = $tableprefix;
          
          if(defined('FORCE_LANG')) {
             $this->simple_query("/*!40101 SET NAMES '".FORCE_LANG."' */");
          }
  }

  /**
  * function setQuery
  * @param sql code
  * @param limit
  * @param offset
  * @param pseudo prefix
  * @since 1.0
  */
  public function setQuery( $sql, $limit = 0, $offset = 0, $prefix = '#__' ) {
          $this->_sql = $this->repTBLprefix( $prefix, $sql );
          $this->_limit_value = $limit;
          $this->_limit_offset = $offset;           
  }
  
  /**
  * function repTBLprefix
  * @param pseudo-prefix
  * @param input sql code
  * @since 1.0
  */
  private function repTBLprefix( $prefix, $sqlcode ) {
          return str_replace( $prefix, $this->_tableprefix, $sqlcode );
  }

  /**
  * function Query
  * @param bool $sage safe transaction as alias to function
  * @since 1.0
  * @return resource
  * General cleaned in v 1.4
  */
  public function Query($safe=0) {
          //Check whether the end of SQL query exists
          if( substr($this->_sql,-1,1) == ";" ) {
            $this->_sql = substr($this->_sql,0,-1);
          }
          if( $this->_limit_value > 0 || $this->_limit_offset > 0 ) {
            $this->_sql .= " LIMIT $this->_limit_offset, $this->_limit_value";
          }           
          
          if($safe && $this->_safe_transaction) {
            $this->_cursor = $this->query_batch();
          } else {
            $this->_cursor = mysql_query( $this->_sql, $this->_resource );
          }
                    
          if(!$this->_cursor) {
             return false;
          }

          //Affected rows(for testing purpose)
          $act = substr($this->_sql,0,6);

          if((strtolower($act) == "insert") || (strtolower($act) == "update")) {
            $this->_affected = $this->getAffectedRows();
          }            

          $this->countQueries();
          $this->_sql_queries[] = $this->_sql;
          return $this->_cursor;
          
  }

  /**
  * function query_batch
  * @param none
  * @return bool
  * @since 1.4
  */
  public function query_batch() {
          $si = mysql_get_server_info( $this->_resource );
      preg_match_all( "/(\d+)\.(\d+)\.(\d+)/i", $si, $m );

          if($m[1] >= 4) {
            $this->_sql = 'START TRANSACTION;' . $this->_sql . '; COMMIT;';
      } else {
            if($m[2] >= 23 && $m[3] >= 19) {
          $this->_sql = 'BEGIN WORK;' . $this->_sql . '; COMMIT;';
            } else {
              if($m[2] >= 23 && $m[3] >= 17) {
        $this->_sql = 'BEGIN;' . $this->_sql . '; COMMIT;';
          }
        }
          }

          $query_split = preg_split ("/[;]+/", $this->_sql);
      $line = 0;
      foreach($query_split as $command_line) {
                 $line++;
         $command_line = trim( $command_line );
         if($command_line != '') {
           $this->_cursor = mysql_query( $command_line, $this->_resource );
           if(!$this->_cursor) {
             $this->showError('Error in safe transaction at line $line');
                   }
         }
          }
  }
  
  /**
  * function getAffectedRows
  * @param none
  * @sice 1.0
  * @return array
  */
  public function getAffectedRows() {
          return mysql_affected_rows( $this->_resource );
  }
  
  /**
  * function getQuery
  * @param none
  * @since 1.0
  * @return string
  */
  function getQuery() {
          return htmlspecialchars( $this->_sql ) ;
  }
  
  /**
  * function Analyze
  * @param none
  * @since 1.0
  * Diagnostic function
  */
  function Analyze() {
          $temp = $this->_sql;
          $this->_sql = "EXPLAIN $this->_sql";
          $this->query();

          if(!($cur = $this->query())) {
              return null;
          }

          $first = true;
          $buf = "<table cellspacing=\"1\" cellpadding=\"2\" border=\"0\" bgcolor=\"#000000\" align=\"center\">";
          $buf .= $this->getQuery();

          while ($row = mysql_fetch_assoc( $cur )) {

                if ($first) {
                   $buf .= "<tr>";
                   foreach ($row as $k=>$v) {
                           $buf .= "<th bgcolor=\"#ffffff\">$k</th>";
                   }
                $buf .= "</tr>";
                $first = false;
                }

                $buf .= "<tr>";
                foreach ($row as $k=>$v) {
                        $buf .= "<td bgcolor=\"#ffffff\">$v</td>";
                }
                $buf .= "</tr>";
          }

          $buf .= "</table><br />&nbsp;";

          mysql_free_result( $cur );
          $this->_sql = $temp;
          return "<div style=\"background-color:#FFFFCC\" align=\"left\">$buf</div>";
  }  

  /**
  * function getNumRows
  * @param MySQL connection resource
  * @since 1.0
  * @return integer
  */
  public function getNumRows( $cur=null ) {
          return mysql_num_rows( $cur ? $cur : $this->_cursor );
  }

  /**
  * function LoadObject
  * @param object
  * @since 1.0
  * @return 
  */ 
  public function LoadObject( &$object ) {
          if (!($cur = $this->Query())) {
            return false;
          }

          if($res = mysql_fetch_assoc( $cur )) {
            mysql_free_result( $cur );
            BindArrayToObject( $array, $object );

            return true;
          } else {
            return false;
          }
  }

  /**
  * function showError
  * @param msg
  * @since 1.0
  * @notice shows error
  * @notice must be deprecated
  */
  public function showError( $msg='Unknown internal error .' ) {
          $err = "<div align=left><textarea style='width:500px;height:200px;'>" . $msg . "\n\nMySQL returned error :\n" . mysql_error() . "\n\nLast SQL query was :\n" . $this->getQuery() . "</textarea></div>" ;
          print( $err );
          exit();
  }
  
  /**
  * function retMySQLerr
  * @param none
  * @return mysql_error() answer
  * @since 1.0
  */
  private function retMySQLerr() {
          return mysql_error();
  }

  /**
  * function countQueries
  * @param none
  * @return query count
  * @since 1.3 as deprecated getCount()
  */
  private function countQueries() {
          return ++$this->_ticker;
  }

  /**
  * function getEscaped
  * @param text
  * @since 1.3
  * @return escaped string
  */
  private function getEscaped( $text ) {
          if(version_compare(phpversion(), '4.3.0', '<')) {
            $string = mysql_escape_string($text);
      } else {
            $string = mysql_real_escape_string($text);
      }
    
      return $string;
  }

/**
* title Small functions section
* author admLoki
* created <???>
* updated 27.11.2007 d-m-Y
*/

  /**
  * function get_table_fields
  * @param table name
  * @return array
  * @since 1.4
  * @notice
  */
  private function get_table_fields($tbl) {
          $this->setQuery("SHOW FIELDS FROM $tbl");

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          while($res = mysql_fetch_array($cur)) {
               $fields[] = $res['Field'];
          }

          return $fields;
  }

  /**
  * function simple_insert
  * @param target table
  * @param array of chains
  * @return boolean true,false;
  * @since 1.4
  * @example
  * $db->simple_insert("#__test",array("id"=>"value","testfield"=>"value");
  * @notice function is fucking awesome!
  */
  public function simple_insert($tbl, $arr) {
          if(!is_array($arr)) {
            trigger_error("wrong parameter 2 for simple_insert",E_USER_ERROR);
          }

          $fields = $this->get_table_fields($tbl);
          $fi_str = '';
          $fi_cn = count($fields);

          foreach($fields as $k => $v) {
                 if($k != ($fi_cn - 1)) {
                   $fi_str .= "`$v`,";
                 } else {
                   $fi_str .= "`$v`";
                 }
          }

          $va_cn = count($arr);
          $va_i = 0;
          $va_str = '';

          foreach($fields as $k => $v) {
                 if(eregi($v,$fi_str)) {
                   $pass = true;
                   continue;
                 } else {
                   $pass = false;
                   break;
                 }
          }

          if(!$pass) {
            trigger_error("field names mismatch in simple_insert",E_USER_ERROR);
          }

          foreach($arr as $k => $v) {
                 if($va_i != ($va_cn - 1)) {
                   $va_str .= "'$v',";
                 } else {
                   $va_str .= "'$v'";
                 }
                 $va_i++;
          }
                     
          $compiled_sql = "INSERT INTO `$tbl` ( $fi_str ) VALUES ( $va_str )";
          $this->setQuery($compiled_sql);

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          return $cur;
  }

  /**
  * function simple_update
  * @param table name
  * @param array of chains
  * @since 1.4
  * @example
  * @see simple_insert
  */
  public function simple_update($tbl, $arr, $where=0) {
          if(!is_array($arr)) {
            trigger_error("wrong parameter 2 in simple_update",E_USER_ERROR);
          }

          $va_cn = count($arr);
          $va_str = '';
          $va_i = 0;

          foreach($arr as $k => $v) {
                 if($va_i != ($va_cn - 1)) {
                   $va_str .= "$k = '$v',";
                 } else {
                   $va_str .= "$k = '$v'";
                 }
                 $va_i++;
          }
          
          $compiled_sql = "UPDATE $tbl SET $va_str";

          if($where) {
            $compiled_sql .= " WHERE $where";
          }
          
          $this->setQuery($compiled_sql);

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          return $cur;
  }

  /**
  * function simple_select
  * @param table name
  * @param required fields
  * @param 'WHERE' option
  * @param various options(LIMIT etc.)
  * @return cursor resource or 'false'
  * @since 1.4
  * @example $db->simple_select("#__test","id,testfield","id=1");
  */  
  public function simple_select($tbl, $get_str='*', $where=0, $optional='') {
          $compiled_sql = "SELECT $get_str FROM $tbl";

          if($where) {
            $compiled_sql .= " WHERE $where $optional";
          }

          $this->setQuery($compiled_sql);

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          return $cur;
  }

  /**
  * function simple_delete
  * @param table name
  * @param 'WHERE' option
  * @return cursor resource or 'false'
  * @since 1.4
  */
  function simple_delete($tbl, $where=0) {
          $compiled_sql = "DELETE FROM $tbl";
     
          if($where) {
            $compiled_sql .= " WHERE $where";
          } else {
            $compiled_sql .= " WHERE 1";
          }

          $this->setQuery($compiled_sql);

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          return $cur;
  }
  
  /**
  * function simple_select_exec
  * @see simple_select
  * @param same as simple_select
  * @notice selects data and fetchs array with results
  */
  public function simple_select_exec($tbl, $get_str='*', $where=0) {
          $cur = $this->simple_select($tbl,$get_str,$where);
          return mysql_fetch_array($cur);
  }

  /**
  * function simple_select_row
  * @see simple_select
  * @notice returns count of rows
  */
  public function simple_select_row($tbl, $get_str='*', $where=0) {
          $cur = $this->simple_select($tbl, $get_str, $where);
          return $this->getNumRows();
  }

  /**
  * function simple_get_tables
  * @param none
  * @return array with tables names
  */  
  public function simple_get_tables() {
          $this->setQuery("SHOW TABLES");

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          $tables = array();

          while($res = mysql_fetch_row($cur)) {
               $tables[] = $res[0];
          }

          return $tables;
  }

  /**
  * function simple_query
  * @param SQL-code
  * @return cursor
  */
  public function simple_query($sql) {
          $this->setQuery($sql);

          if(!($cur = $this->Query())) {
            $this->showError();
          }

          return $cur;
  }
  
  /**
  * function simple_count
  * @param table
  * @param options
  */
  public function simple_count($tbl,$options = '')
    {
    $query = "SELECT COUNT(*) FROM $tbl";
    if(!empty($options))
        {
        $query .= " WHERE $options";
        }
    $res = mysql_fetch_row($this->simple_query($query));
    if(!($res[0]))
        {
        return 0;
        }
        else
        {
        return $res[0];
        }
    }

       
  /****************************************
  * simple_functions section end
  * if any bugs found write me :
  * admloki@gmail.com
  ****************************************/
  
  /**
  * Additional section
  */
  
  /**
  * public function CreateChild
  * @param object new child object
  * @return object
  */
  public function CreateChild(&$var)
    {
    return $var = $this;
    }

}

?>

[MentaL]

[eTerNuLL.SiN]

Your site offers free PHP scripts? When will the forum be up?

[Daevius]

Nice work. But get rid of the link in your signature, warez links are removed very quickly (once super mods are known of it) and might result in a ban for you.

[kip0130]

Me thinks you should implement something so people cant flood your database...

[admLoki]

Your site offers free PHP scripts? When will the forum be up?

Yup, them too, but in fact its a site of our coders team. Forum will be up on this week .

Nice work. But get rid of the link in your signature, warez links are removed very quickly (once super mods are known of it) and might result in a ban for you.

Ok, but my site distributes scripts only for education, it wrote down in disclaimer .

Me thinks you should implement something so people cant flood your database...

Anti-hammer protect must be implemented in script functions, not in my DB class .

[Cask]

Shouldnt it be 91st? lol..

[admLoki]

Shouldnt it be 91st?

No xD

[FragFrog]

Nicely documented and clearly written, good job! :smile:

Could do with a bit more abstraction though, and your simple_insert and simple_update members are quite strict in their implementation. I wrote a class that could do bulk inserst / updates a while ago that uses a describe of the table that allowed you to simply dump your $_POST in it for example, whereupon it checks the fields in the database, matches them agains the variables in the array argument, parses them into the correct type and length, escapes them, and if all non-empty fields are matched inserts it. Saves you a spot of bother :icon6: