Page 1 of 1

Overhauling my db class (recommendations)

Posted: Mon Jun 19, 2006 5:19 pm
by Luke
I picked up this database class somewhere. It has been working pretty well for me so far, but I am really wondering what other people's database classes look like and how they implement them. Please critique this one and maybe give some advice or even post a more sophisticated class or series of classes.

Code: Select all

// constants used by class
define('MYSQL_TYPES_NUMERIC', 'int real ');
define('MYSQL_TYPES_DATE', 'datetime timestamp year date time ');
define('MYSQL_TYPES_STRING', 'string blob ');

class dataBase {
 
   var $last_error;         // holds the last error. Usually mysql_error()
   var $last_query;         // holds the last query executed.
   
   var $host;               // mySQL host to connect to
   var $user;               // mySQL user name
   var $pw;                 // mySQL password
   var $db;                 // mySQL database to select

   var $db_link;            // current/last database link identifier
   var $auto_slashes;       // the class will add/strip slashes when it can
   
   function dataBase() {
   
      // class constructor.  Initializations here.
      
      // Setup your own default values for connecting to the database here. You
      // can also set these values in the connect() function and using
      // the select_database() function.
      
      $this->host = '';
      $this->user = '';
      $this->pw = '';
      $this->db = '';
 
      $this->auto_slashes = true;
   }

   function connect($host='', $user='', $pw='', $db='', $persistant=true) {
 
      // Opens a connection to MySQL and selects the database.  If any of the
      // function's parameter's are set, we want to update the class variables.  
      // If they are NOT set, then we're giong to use the currently existing
      // class variables.
      // Returns true if successful, false if there is failure.  
      
      if (!empty($host)) $this->host = $host; 
      if (!empty($user)) $this->user = $user; 
      if (!empty($pw)) $this->pw = $pw; 

 
      // Establish the connection.
      if ($persistant) 
         $this->db_link = mysql_pconnect($this->host, $this->user, $this->pw);
      else 
         $this->db_link = mysql_connect($this->host, $this->user, $this->pw);
 
      // Check for an error establishing a connection
      if (!$this->db_link) {
         $this->last_error = mysql_error();
         return false;
      } 
  
      // Select the database
      if (!$this->select_db($db)) return false;
 
      return true;  // success
   }

   function select_db($db='') {
 
      // Selects the database for use.  If the function's $db parameter is 
      // passed to the function then the class variable will be updated.
 
      if (!empty($db)) $this->db = $db; 
      
      if (!mysql_select_db($this->db)) {
         $this->last_error = mysql_error();
         return false;
      }
 
      return true;
   }
   
