UCMDataLayerClass.php

<?php
/*
 UConn Mathematics DataLayer Class
 Kevin Marinelli - April 16/2013.
 Modified from the original my Marc Corluy, 2006.
 Updated to use mysqli instead of mysql function calls.
 Updates also include some structural rewrites to improve
 Stability and maintainability of the library.

   API List
   ===================================================================================================================
   Method                              : Purpose
   ===================================  =============================================================================
   UCMDataLayer()                      : Class constructor.

   PublicConnect()                     : Connect using public account.
   StandardConnect()                   : Connect using standard connection.
   GenericConnect($username,$password) : Connect using a specified username and password.
   Connect($host, $username, $password, $db)
                                       : Connect specifying host, username, password, and database.

   StandardListFields($table)          : List fields in a table in the defaultDB.
   ListFields($db , $table)            : List fields in a table from a specified db.
   GetError()                          : Get an error from the array of errors.
   SetError($str)                      : Add an error to the array of errors.
   _Query($query)                      : Submit an SQL query and return the result.
   SetQuery($query)                    : Submit a query and return the number of affected rows.
   GetQuery($query)                    : Submit a query and return the result (true, false, or an associative array).
   NumRows($result)                    : Return the number of rows in a result.
   Select($table, $condition, $sort)   : Make a selection on a table depening on the conditions and sort order.
   SelectDistinct($table, $fields, $condition, $sort)  
                                       :Select distinct values from the list of fields in a table.
   Insert($table, $add_array)         : Insert an array into a table.
   Update($table, $update_array, $condition) 
                                       : Update a table given an arrays of updates and conditions.
   Delete($table, $condition)          : Delete records from a table given an array of conditions.
   SetDebug()                          : Set debug status.
   UnsetDebug()                        : Unset Sebug status.
   DebugMsg($msg)                      : Display msg if debug is set.
   _MakeWhereList($condition)          : Given an array of conditions, construct a MySQL where clause. 
   _QuoteVal($val)                     : Quote a value.
   _QuoteVals($array)                  : Quote an array of values;
   Close()                             : Close the MySQL database connection.

*/

//Database configuration secrets are stored in a configuratin file, so the source becomes safely printable.

/*$IncludeDir="/var/www/html/";
if( isset($_SERVER['DOCUMENT_ROOT'])&&strcmp($_SERVER['DOCUMENT_ROOT'],"")!=0)
{
   $IncludeDir=$_SERVER['DOCUMENT_ROOT']."/";
}
*/

include_once("UCMDataLayerClassConfig.php");

class UCMDataLayer 
{
// Server dependent variables

   var $defaultDB;
   var $defaultUserName;
   var $defaultPassword;
   var $defaultHost;

   var $Host;
   var $UserName;
   var $Password;
   var $DB;

// global class variables
   var $mysqli;   // Mysql database object.
   var $errors = array();
   var $debug = false;

   function UCMDataLayer() 
   {
      global $Settings;
      global $UCMdefaultDB;
      global $UCMDefaultUserName;
      global $UCMDefaultPassword;
      global $UCMDefaultHost;

      $this->defaultDB=$UCMdefaultDB; 
      $this->defaultUserName=$UCMDefaultUserName;
      $this->defaultPassword=$UCMDefaultPassword;
      $this->defaultHost=$UCMDefaultHost;

      $this->Debug=false;

     // $this->defaultDB="hostdb";

      if( isset($Settings['DatabaseServer'])) 
      {
         $this->defaultHost=$Settings['DatabaseServer'];
      }
   }

   function PublicConnect() 
   {
      /* most connections using the public account should be as follows */

      return $this->Connect( $this->defaultHost, $this->defaultUserName, $this->defaultPassword,
                             $this->defaultDB);
   }

   function StandardConnect() 
   {
      global $username;
      global $password;                 

      return $this->Connect($this->defaultHost, $username, $password, $this->defaultDB);
   }

   function GenericConnect($username,$password) 
   {
      return $this->Connect($this->defaultHost, $username, $password, $this->defaultDB);
   }

