Rev 6 | Rev 13 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?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
?>