   function select($sql) {
      
      // Performs an SQL query and returns the result pointer or false
      // if there is an error.
 
      $this->last_query = $sql;
      
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      }
      return $r;
   }

   function select_one($sql) {
 
      // Performs an SQL query with the assumption that only ONE column and
      // one result are to be returned.
      // Returns the one result.
 
      $this->last_query = $sql;
      
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      }
      if (mysql_num_rows($r) > 1) {
         $this->last_error = "Your query in function select_one() returned more than one result.";
         return false;     
      }
      if (mysql_num_rows($r) < 1) {
         $this->last_error = "Your query in function select_one() returned no results.";
         return false;     
      }
      $ret = @mysql_result($r, 0);
      mysql_free_result($r);
      if ($this->auto_slashes) return stripslashes($ret);
      else return $ret;
   }
   
   function get_row($result, $type='MYSQL_BOTH') {
 
      // Returns a row of data from the query result.  You would use this
      // function in place of something like while($row=mysql_fetch_array($r)). 
      // Instead you would have while($row = $db->get_row($r)) The
      // main reason you would want to use this instead is to utilize the
      // auto_slashes feature.
      if ($type == 'MYSQL_ASSOC') $row = mysql_fetch_array($result, MYSQL_ASSOC);
      if ($type == 'MYSQL_NUM') $row = mysql_fetch_array($result, MYSQL_NUM);
      if ($type == 'MYSQL_BOTH') $row = mysql_fetch_array($result, MYSQL_BOTH); 
      
      if (!$row) return false;
      if ($this->auto_slashes) {
         // strip all slashes out of row...
         foreach ($row as $key => $value) {
            $row[$key] = stripslashes($value);
         }
      }
      return $row;
   }
   
   function dump_query($sql) {
   
      // Useful during development for debugging  purposes.  Simple dumps a 
      // query to the screen in a table.
 
      $r = $this->select($sql);
      if (!$r) return false;
      echo "<div style=\"border: 1px solid blue; font-family: sans-serif; margin: 8px;\">\n";
      echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" width=\"100%\">\n";
      
      $i = 0;
      while ($row = mysql_fetch_assoc($r)) {
         if ($i == 0) {
            echo "<tr><td colspan=\"".sizeof($row)."\"><span style=\"font-face: monospace; font-size: 9pt;\">$sql</span></td></tr>\n";
            echo "<tr>\n";
            foreach ($row as $col => $value) {
               echo "<td bgcolor=\"#E6E5FF\"><span style=\"font-face: sans-serif; font-size: 9pt; font-weight: bold;\">$col</span></td>\n";
            }
            echo "</tr>\n";
         }
         $i++;
         if ($i % 2 == 0) $bg = '#E3E3E3';
         else $bg = '#F3F3F3';
         echo "<tr>\n";
         foreach ($row as $value) {
            echo "<td bgcolor=\"$bg\"><span style=\"font-face: sans-serif; font-size: 9pt;\">$value</span></td>\n";
         }
         echo "</tr>\n";
      }
      echo "</table></div>\n";
   }
   
   function insert_sql($sql) {
       
      // Inserts data in the database via SQL query.
      // Returns the id of the insert or true if there is not auto_increment
      // column in the table.  Returns false if there is an error.      
 
      $this->last_query = $sql;
      
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      }
      
      $id = mysql_insert_id();
      if ($id == 0) return true;
      else return $id; 
   }

   function update_sql($sql) {
 
      // Updates data in the database via SQL query.
      // Returns the number or row affected or true if no rows needed the update.
      // Returns false if there is an error.

      $this->last_query = $sql;
      
      $r = mysql_query($sql);
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      }
      
      $rows = mysql_affected_rows();
      if ($rows == 0) return true;  // no rows were updated
      else return $rows;
   }
   
   function insert_array($table, $data) {
      
      // Inserts a row into the database from key->value pairs in an array. The
      // array passed in $data must have keys for the table's columns. You can
      // not use any MySQL functions with string and date types with this 
      // function.  You must use insert_sql for that purpose.
      // Returns the id of the insert or true if there is not auto_increment
      // column in the table.  Returns false if there is an error.
      
      if (empty($data)) {
         $this->last_error = "You must pass an array to the insert_array() function.";
         return false;
      }
      
      $cols = '(';
      $values = '(';
      
      foreach ($data as $key=>$value) {     // iterate values to input
          
         $cols .= "$key,";  
         
         $col_type = $this->get_column_type($table, $key);  // get column type
         if (!$col_type) return false;  // error!
         
         // determine if we need to encase the value in single quotes
         if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $values .= "$value,";
         elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
            $value = $this->sql_date_format($value, $col_type); // format date
            $values .= "'$value',";
         }
         elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
            if ($this->auto_slashes) $value = mysql_real_escape_string($value);
            $values .= "'$value',";  
         }
      }
      $cols = rtrim($cols, ',').')';
      $values = rtrim($values, ',').')';     
      
      // insert values
      $sql = "INSERT INTO $table $cols VALUES $values";
      return $this->insert_sql($sql);
   }
   
   function update_array($table, $data, $condition) {
      
      // Updates a row into the database from key->value pairs in an array. The
      // array passed in $data must have keys for the table's columns. You can
      // not use any MySQL functions with string and date types with this 
      // function.  You must use insert_sql for that purpose.
      // $condition is basically a WHERE claus (without the WHERE). For example,
      // "column=value AND column2='another value'" would be a condition.
      // Returns the number or row affected or true if no rows needed the update.
      // Returns false if there is an error.
      
      if (empty($data)) {
         $this->last_error = "You must pass an array to the update_array() function.";
         return false;
      }
      
      $sql = "UPDATE $table SET";
      foreach ($data as $key=>$value) {     // iterate values to input
          
         $sql .= " $key=";  
         
         $col_type = $this->get_column_type($table, $key);  // get column type
         if (!$col_type) return false;  // error!
         
         // determine if we need to encase the value in single quotes
         if (substr_count(MYSQL_TYPES_NUMERIC, "$col_type ")) $sql .= "$value,";
         elseif (substr_count(MYSQL_TYPES_DATE, "$col_type ")) {
            $value = $this->sql_date_format($value, $col_type); // format date
            $sql .= "'$value',";
         }
         elseif (substr_count(MYSQL_TYPES_STRING, "$col_type ")) {
            if ($this->auto_slashes) $value = mysql_real_escape_string($value);
            $values .= "'$value',";  
         }

      }
      $sql = rtrim($sql, ','); // strip off last "extra" comma
      if (!empty($condition)) $sql .= " WHERE $condition";
      
      // insert values
      return $this->update_sql($sql);
   }
   
   function execute_file ($file) {
 
      // executes the SQL commands from an external file.
      
      if (!file_exists($file)) {
         $this->last_error = "The file $file does not exist.";
         return false;
      }
      $str = file_get_contents($file);
      if (!$str) {
         $this->last_error = "Unable to read the contents of $file.";
         return false; 
      }
      
      $this->last_query = $str; 
      
      // split all the query's into an array
      $sql = explode(';', $str);
      foreach ($sql as $query) {
         if (!empty($query)) {
            $r = mysql_query($query);
 
            if (!$r) {
               $this->last_error = mysql_error();
               return false;
            }
         }
      }
      return true;
    
   }
   
   function get_column_type($table, $column) {
      
      // Gets information about a particular column using the mysql_fetch_field
      // function.  Returns an array with the field info or false if there is
      // an error.
 
      $r = mysql_query("SELECT $column FROM $table LIMIT 1");
      if (!$r) {
         $this->last_error = mysql_error();
         return false;
      }
      $ret = mysql_field_type($r, 0);
      if (!$ret) {
         $this->last_error = "Unable to get column information on $table.$column.";
         mysql_free_result($r);
         return false;
      }
      mysql_free_result($r);
      return $ret;
      
   }
   
   function get_row_amount($table){
	   $sql = "SELECT COUNT(*) AS amount FROM " . $table;
       $this->last_query = $sql;
      
       $r = mysql_query($sql);
       if (!$r) {
           $this->last_error = mysql_error();
           return false;
       }
       else{
	       if($row = mysql_fetch_assoc($r)){
		       return $row['amount'];
	       }
       }
   }
   
   function sql_date_format($value) {

      // Returns the date in a format for input into the database.  You can pass
      // this function a timestamp value such as time() or a string value
      // such as '04/14/2003 5:13 AM'. 
      
      if (gettype($value) == 'string') $value = strtotime($value);
      return date('Y-m-d H:i:s', $value);

   }
   function print_last_error($show_query=true) {
      
      // Prints the last error to the screen in a nicely formatted error message.
      // If $show_query is true, then the last query that was executed will
      // be displayed aswell.
 
      ?>
      <div style="border: 1px solid red; font-size: 9pt; font-family: monospace; color: red; padding: .5em; margin: 8px; background-color: #FFE2E2">
         <span style="font-weight: bold">db.class.php Error:</span><br><?php echo $this->last_error ?>
      </div>
      <?php
      if ($show_query && (!empty($this->last_query))) {
      $this->print_last_query();
      }
 
   }

   function print_last_query() {
    
      // Prints the last query that was executed to the screen in a nicely formatted
      // box.
     
      ?>
      <div style="border: 1px solid blue; font-size: 9pt; font-family: monospace; color: blue; padding: .5em; margin: 8px; background-color: #E6E5FF">
         <span style="font-weight: bold">Last SQL Query:</span><br><?php echo str_replace("\n", '<br>', $this->last_query) ?>
      </div>
      <?php 
   }
}