   function Connect( $host, $username, $password, $db ) 
   {
      $debug=false; 
      if ( ! $db )   // If a database has not been specified, use the default database.
      {
         $db = $this->defaultDB;                          
      }
                
      if( $this->mysqli) $this->Close(); // If a connection is open, close it.

      $this->Host=$host;
      $this->UserName=$username;
      $this->Password=$password;
      $this->DB=$db;
      
      $this->mysqli=@new mysqli( $this->Host, $this->UserName, $this->Password, $this->DB);

      if ($errno=mysqli_connect_errno())
      {
         printf("Connect failed: error number=%d : %s\n", $errno,mysqli_connect_error());

         return false;
      }

      if ( ! $this->mysqli->select_db( $db ) ) 
      {
         $this->SetError("Couldn't select database: $db");
         $this->Close();
         return false;
      }
                
      return true;	
   }

// Return an array containing the fields of a table from the currently selected db.
   function StandardListFields ( $table ) 
   {
      $result = $this->ListFields ( $this->DB , $table );
      return $result;
   }

/*  ListFields($db, $table)

    Return an array containing the fields in $table of database $db.
    Each element of the array contains the following pointers:
       fieldName - The name of the field in the table.
       fieldType - The type of the field in the table. 
*/
   function ListFields ( $db , $table ) 
   {
      $query="describe $db.$table;";
      
      $fields = $this->mysqli->query($query);
      $names=$fields->fetch_fields();
      $fieldNameRow=$names[0]->name;
      $fieldTypeRow=$names[1]->name;

      $i=0;
      while( $row[$i]=$fields->fetch_assoc())
      {
         $fieldlist[$i]->fieldName=$row[$i][$fieldNameRow];
         $fieldlist[$i]->fieldType=$row[$i][$fieldTypeRow];
         $i++;
      }

      return $fieldlist;
   }

// Pop the current error from the top of the stack and return it.
   function GetError( ) 
   {
      if( count($this->errors) <1) return false;

      return array_pop($this->errors);
   }

// Push an error string into the stack of errors.
   function SetError( $str ) 
   {
      array_push( $this->errors, $str );
   }

// Submit a query and return a mysqli result.
   function _Query( $query ) 
   {
      if ( mysqli_connect_errno())
      {
         echo "<br>\nError: No active database link.<br>\n";
         $this->SetError("No active db connection");
         return false;
      }

      $result = $this->mysqli->query($query);
      if ( ! $result )
         $this->SetError("error: ".$this->mysqli->error);
      return $result;
   }

// Submit a query and return the number of affected rows.
   function SetQuery( $query ) 
   {
      if (! $result = $this->_Query( $query ) ) 
         return false;	
      return $this->mysqli->affected_rows;
   }

    
// Submit a query and return the result (true, false, or an associative array).
   function GetQuery( $query ) 
   {
      if (! $result = $this->_Query( $query ) ) 
      return false;	

      if( $result === true) return true;

      // If the result is not false, or true, move the data to an array
      // to be used as the return value.
      $rows = array();
      while ( $row = $result->fetch_assoc( ) )
         {
         // added to fix UTF8 encoding issue on WP, IB 12/09/13 
         foreach ($row as $key => $val) { if (gettype($val)==="string") $row[$key]=utf8_encode($val);} 
         $rows[] = $row;
         }

      return $rows;  // return the rows of data.

   }
   
 /*
    function GetQueryUTF8 ($query)
   {      
   if (! $result = $this->_Query( $query ) ) 
      return false;	

      if( $result === true) return true;

      // If the result is not false, or true, move the data to an array
      // to be used as the return value.
      $rows = array();
      while ( $row = $result->fetch_assoc( ) )
         {
         foreach ($row as $key => $val) { if (gettype($val)==="string") $row[$key]=utf8_encode($val);} 
         $rows[] = $row;
         }

      return $rows;  // return the rows of data.

   }
 */

   
   function NumRows($result)
   {
       return $result->num_rows;
   }

   function Select( $table, $condition="", $sort="" ) 
   {
      $query = "SELECT * FROM $table";
      $query .= $this->_MakeWhereList( $condition );	
      if ( $sort != "" )
      $query .= " order by $sort";
      $this->DebugMsg( $query );
      return $this->GetQuery( $query);
   }

   function SelectDistinct( $table, $fields="", $condition="", $sort="" ) 
   {
      if ($fields == "") { $fields = "*"; }
      $query = "SELECT DISTINCT ".$fields." FROM ".$table;
      $query .= $this->_MakeWhereList( $condition );	
      if ( $sort != "" )
         $query .= " order by $sort";
      $this->DebugMsg( $query );
      return $this->GetQuery( $query);
   }

   function Insert( $table, $add_array ) 
   {
      $add_array = $this->_QuoteVals( $add_array );
      $keys = "(".implode( array_keys( $add_array ), ", ").")";
      $values = "values (".implode( array_values( $add_array ), ", ").")";
      $query = "INSERT INTO $table $keys $values";
      $this->DebugMsg( $query );
      return $this->SetQuery( $query );
   }

   function Update( $table, $update_array, $condition="" ) 
   {
      $update_pairs=array();
      foreach( $update_array  as $field=>$val )
         array_push( $update_pairs, "$field=".$this->_QuoteVal( $val ) );

      $query = "UPDATE $table set ";
      $query .= implode( ", ", $update_pairs );
      $query .= $this->_MakeWhereList( $condition );	
      echo "<!-- QQQQ $query QQQQ --> \n\n";
      $this->DebugMsg( $query );
      return $this->SetQuery( $query );
   }

   function Delete( $table, $condition="" ) 
   {
      $query = "DELETE FROM  $table";
      $query .= $this->_MakeWhereList( $condition );	
      $this->DebugMsg( $query );
      return $this->SetQuery( $query);
   }
	
   function SetDebug()
   {
      $this->Debug=true;
   }

   function UnsetDebug()
   {
      $this->Debug=false;
   }

   function DebugMsg( $msg ) 
   {
      if ( $this->Debug )
         print "$msg<br>";
   }

   function _MakeWhereList( $condition ) 
   {
      if ( empty( $condition ) ) return "";

      $retstr = " WHERE ";
      if ( is_array( $condition ) ) 
      {
         $cond_pairs=array();
         foreach( $condition  as $field=>$val )
            array_push( $cond_pairs, "$field=".$this->_QuoteVal( $val ) );
         $retstr .= implode( " and ", $cond_pairs );
      } 
      elseif ( is_string( $condition ) && ! empty( $condition ) )
         $retstr .= $condition;
      return $retstr;
   }

   function _QuoteVal( $val ) 
   {
      // if ( is_numeric( $val ) ) return $val;
      return "'".addslashes($val)."'";
   }

   function _QuoteVals( $array ) 
   {
      foreach( $array as $key=>$val )
         $ret[$key]=$this->_quote_val( $val );
      return $ret;
   }

   function Close() 
   {
      if($this->mysqli)$this->mysqli->close();
   }
}	

?>