Subversion Repositories phpLibraryV2

<?php

   //require_once "DBDatabase.class.php";

   global $DEBUG;

   define( 'HTML_QUOTE_CHAR', '"' );

   define( 'CONSTANT_NO_VALUE_DROPDOWN', '--------' );

   class DBQuery {

      // following are used for the class with no instantiation

      protected static $connected = false;

      protected static $connectionInfo = null; // connection information for database

      protected $parameters = array(

                               // query to be run

                               'query' => '',

                               // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select

                               'rowsAffected' => '',

                               // the value of the last row inserted (mysql only)

                               'lastInsertKey' => '',

                               // an array that contains the data retrieved by a query (select only)

                               'returnData' => '',

                               // an array that contains the meta data from the query

                               'columnMetaData' => '',

                               // if true, $returnData is an array of associative array, with the column names being the key fields

                               'useAssociativeArray' => '',

                               // if true, will return errors to STDOUT

                               'showErrors' => '',

                               // set to last error returned

                               'error' => '',

                               // Will be instantiation of a  Logging instance if set

                               'log' => '',

                               // if set to a table name, all queries are written to it

                               'auditTable' => '',

                               'orderBy' => '',

                             );

      public static function connect( $connectionInfo = null ) {

         if ( isset ( $connectionInfo ) ) {

            DBQuery::$connectionInfo = $connectionInfo;

            if ( ! isset( DBQuery::$connectionInfo['host'] ) || DBQuery::$connectionInfo['host'] == '' )

               DBQuery::$connectionInfo['host'] = '127.0.0.1';

         } // if they passed in a connection string

         if ( ! DBQuery::$connected ) {

            try {

               if ( mysql_connect( DBQuery::$connectionInfo['host'], DBQuery::$connectionInfo['username'], DBQuery::$connectionInfo['password'] ) === false )

                  throw new Exception( mysql_error() );

               if ( mysql_select_db( DBQuery::$connectionInfo['name'] ) === false )

                  throw new Exception(mysql_error());

               DBQuery::$connected = true;

            } catch ( Exception $e ) {

               die( $e->getMessage() );

            }

         } // if we are not connected

      } // static function connect

      protected function logIt ( $level, $message, $file = null, $class = null, $function = null, $line = null ) {

         global $DEBUG;

         if ( isset( $DEBUG ) ) {

            $DEBUG->writeLog( $level, $message, $file, $class, $function, $line );

         }

      } // logIt

      public function __construct( $query, $whereClause = null, $orderBy = null, $runImmediate = false ) {

         $this->parameters['query'] = $query;

         if ( isset( $whereClause ) ) {

            $this->parameters[ 'query' ] .= $this->makeWhereClause( $whereClause );

         } // whereclause

         if ( isset( $orderBy ) ) {

            $this->parameters[ 'orderBy' ] .= ' order by ' . implode( ',', $orderBy );

         } // orderby

         $this->parameters[ 'rowsAffected' ] = 0;

         $this->parameters[ 'lastInsertKey' ] = '';

         $this->parameters[ 'returnData' ] = array();

         $this->parameters[ 'columnMetaData' ] = array();

         $this->parameters[ 'useAssociativeArray' ] = true;

         $this->parameters[ 'showErrors' ] = true;

         $this->parameters[ 'error' ] = '';

         if ( $runImmediate )

            $this->run();

         // if (isset( $AUDITTABLE ) ) $this->parameters[ 'auditTable' ) =  new Logging( $LOGFILE );

      } // __construct

      public function save() {

         return $this->parameters;

      } // function save

      public function load( $parameters = array() ) {

         $this->parameters = $parameters;

      } // function load

      public function __set( $name, $value ) {

         $this->parameters[$name] = $value;

         return $value;

      }

      public function __get( $name ) {

         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;

      }

      public function __isset( $name ) {

         return isset( $this->parameters[$name] );

      }

      /* 

         function actually executes the query, populating the members with the results

         returns true if no errors, false if errors (see $this->parameters[ 'error' ) for code)

      */

      function run () {

         if ( ! DBQuery::$connected ) {

            if ( isset( DBQuery::$connectionInfo ) ) {

               DBQuery::connect();

            } else {

               throw new Exception( "No Connection Available" );

            }

         }

         // print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";

         $this->logIt( 2, $this->parameters['query'] );

//         if ( isset( $this->parameters[ 'auditTable' )) ) audit( $query );

         $result = @mysql_query($this->parameters[ 'query' ]);

         if( mysql_errno() ) {

            $this->parameters[ 'error' ] = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$this->parameters[query]</VAR>";

            if ( $this->parameters[ 'showErrors' ] ) echo($this->parameters[ 'error' ]);

            return false;

         } // if

         if( preg_match ( '/^\s*select/i', $this->parameters[ 'query' ] ) ) { // this is a select statement

            $this->parameters[ 'rowsAffected' ] = @mysql_num_rows($result);

            $this->parameters[ 'columnMetaData' ] = array();

            for ($i = 0; $i < mysql_num_fields( $result ); $i++) {

               $meta = mysql_fetch_field ( $result );

               $this->parameters[ 'columnMetaData' ][] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );

            }

            if( ! $this->parameters[ 'rowsAffected' ] ) return;

            $this->parameters[ 'returnData' ] = array();

            for( $i = 0; $i < $this->parameters[ 'rowsAffected' ]; $i++ ) {

               $this->parameters[ 'returnData' ][] = $this->parameters[ 'useAssociativeArray' ] ? mysql_fetch_assoc($result) : mysql_fetch_row($result);

            }

            mysql_free_result($result);

         } else { // not a select statement

            $this->parameters[ 'rowsAffected' ] = mysql_affected_rows();

            $this->parameters[ 'lastInsertKey' ] = mysql_insert_id();

         }

         return true;

      } // function run

      /*

       * function will return one and only one row, NOT as an array of array

       * but as a single row array

       * if more than one row is returned by query, error is set and function

       * returns false.

       * Otherwise, function returns true

       */

      public function getOneRow( $sql = null ) {

         if ( isset( $sql ) )

            $this->parameters[ 'query' ] = $sql;

         $save = $this->parameters[ 'useAssociativeArray' ];

         $useAssociativeArray = true;

         $this->run();

         $useAssociativeArray = $save;

         if ( $this->parameters[ 'rowsAffected' ] == 1 ) {

            $this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];

            return true;

         } else

            $this->parameters['error'] = "$this->parameters[query] did not return a unique row in getOneRow";

         return false;

      } // getOneRow

      // function returns the first column of the first row of data returned from query

      // or null no value returned

      public function getOneDBValue( $sql = null ) {

         if ( isset( $sql ) )

            $this->parameters[ 'query' ] = $sql;

         $save = $this->parameters[ 'useAssociativeArray' ];

         $useAssociativeArray = false;

         $this->run();

         $useAssociativeArray = $save;

         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;

      }

      public function countNumberOfRows ( $sql = null ) {

         if ( isset( $sql ) )

            $this->parameters[ 'query' ] = $sql;

         $save = $this->parameters[ 'useAssociativeArray' ];

         $this->parameters['useAssociativeArray'] = false;

         $this->run();

         $this->parameters['useAssociativeArray'] = $save;

         return $this->parameters[ 'rowsAffected' ];

      }

      public function makeWhereClause ($conditions, $joinedBy = 'and') {

         $joinedBy = " $joinedBy "; // make sure there are spaces around it

         $whereClause = ' where ' . implode ( $joinedBy, $conditions );

         return $whereClause;

      }

      /* 

         values is an associative array of name/value pairs

         function will replace all items of the form <$name> with its values (the less than and greater than symbols around the key)

      */

      protected function insertValuesIntoQuery( $values ) {

         foreach ( $values as $name => $value ) {

            $this->parameters[ 'query' ] = search_replace_string($this->parameters[ 'query' ], "<$name>", $value );

         }

      } // insertValuesIntoQuery

      /*

       * function will attempt to make a constant ($value) safe for SQL depending on the type.

       * 

       * if $value is empty, $default is returned, as will happen if any of the

       * conversions (date, datetime, etc...) fail.

       * 

       * First, it will pass it through get_magic_quotes_gpc, 

       * then will run through mysql_real_escape_string

       * 

       * For strings, will encapsulate in quotes

       * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes

       * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS

       * Integer and Floats are passed through builtins intval and floatval

       * Boolean only checks the first character, a '0', 'f' and 'n' denoting false

       *    all else denoting true. The result is converted based on the variable

       *    $falsetrue, with the first char denoting false and the second denoting true

       */

      public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {

         if (strlen($value) == 0) // simply set any empty values to null

            return $default;

         // print "Processing $value as $type with default $default<br>\n";

         switch ( strtolower( $type ) ) {

            case 'string' :

            case 's' : 

                     if ( get_magic_quotes_gpc() ) 

                        $value = stripslashes($value);

                     $value = mysql_real_escape_string( $value );

                     $value = strlen( $value ) > 0 ? "'$value'" : $default;

                     break;

            case 'date' :

            case 'd' :

                     if ( $value != 'null' ) {

                        $result = strtotime( $value );

                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";

                     }

                     break;

            case 'datetime':

            case 'timestamp':

            case 'dt': 

                     if ( $value != 'null' ) {

                        $result = strtotime( $value );

                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";

                     }

                     break;

            case 'integer':

            case 'i' :  

                     $value = intval( $value );

                     break;

            case 'float':

            case 'f' :  

                     $value = floatval( $value );

                     break;

            case 'bool':

            case 'boolean':

            case 'b' :  // note, because of the way strpos works, you can not

                        // simply set $value based on the output; you MUST do

                        // as below; specifically check for false, then set the result

                        $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;

                        $value = substr( $falsetrue, $value, 0, 1 );

                        break;

         } // switch

         return $value;

      }

   } // class DBQuery

?>