Posted: Mon Jun 19, 2006 5:21 pm
by Christopher
I would recommend moving to a Connection/RecordSet style lower layer and building Gateway/Mapper style upper layer over that.

Posted: Mon Jun 19, 2006 5:24 pm
by Luke
arborint wrote:I would recommend moving to a Connection/RecordSet style lower layer and building Gateway/Mapper style upper layer over that.
Can you elaborate? Gateway/Mapper?

Posted: Mon Jun 19, 2006 5:35 pm
by Christopher
Table Data Gateway, Active Record or O/R Mapper are the common patterns. There are lots of implementation of these around.

Posted: Mon Jun 19, 2006 5:36 pm
by Benjamin
well this isn't oop but it works for me and you could probably turn it into a class...

Code: Select all

function CallFatalError($ErrorNumber) {
  ?>
    <html><head><title>Website Update In Progress</title></head>
      <body>
        <h1>System Update In Progress</h1>
        <p>The page you are trying to view is currently being updated.  Please try again later.</p>
        <p>Message #: <?php echo $ErrorNumber; ?></p>
      </body>
    </html>
  <?php
  die();
}

/***************************************************************************
                         CREATE DATABASE CONNECTION
***************************************************************************/
function ConnectToDB() {
  if (!$link_id = @mysql_pconnect(MYSQL_DATABASE_HOST, MYSQL_USERNAME, MYSQL_PASSWORD)) {
    CallFatalError('3 Database Connection Update' . mysql_error());
  }
  if (!@mysql_select_db(MYSQL_DATABASE_NAME, $link_id)) {
    CallFatalError('4 Database Selection Update');
  }
  return $link_id;
}
$Connect = ConnectToDB();

