Rev 4 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
<?php
require_once "DBDatabase.class.php";
require_once "Logging.class.php"; // For logging
global $LOGFILE;
define( 'HTML_QUOTE_CHAR', '"' );
define( 'CONSTANT_NO_VALUE_DROPDOWN', '--------' );
class DBQuery {
protected $query; // the query to be processed
protected $rowsAffected; // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
protected $lastInsertKey; // the value of the last row inserted (mysql only)
protected $returnData; // an array that contains the data retrieved by a query (select only)
protected $columnMetaData; // an array that contains the meta data from the query
protected $useAssociativeArray; // if true, $returnData is an array of associative array, with the column names being the key fields
protected $showErrors; // if true, will return errors to STDOUT
protected $error; // set to last error returned
protected $log; // Wil be instantiation of a Logging instance if set
protected $auditTable; // if set to a table name, all queries are written to it
protected $orderBy;
public function __construct( $query, $whereClause = null, $orderBy = null, $runImmediate = false ) {
$this->query = $query;
if ( isset( $whereClause ) ) {
$this->query .= $this->makeWhereClause( $whereClause );
}
if ( isset( $orderBy ) ) {
$this->orderBy .= ' order by ' . implode( ',', $orderBy );
}
$this->rowsAffected = 0;
$this->lastInsertKey = '';
$this->returnData = array();
$this->columnMetaData = array();
$this->useAssociativeArray = true;
$this->showErrors = true;
$this->error = '';
if (isset( $LOGFILE ) ) $this->logFile = new Logging( $LOGFILE );
if ( $runImmediate ) {
$this->run();
// if (isset( $AUDITTABLE ) ) $this->auditTable = new Logging( $LOGFILE );
}
public function __set( $name, $value ) {
$this->$name = $value;
return $value
}
public function __get( $name ) {
return isset( $this->$name ) ? $this->$name : null;
}
public function __isset( $name ) {
return isset( $this->$name );
}
/*
function actually executes the query, populating the members with the results
returns true if no errors, false if errors (see $this->error for code)
*/
function run () {
// print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";
if ( isset($this->log) ) $this->log->printLog( $query );
// if ( isset( $this->auditTable) ) audit( $query );
$result = @mysql_query($this->query);
if( mysql_errno() ) {
$this->error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$query</VAR>";
if ( $this->showErrors ) echo($this->error);
return false;
} // if
if( preg_match ( '/^\s*select/i', $this->query ) ) { // this is a select statement
$this->rowsAffected = @mysql_num_rows($result);
$this->columnMetaData = array();
for ($i = 0; $i < mysql_num_fields( $result ); $i++) {
$meta = mysql_fetch_field ( $result );
$this->columnMetaData[] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );
}
if( ! $this->rowsAffected ) return;
$this->returnData = array();
for( $i = 0; $i < $this->rowsAffected; $i++ ) {
$this->returnData[] = $this->useAssociativeArray ? mysql_fetch_assoc($result) : mysql_fetch_row($result);
}
mysql_free_result($result);
} else { // not a select statement
$this->rowsAffected = mysql_affected_rows();
$this->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->query = $sql;
$save = $this->useAssociativeArray;
$useAssociativeArray = true;
$this->run();
$useAssociativeArray = $save;
if ( $this->rowsAffected == 1 ) {
$this->returnData = $this->returnData[0];
return true;
} else
$this->error = "$this->rowsAffected rows returned from getOneRow";
return false;
} // getOneRow
// function returns the first column of the first row of data returned from query
// or null no value returned
function getOneDBValue( $sql = null ) {
if ( isset( $sql ) )
$this->query = $sql;
$save = $this->useAssociativeArray;
$useAssociativeArray = false;
$this->run();
$useAssociativeArray = $save;
return $this->rowsAffected ? $this->returnData[0][0] : null;
}
function countNumberOfRows ( $sql = null ) {
if ( isset( $sql ) )
$this->query = $sql;
$save = $this->useAssociativeArray;
$useAssociativeArray = false;
$this->run();
$useAssociativeArray = $save;
return $this->rowsAffected;
}
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)
*/
function insertValuesIntoQuery( $values ) {
foreach ( $values as $name => $value ) {
$this->query = search_replace_string($this->query, "<$name>", $value );
}
}
} // class DBQuery
?>