/***************************************************************************
                         GET DATA FROM THE DATABASE
***************************************************************************/

function GetMySQLData($Fields, $Table, $Parameters, $Limit, $OrderBy = null) {
  Global $Connect, $PageData;
  $Start = MicroTimeFloat();
  $Query = "select ";
  $Fields = explode(" ", $Fields);
  $FieldCount = count($Fields) - 1;
  for ($CreateField = 0; $CreateField <= $FieldCount; $CreateField++) {
    $Query .= "`" . mysql_real_escape_string($Fields[$CreateField]) . "`, ";
  }
  $Query = substr($Query, 0, -2) . " from `" . mysql_real_escape_string($Table) . "` ";
  if ($Parameters != "") {
    $Query .= "where";
    while (list($Field, $Value) = each($Parameters)) {
      $Value = explode(" ", $Value);
      $Query .= " `" . mysql_real_escape_string($Field) . "` " . mysql_real_escape_string($Value[0]) . " '" . mysql_real_escape_string($Value[1]) . "' and ";
    }
  $Query = substr($Query, 0, -4);
  }
  if ($OrderBy != null) {
    $Query .= 'order by ' . $OrderBy . ' ';
  }
  if ($Limit != "") {
    $Query .= "limit " . mysql_real_escape_string($Limit) . ' ';
  }
  if (DEBUG_MYSQL_QUERY) {
    echo '<div class="debug"><b>BEGIN QUERY:</b><br />' . $Query . "</div>";
  }
  if (!$Resource = @mysql_query($Query,$Connect)) {
    if (DEBUG_MODE == true) {
      $Message = "5<br /><br /><b style='color: #ff0000'>Database Query Error!</b><br /><br />Query: " . $Query . "<br /><br />Returned Error: " . mysql_errno() . " " . mysql_error();
      CallFatalError($Message);
    } else {
      CallFatalError('5 General Database Update');
    }
  } else {
    $Record = 0;
    while ($Data = mysql_fetch_assoc($Resource)) {
      $Records[$Record] = $Data;
      $Record++;
    }
    $Records['NumRows'] = mysql_num_rows($Resource);
  }
  $End = MicroTimeFloat();
  $PageData['QueryTime'] = $PageData['QueryTime'] + ($End - $Start);
  $PageData['TotalQueries']++;
  @mysql_free_result($Resource);
  return $Records;
}

/***************************************************************************
                        DELETE DATA FROM THE DATABASE
***************************************************************************/

function DeleteMySQLData($Table, $Parameters, $Limit = null) {
  Global $Connect, $PageData;
  $Start = MicroTimeFloat();
  $Query = "delete from " . mysql_real_escape_string($Table);
  $Query .= " where"; // placed here just to make sure it fails with invalid parameters
  if ($Parameters != "") {
    while (list($Field, $Value) = each($Parameters)) {
      $Value = explode(" ", $Value);
      $Query .= " `" . mysql_real_escape_string($Field) . "` " . mysql_real_escape_string($Value[0]) . " '" . mysql_real_escape_string($Value[1]) . "' and ";
    }
    $Query = substr($Query, 0, -4);
    if ($Limit != null) {
      $Query .= "limit " . $Limit;
    }
  } else {
    if (DEBUG_MODE == true) {
      $Message = "12<br /><br /><b style='color: #ff0000'>Critical Database Error!</b><br /><br />Query: " . $Query . "<br /><br />Returned Error: " . mysql_errno() . " " . mysql_error();
      CallFatalError($Message);
    } else {
      CallFatalError('12 Database Update');
    }
  }
  if (!$Resource = @mysql_query($Query,$Connect)) {
    if (DEBUG_MODE == true) {
      $Message = "11<br /><br /><b style='color: #ff0000'>Database Query Error!</b><br /><br />Query: " . $Query . "<br /><br />Returned Error: " . mysql_errno() . " " . mysql_error();
      CallFatalError($Message);
    } else {
      CallFatalError('11 General Database Update');
    }
  }
  if (DEBUG_MYSQL_QUERY) {
    echo '<div class="debug"><b>BEGIN QUERY:</b><br />' . $Query . "</div>";
  }
  $End = MicroTimeFloat();
  $PageData['QueryTime'] = $PageData['QueryTime'] + ($End - $Start);
  $PageData['TotalQueries']++;
  return true;
}

/***************************************************************************
                 UPDATE OR INSERT RECORDS INTO THE DATABASE
***************************************************************************/

function MySQLInsertOrUpdate($Action, $TableName, $Data, $Parameters, $Limit = null) {
  $Start = MicroTimeFloat();
  Global $Connect, $PageData;
  reset($Data);
  $Records = array();
  if ($Action == "insert") {
    $MySQLQuery = "insert into `" . mysql_real_escape_string($TableName) . "` (";
    while (list($Fields, ) = each($Data)) {
      $MySQLQuery .= "`" . mysql_real_escape_string($Fields) . "`, ";
    }
    $MySQLQuery = substr($MySQLQuery, 0, -2) . ") values (";
    reset($Data);
    while (list(, $Values) = each($Data)) {
      $MySQLQuery .= "'" . mysql_real_escape_string($Values) . "', ";
    }
    $MySQLQuery = substr($MySQLQuery, 0, -2) . ")";
  } elseif ($Action == 'update') {
    if (count($Parameters) < 1) {
      if (DEBUG_MODE == true) {
        $Message = "9<br /><br /><b style='color: #ff0000'>Database Query Error! - Cannot update without where parameters!</b><br /><br />Query: " . $Query . "<br /><br />Returned Error: " . mysql_errno() . " " . mysql_error();
        CallFatalError($Message);
      } else {
        CallFatalError('9 Database Update');
      }
    }
    $MySQLQuery = "update `" . mysql_real_escape_string($TableName) . "` set ";
    while (list($Fields, $Values) = each($Data)) {
      $MySQLQuery .= "`" . mysql_real_escape_string($Fields) . "`" . "='" . mysql_real_escape_string($Values) . "', ";
    }
    $MySQLQuery = substr($MySQLQuery, 0, -2) . " where";
    while (list($Field, $Value) = each($Parameters)) {
      $Value = explode(" ", $Value);
      $MySQLQuery .= " `" . mysql_real_escape_string($Field) . "` " . mysql_real_escape_string($Value[0]) . " '" . mysql_real_escape_string($Value[1]) . "' and ";
    }
    $MySQLQuery = substr($MySQLQuery, 0, -4);
  }
  if ($Limit != null) {
    $MySQLQuery = $MySQLQuery . 'limit ' . $Limit;
  }
  if (DEBUG_MYSQL_QUERY) {
    echo '<div class="debug"><b>BEGIN QUERY:</b><br />' . $MySQLQuery . "<br /><br />Insert ID: " . mysql_insert_id() . "</div>";
  }
  if (!$Resource = @mysql_query($MySQLQuery,$Connect)) {
    if (DEBUG_MODE == true) {
      $Message = "10<br /><br /><b style='color: #ff0000'>Database Query Error!</b><br /><br />Query: " . $MySQLQuery . "<br /><br />Returned Error: " . mysql_errno() . " " . mysql_error();
      CallFatalError($Message);
    } else {
      CallFatalError('10 General Database Update');
    }
  } else {
    $Records['AffectedRows'] = @mysql_affected_rows();
    $Records['InsertID'] = @mysql_insert_id();
    $PageData['TotalQueries']++;
    $End = MicroTimeFloat();
    $PageData['QueryTime'] = $PageData['QueryTime'] + ($End - $Start);
    return $Records;
  }
}
Joins are not supported

You can call these functions like so....

Code: Select all

// to retrieve data
$Parameters = array('FieldOne' => '= ' . $ThisValue,
                                  'FieldTwo' => '!= ' . $ThatValue);
$Records = GetMySQLData('FieldOne FieldTwo FieldThree', 'TableName', $Parameters, '0,1', 'FieldOne ASC'); // 0,1 is limit, last param is optional

// $Records is a multi dimensional array of records.

// to delete data
$Parameters = array("FieldOne" => "= " . $ThisValue);
DeleteMySQLData("TableName", $Parameters);

// to insert data
$Data = array("FieldOne"  => $ThisValue,
                        "FieldTwo" => $ThatValue);
MySQLInsertOrUpdate("insert", "TableName", $Data, null);

// to update data
$Data = array("FieldOne"  => $ThisValue,
                        "FieldTwo" => $ThatValue);
$Parameters = array("FieldOne" => "= " . $ThisValue);
MySQLInsertOrUpdate("update", "TableName", $Data, $Parameters);
These functions work very well and as far as I know are very secure. If you improve them please post the changes.

I hope these help you.