<?php
/**
* This file only has the base Db class in it. This sets up class variables and basic constructs only.
* Each subclass (different database type) overrides most functions (except logging) and handles database specific instances.
*
* @version $Id: db.php 138 2009-02-26 05:47:56Z chris.boulton $
* @author Chris <[email protected]>
*
* @package Library
* @subpackage Db
*/
/**
* This is the base class for the database system.
* Almost all methods are overwritten in subclasses, except for logging and for the 'FetchOne' method.
*
* @package Library
* @subpackage Db
*/
class Db
{
/**
* The global database connection.
*
* @see Connect
*
* @var Resource
*/
var $connection = null;
/**
* Where any database errors are stored.
*
* @see GetError
* @see SetError
*
* @access private
*
* @var String
*/
var $_Error = null;
/**
* What type of error this is.
*
* @see GetError
* @see SetError
*
* @access private
*
* @var String
*/
var $_ErrorLevel = E_USER_ERROR;
/**
* Determines if the list of queries executed on a page will be stored in the QueryList array.
* Useful for development / debug purposes to show a list of queries at the bottom of a page.
*
* @see Query
* @var Boolean
*/
var $StoreQueryList = false;
/**
* Determines whether a query will be logged or not. If it's false or null it won't log, if it's a filename (or path to a file) it will log.
* Useful for debug / development purposes to see all the queries being run.
*
* @see LogQuery
*
* @var String
*/
var $QueryLog = null;
/**
* Determines whether a time query will be logged or not. If it's false or null it won't log, if it's a filename (or path to a file) it will log.
* Useful for debug / development purposes to find slow queries.
*
* @see TimeQuery
*
* @var String
*/
var $TimeLog = null;
/**
* Determines whether an error will be logged or not. If it's false or null it won't log, if it's a filename (or path to a file) it will log.
* Useful for debug / development purposes to find queries that create errors.
*
* @see LogError
*
* @var String
*/
var $ErrorLog = null;
/**
* The number of queries that have been executed
*
* @var Integer
*/
var $NumQueries = 0;
/**
* Array of queries for debug purposes
*
* @var Integer
*/
var $QueryList = array();
/**
* Is magic quotes runtime on ?
*
* @var Boolean
*/
var $magic_quotes_runtime_on = false;
/**
* The database table prefix that is being used.
*
* @var String
*/
var $TablePrefix = null;
/**
* The character to enclose fields/tables with to escape the field name
*
* @var String
*/
var $EscapeChar='';
/**
* The function to call whenever there is a database error. Useful for logging errors.
*
* @var String
*/
var $ErrorCallback = null;
/**
* Are we in the error callback function ? Used to avoid logging errors that happen inside
* the callback function
*
* @var Boolean
*/
var $_InErrorCallback = false;
/**
* The number of transactions that have been 'started'.
* This is used to work out whether a 'COMMIT' will really do a commit or just a 'SAVEPOINT'.
*
* @see StartTransaction
* @see CommitTransaction
* @see RollbackTransaction
*/
var $_transaction_counter = 0;
var $_transaction_names = array();
/**
* Whether a query is being automatically retried or not.
* This is set in the Query method as a flag so we know whether to re-establish the database connection
* This only happens when a particular error code comes back so we don't try to re-run bad queries
*
* @see Query
* @see Connect
*
* @var Boolean
*/
var $_retry = false;
/**
* The hostname we are connected to.
* This is needed so we can re-establish a database connection under particular circumstances
*
* @see Connect
*
* @var String
*/
var $_hostname = '';
/**
* The username we are connected with.
* This is needed so we can re-establish a database connection under particular circumstances
*
* @see Connect
*
* @var String
*/
var $_username = '';
/**
* The password we are connected with.
* This is needed so we can re-establish a database connection under particular circumstances
*
* @see Connect
*
* @var String
*/
var $_password = '';
/**
* The database name we are connected to.
* This is needed so we can re-establish a database connection under particular circumstances
*
* @see Connect
*
* @var String
*/
var $_databasename = '';
/**
* The character set to use when connecting and querying the database server.
*/
var $charset = '';
/**
* Constructor
*
* Sets up the database connection.
* Since this is the parent class the others inherit from, this returns null when called directly.
*
* @return Null This will always return null in the base class.
*/
function Db()
{
$this->magic_quotes_runtime_on = get_magic_quotes_runtime();
return null;
}
/**
* Connect
*
* This function will connect to the database based on the details passed in.
* Since this is the parent class the others inherit from, this returns false when called directly.
*
* @return False Will always return false when called in the base class.
*/
function Connect()
{
$this->SetError('Cannot call base class method Connect directly');
return false;
}
/**
* Disconnect
*
* This function will disconnect from the database resource passed in.
* Since this is the parent class the others inherit from, this returns false when called directly.
*
* @return False Will always return false when called in the base class.
*/
function Disconnect()
{
$this->SetError('Cannot call base class method Disconnect directly');
return false;
}
/**
* SetError
*
* Stores the error in the _error var for retrieval. This function will also call the error callback if there is one specified.
*
* @param String $error The error you wish to store for retrieval.
* @param String $errorlevel The error level you wish to store.
* @param String $query (Optional) The query that was executed that caused the error if there is one.
*
* @return Void Doesn't return anything, only sets the values and leaves it at that.
*/
function SetError($error='', $errorlevel=E_USER_ERROR, $query='')
{
$this->_Error = $error;
$this->_ErrorLevel = $errorlevel;
/**
* GetError
*
* This simply returns the $_Error var and it's error level.
*
* @see SetError
*
* @return Array Returns the error and it's error level.
*/
function GetError()
{
return array($this->_Error, $this->_ErrorLevel);
}
/**
* Error
*
* This returns just the error message from the database.
*
* @see SetError
* @see _Error
*
* @return String Returns just the error message from SetError.
*/
function Error()
{
return $this->_Error;
}
/**
* GetErrorMsg
*
* This simply returns the $_Error var
*
* @access public
*
* @see SetError
*
* @return String Returns the error
*/
function GetErrorMsg()
{
return $this->_Error;
}
/**
* Query
*
* This runs a query against the database and returns the resource result.
*
* @return False Will always return false when called in the base class.
*/
function Query()
{
$this->SetError('Cannot call base class method Query directly');
return false;
}
/**
* Fetch
* This function will fetch a result from the result set passed in.
*
* @see Query
* @see SetError
*
* @return False Will always return false when called in the base class.
*/
function Fetch()
{
$this->SetError('Cannot call base class method Fetch directly');
return false;
}
/**
* LogError
* This will log all errors if ErrorLog is not false or null. Is called from Query.
*
* @param String $query The query to log.
* @param String $error The error message to log.
*
* @see ErrorLog
* @see Query
*
* @return Boolean Returns whether the query & error are logged or not. Will return false if there is no query, or if the ErrorLog variable is set to false or null.
*/
function LogError($query='', $error=false)
{
if (!$query) {
return false;
}
if (!$this->ErrorLog || $this->ErrorLog === null) {
return false;
}
if (!$fp = fopen($this->ErrorLog, 'a+')) {
return false;
}
$backtrace = '';
if (function_exists('debug_backtrace')) {
$backtrace = debug_backtrace();
$called_from = $backtrace[1];
// if the called_from[file] entry is this particular file, we used FetchOne to do the query.
// so we need to go back one more level.
if ($called_from['file'] == __FILE__) {
$called_from = $backtrace[2];
}
$backtrace = $called_from['file'] . ' (' . $called_from['line'] . ')';
}
$line = date('M d H:i:s') . "\t" . getmypid() . "\t" . $backtrace . "\t" . "Error!: " . $error . "\t" . preg_replace('%\s+%', ' ', $query) . "\n";
fputs($fp, $line, strlen($line));
fclose($fp);
return true;
}
/**
* LogQuery
* This will log all queries if QueryLog is not false or null. Is called from Query.
*
* @param String $query The query to log.
*
* @see QueryLog
* @see Query
* @see SetError
*
* @return Boolean Returns whether the query is logged or not. Will return false if there is no query or if the QueryLog variable is set to false or null.
*/
function LogQuery($query='')
{
if (!$query) {
return false;
}
if (!$this->QueryLog || $this->QueryLog === null) {
return false;
}
if (!$fp = fopen($this->QueryLog, 'ab+')) {
return false;
}
$backtrace = '';
if (function_exists('debug_backtrace')) {
$backtrace = debug_backtrace();
$called_from = $backtrace[1];
// if the called_from[file] entry is this particular file, we used FetchOne to do the query.
// so we need to go back one more level.
if ($called_from['file'] == __FILE__) {
$called_from = $backtrace[2];
}
$backtrace = $called_from['file'] . ' (' . $called_from['line'] . ')';
}
$line = date('M d H:i:s') . "\t" . getmypid() . "\t" . $backtrace . "\t" . str_replace('`', '', preg_replace('%\s+%', ' ', $query)) . "\n";
fputs($fp, $line, strlen($line));
fclose($fp);
return true;
}
function TimeQuery($query='', $timestart=0, $timeend=0)
{
if (!$this->TimeLog || $this->TimeLog === null) {
return false;
}
if (!$query) {
return false;
}
if (!$fp = fopen($this->TimeLog, 'a+')) {
return false;
}
$backtrace = '';
if (function_exists('debug_backtrace')) {
$backtrace = debug_backtrace();
$called_from = $backtrace[1];
// if the called_from[file] entry is this particular file, we used FetchOne to do the query.
// so we need to go back one more level.
if ($called_from['file'] == __FILE__) {
$called_from = $backtrace[2];
}
$backtrace = $called_from['file'] . ' (' . $called_from['line'] . ')';
}
$line = date('M d H:i:s') . "\t" . getmypid() . "\t" . $backtrace . "\t" . sprintf("%01.2f", ($timeend - $timestart)) . "\t" . preg_replace('%\s+%', ' ', $query) . "\n";
fputs($fp, $line, strlen($line));
fclose($fp);
return true;
}
/**
* FreeResult
* Frees the result from memory. The base class always returns false.
*
* @see Query
* @see SetError
*
* @return Boolean Whether freeing the result worked or not.
*/
function FreeResult()
{
$this->SetError('Can\'t call FreeResult from the base class.');
return false;
}
/**
* CountResult
* Returns the number of rows returned for the resource passed in. The base class always returns 0.
*
* @see Query
* @see SetError
*
* @return False Always returns false in the base class.
*/
function CountResult()
{
$this->SetError('Can\'t call CountResult from the base class.');
return false;
}
/**
* FetchOne
* Fetches one item from a result and returns it.
*
* @param String $result Result to fetch the item from.
* @param String $item The item to look for and return.
*
* @see Fetch
*
* @return Mixed Returns false if there is no result or item, or if the item doesn't exist in the result. Otherwise returns the item's value.
*/
function FetchOne($result=null, $item=null)
{
if ($result === null) {
return false;
}
if (!is_resource($result)) {
$result = $this->Query($result);
}
$row = $this->Fetch($result);
if (!$row) {
return false;
}
if ($item === null) {
$item = key($row);
}
if (!isset($row[$item])) {
return false;
}
if($this->magic_quotes_runtime_on) {
$row[$item] = stripslashes($row[$item]);
}
return $row[$item];
}
/**
* Concat
* Concatentates multiple strings together. The base class returns nothing - it needs to be overridden for each database type.
*
* @return False Always returns false in the base class.
*/
function Concat()
{
return false;
}
/**
* StripslashesArray
* Strips the slashes from all the elements in an entire (multidimensional) array
*
* @param Mixed $value Item you want to strip the slashes from
*
* @return Mixed Returns the same type as passed in
*/
function StripslashesArray($value)
{
if(is_array($value)) {
$value = array_map(array($this, 'StripslashesArray'), $value);
}
else {
$value = stripslashes($value);
}
return $value;
}
/**
* Quote
* Quotes the string ready for database queries. This uses addslashes if not overridden in subclasses.
*
* @param String $string String you want to quote ready for database entry.
*
* @return String String with quotes!
*/
function Quote($string='')
{
return addslashes($string);
}
/**
* LastId
*
* Returns the last insert id
*
* @param String $seq The sequence name to fetch the last id from.
*
* @return False The base class always returns false.
*/
function LastId($seq='')
{
$this->SetError('Can\'t call LastId from the base class.');
return false;
}
/**
* NextId
*
* Returns the next insert id
*
* @param String $seq The sequence name to fetch the next id from.
*
* @return False The base class always returns false.
*/
function NextId($seq='')
{
$this->SetError('Can\'t call NextId from the base class.');
return false;
}
/**
* CheckSequence
*
* Checks to make sure a sequence doesn't have multiple entries.
*
* @return False The base class always returns false.
*/
function CheckSequence()
{
$this->SetError('Can\'t call CheckSequence from the base class.');
return false;
}
/**
* ResetSequence
*
* Resets a field in a sequence table.
*
* @return False The base class always returns false.
*/
function ResetSequence()
{
$this->SetError('Can\'t call ResetSequence from the base class.');
return false;
}
/**
* OptimizeTable
*
* Runs "optimize" or "analyze" over the tablename passed in. This is useful to keep the database reasonably speedy.
*
* @param String $tablename The tablename to optimize.
*
* @return False The base class always returns false.
*/
function OptimizeTable($tablename='')
{
$this->SetError('Can\'t call OptimizeTable from the base class.');
return false;
}
/**
* GetTime
*
* Returns a float microtime so we can record how long it took to get a result from the database.
*
* @see TimeQuery
* @see MySQLDB::Query
* @see PgSQLDB::Query
*
* @return Float Returns a float microtime.
*/
function GetTime()
{
list($usec, $sec) = explode(" ", microtime());
return ((float)$usec + (float)$sec);
}
/**
* FetchRow
*
* Fetch a single row from the database or false on error
*
* @param string $query The sql query to fetch the row
*
* @return mixed array of data or false on error
*/
function FetchRow($query)
{
if (empty($query)) {
return false;
}
$result = $this->Query($query);
return $this->Fetch($result);
}
/**
* NumAffected
*
* The base class always returns false.
* @param mixed $null Placeholder for postgres compatability
*
* @return False The base class always returns false.
*/
function NumAffected($null=null)
{
$this->SetError('Can\'t call NumAffected from the base class.');
return false;
}
/**
* UnbufferedQuery
*
* The base class always returns false. UnbufferedQuery is also a mysql-only feature.
*
* @param String This is a placeholder which the sub-classes take, not used in the base class.
*
* @return False The base class always returns false.
*/
function UnbufferedQuery($query=null)
{
$this->SetError('Can\'t call UnbufferedQuery from the base class. Also note this is a mysql-only feature.');
return false;
}
/**
* Build and execute a database insert query from an array of keys/values.
*
* @param string The table to insert into.
* @param array Associative array of key/value pairs to insert.
* @param bool TRUE to interpret NULL as being database NULL, FALSE to mean an empty string
* @return mixed Insert ID or true on successful insertion, false on failure.
*/
function InsertQuery($table, $values, $useNullValues=false)
{
$keys = array_keys($values);
$fields = implode($this->EscapeChar.",".$this->EscapeChar, $keys);
if ($this->Query($query)) {
// only return last id if it contains a valid value, otherwise insertquery reports as failed if it returns a false value (0, null etc)
if ((int)$this->LastId() > 0) {
return $this->LastId();
}
else {
return true;
}
}
else {
return false;
}
}
/**
* Build and execute a database update query from an array of keys/values.
*
* @param string The table to insert into.
* @param array Associative array containing key/value pairs to update.
* @param string The where clause to apply to the update
* @param bool TRUE to interpret NULL as being database NULL, FALSE to mean an empty string
*
* @return boolean True on success, false on error.
*/
function UpdateQuery($table, $values, $where="", $useNullValues=false)
{
$fields = array();
foreach ($values as $k => $v) {
$query = sprintf('UPDATE [|PREFIX|]%s SET %s', $table, $fields);
if ($this->Query($query)) {
return true;
}
else {
return false;
}
}
/**
* DeleteQuery
* Formats a delete query based on the table name passed in and the query which could include a where clause and/or an order by etc.
* If the database type supports a delete query with a limit clause, this is the method called.
* If the database type doesn't support this (for example postgresql), then this method is overridden in the subclass.
* If a query is not passed in, then this returns false as a safe-guard against accidentally deleting all of your table records.
*
* @param String $table The table you want to delete from.
* @param String $query The query to restrict which entries to delete. If this is not supplied, the function returns false.
* @param Int $limit The number of entries you want to delete. This is usually left off altogether.
*
* @see Query
*
* @return Mixed Returns false if no query is passed in, or if an invalid limit is supplied. Otherwise returns the result from Query
*/
function DeleteQuery($table='', $query=null, $limit=0)
{
if ($query === null) {
return false;
}
/**
* Start a transaction
*
* Method will start a transaction
* If a transaction is already in progress, then it will issue a "SAVEPOINT" command.
*
* @access public
* @return bool TRUE if the transaction was successfully created, FALSE otherwise
*/
function StartTransaction()
{
/**
* If there are no transactions open, start one up.
*/
if ($this->_transaction_counter == 0) {
$this->_transaction_counter++;
return (bool)$this->Query("START TRANSACTION");
}
/**
* If there is a transaction open, work out a new "name" and issue a "SAVEPOINT" command.
*/
$name = $this->_generate_transaction_name();
$this->_transaction_counter++;
return (bool)$this->Query("SAVEPOINT " . $name);
}
/**
* Commit all open transactions and save points
*
* This will commit all save points and open transactions until everything is done.
* By issuing a "COMMIT" statement, the db will automatically commit everything it has done.
* We need to clear the transaction counter & transaction names.
*
* @return Boolean Returns whether the "COMMIT" call was successful or not.
*/
function CommitAllTransactions()
{
$this->_transaction_counter = 0;
$this->_transaction_names = array();
return (bool)$this->Query("COMMIT");
}
/**
* Commit a transaction
*
* Method will commit a transaction if it's the last transaction in progress.
* If more than one transaction is in progress, then it will simply "ignore" the commit (there is no way to "commit" a savepoint or sub-transaction).
* This will just decrement the number of transactions we have open and remove the last transaction name from the queue.
*
* @access public
* @return bool TRUE if the transaction was successfully commited, FALSE otherwise
*/
function CommitTransaction()
{
/**
* If there are no transactions open, return false.
*/
if ($this->_transaction_counter < 1) {
return false;
}
if ($this->_transaction_counter == 1) {
$this->_transaction_counter--;
return (bool)$this->Query("COMMIT");
}
/**
* If we're in a transaction, all we need to do is get rid of the last 'savepoint' name
* We can't actually "commit" a savepoint.
*/
$name = array_pop($this->_transaction_names);
$this->_transaction_counter--;
return true;
}
/**
* Rollback a transaction
*
* Method will completely rollback a transaction if it's the last one in progress.
* If more than one transaction is in progress, then it will rollback to the last savepoint.
*
* @access public
* @return bool TRUE if the transaction was successfully rolled back, FALSE otherwise
*/
function RollbackTransaction()
{
/**
* If there are no transactions open, return false.
*/
if ($this->_transaction_counter < 1) {
return false;
}
if ($this->_transaction_counter == 1) {
$this->_transaction_counter--;
return (bool)$this->Query("ROLLBACK");
}
/**
* Rollback all open transactions and save points
*
* This will rollback all save points and open transactions until everything is done.
* By issuing a "ROLLBACK" statement, the db will automatically clear everything it has done.
* We need to clear the transaction counter & transaction names.
*
* @return Boolean Returns whether the "ROLLBACK" call was successful or not.
*/
function RollbackAllTransactions()
{
$this->_transaction_counter = 0;
$this->_transaction_names = array();
return (bool)$this->Query("ROLLBACK");
}
/**
* _generate_transaction_name
* Generates a random transaction name for use with "SAVEPOINT" calls.
* This saves having to name your transactions, it's all handled automatically.
* The name is kept in the _transaction_names array so we can check it is unique and also be able to go back to a previous savepoint if necessary.
*
* @see _transaction_names
* @see StartTransaction
* @see CommitTransaction
* @see RollbackTransaction
*
* @return String Returns a random transaction name starting with 'interspire'.
*/
function _generate_transaction_name()
{
while (true) {
$name = uniqid('interspire');
if (!in_array($name, $this->_transaction_names)) {
$this->_transaction_names[] = $name;
return $name;
}
}
}
/**
* Update FullText index
*
* This update the database FullText index, only valid for SQL Server
*
* @return Boolean True if update process success. Otherwise, return False
*/
function UpdateFullTextIndex()
{
return true;
}
/**
* IsFulltextInstalled
* Check if Full Text is Installed
*
* This will check if the full text is installed on the SQL Server. This only applies to MS SQL Server.
*
* @return Boolean True if Full Text Service is Installed. Otherwise, return False
*/
function IsFulltextInstalled()
{
return true;
}
/**
* Version
* Retrieves the version number of the DB.
*
* @return String The version number of the DB, e.g. "4.1.1".
*/
function Version()
{
$result = $this->Query("SELECT VERSION()");
return $this->FetchOne($result);
}
/**
* SubString
* Get the substring from a specified string.
*
* @param String $str The target string.
* @param Int $from The position that the substring started (1 = the first character in the string)
* @param Int $len The length of the string that we would like to retrieve, start from the $from param
*
* @return String If all the param is valid, this will return the substring SQL string. Otherwise, it will return an empty string.
*/
function SubString($str = '', $from = 1, $len = 1)
{
if ($str == '') {
return '';
}
return " SUBSTRING(".$this->Quote($str).", $from, $len) ";
}
}
และต่อกับ mysql ดังไฟล์นี้
mysql.php
<?php
/**
* This file handles mysql database connections, queries, procedures etc.
* Most functions are overridden from the base object.
*
* @version $Id: mysql.php 138 2009-02-26 05:47:56Z chris.boulton $
* @author Chris <[email protected]>
*
* @package Db
* @subpackage MySQLDb
*/
/**
* Include the base database class.
*/
require_once(dirname(__FILE__).'/db.php');
if (!function_exists('mysql_connect')) {
die("Your PHP installation does not have MySQL support. Please enable MySQL support in PHP or ask your web host to do so for you.");
}
/**
* This is the class for the MySQL database system.
*
* @package Db
* @subpackage MySQLDb
*/
class MySQLDb extends Db
{
/**
* Should we use mysql_real_escape_string or the older mysql_escape_string function ?
*
* @var Boolean
*/
var $use_real_escape = false;
/**
* Is magic quotes runtime on ?
*
* @var Boolean
*/
var $magic_quotes_runtime_on = false;
/**
* MySQL uses ` to escape table/database names
*
* @var String
*/
var $EscapeChar = '`';
/**
* This flag is checked when Query is called to see which mode to run the query in.
* Calling the UnbufferedQuery function sets this flag to true, then lets the main Query function handle the rest.
*
* @see UnbufferedQuery
* @see Query
*
* @var Boolean Defaults to false (don't run in unbuffered mode).
*/
var $_unbuffered_query = false;
/**
* Constructor
* Sets up the database connection.
* Can pass in the hostname, username, password and database name if you want to.
* If you don't it will set up the base class, then you'll have to call Connect yourself.
*
* @param String $hostname Name of the server to connect to.
* @param String $username Username to connect to the server with.
* @param String $password Password to connect with.
* @param String $databasename Database name to connect to.
*
* @see Connect
* @see GetError
*
* @return Mixed Returns false if no connection can be made - the error can be fetched by the Error() method. Returns the connection result if it can be made. Will return Null if you don't pass in the connection details.
*/
function MySQLDb($hostname='', $username='', $password='', $databasename='')
{
$this->use_real_escape = version_compare(PHP_VERSION, '4.3.0', '>=');
$this->magic_quotes_runtime_on = get_magic_quotes_runtime();
/**
* Connect
* This function will connect to the database based on the details passed in.
*
* @param String $hostname Name of the server to connect to.
* @param String $username Username to connect to the server with.
* @param String $password Password to connect with.
* @param String $databasename Database name to connect to.
*
* @see SetError
*
* @return False|Resource Returns the resource if the connection is successful. If anything is missing or incorrect, this will return false.
*/
function Connect($hostname=null, $username=null, $password=null, $databasename=null)
{
// Set the character set if we have one
if($this->charset) {
$this->Query('SET NAMES '.$this->charset);
}
return $this->connection;
}
/**
* Disconnect
* This function will disconnect from the database handler passed in.
*
* @param String $resource Resource to disconnect from
*
* @see SetError
*
* @return Boolean If the resource passed in is not valid, this will return false. Otherwise it returns the status from pg_close.
*/
function Disconnect($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource');
return false;
}
$close_success = mysql_close($resource);
if ($close_success) {
$this->connection = null;
}
return $close_success;
}
/**
* Query
* This function will run a query against the database and return the result of the query.
*
* @param String $query The query to run.
*
* @see LogQuery
* @see SetError
*
* @return Mixed Returns false if the query is empty or if there is no result. Otherwise returns the result of the query.
*/
function Query($query='')
{
// if we're retrying a query, we have to kill the old connection and grab it again.
// if we don't, we get a cached connection which won't work.
if ($this->_retry) {
$this->Connect();
}
// Trim query
$query = trim($query);
if (!$query) {
$this->_retry = false;
$this->SetError('Query passed in is empty');
return false;
}
if (!$result) {
$error = mysql_error($this->connection);
$errno = mysql_errno($this->connection);
if ($this->ErrorLog !== null) {
$this->LogError($query, $error);
}
$this->SetError($error, E_USER_ERROR, $query);
// we've already retried? don't try again.
// or if the error is not '2006', then don't bother going any further.
if ($this->_retry || $errno !== 2006) {
$this->_retry = false;
return false;
}
// error 2006 is 'server has gone away'
// http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
if ($errno === 2006) {
$this->_retry = true;
return $this->Query($query);
}
}
// make sure we set the 'retry' flag back to false if we are returning a result set.
$this->_retry = false;
return $result;
}
/**
* UnbufferedQuery
* Runs a query in 'unbuffered' mode which means that the whole result set isn't loaded in to memory before returning it.
* Calling this function sets a flag in the class to say run the query in unbuffered mode, then uses Query to handle the rest.
*
* @param String $query The query to run in unbuffered mode.
*
* @see _unbuffered_query
* @see Query
*
* @return Mixed Returns the result from the Query function.
*/
function UnbufferedQuery($query='')
{
$this->_unbuffered_query = true;
return $this->Query($query);
}
/**
* Fetch
* This function will fetch a result from the result set passed in.
*
* @param String $resource The result from calling Query. Returns an associative array (not an indexed based one)
*
* @see Query
* @see SetError
* @see StripslashesArray
*
* @return Mixed Returns false if the result is empty. Otherwise returns the next result.
*/
function Fetch($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource');
return false;
}
/**
* NextId
* Fetches the next id from the sequence passed in
*
* @param String $sequencename Sequence Name to fetch the next id for.
* @param String $idcolumn The name of the column for the id field. By default this is 'id'.
*
* @see Query
*
* @return Mixed Returns false if there is no sequence name or if it can't fetch the next id. Otherwise returns the next id
*/
function NextId($sequencename=false, $idcolumn='id')
{
if (!$sequencename) {
return false;
}
$query = 'UPDATE '.$sequencename.' SET ' . $idcolumn . '=LAST_INSERT_ID(' . $idcolumn . '+1)';
$result = $this->Query($query);
if (!$result) {
return false;
}
return mysql_insert_id($this->connection);
}
/**
* FullText
* Fulltext works out how to handle full text searches. Returns an sql statement to append to enable full text searching.
*
* @param Mixed $fields Fields to search against. This can be an array or a single field.
* @param String $searchstring String to search for against the fields
* @param Bool $booleanmode In MySQL, is this search in boolean mode ?
*
* @return Mixed Returns false if either fields or searchstring aren't present, otherwise returns a string to append to an sql statement.
*/
function FullText($fields=null, $searchstring=null, $booleanmode=false)
{
if ($fields === null || $searchstring === null) {
return false;
}
if (is_array($fields)) {
$fields = implode(',', $fields);
}
if ($booleanmode) {
$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($this->CleanFullTextString($searchstring)).'\' IN BOOLEAN MODE)';
} else {
$query = 'MATCH ('.$fields.') AGAINST (\''.$this->Quote($searchstring).'\')';
}
return $query;
}
/**
* CleanFullTextString
* Cleans and properly formats an incoming search query in to a string MySQL will love to perform fulltext queries on.
* For example, the and/or words are replaced with correct boolean mode formats, phrases are supported.
*
* @param String $searchstring The string you wish to clean.
* @return String The formatted string
*/
function CleanFullTextString($searchstring)
{
$searchstring = strtolower($searchstring);
$searchstring = str_replace("%", "\\%", $searchstring);
$searchstring = preg_replace("#\*{2,}#s", "*", $searchstring);
$searchstring = preg_replace("#([\[\]\|\.\,:])#s", " ", $searchstring);
$searchstring = preg_replace("#\s+#s", " ", $searchstring);
$words = array();
// Does this search string contain one or more phrases?
$quoted_string = false;
if (strpos($searchstring, "\"") !== false) {
$quoted_string = true;
}
$in_quote = false;
$searchstring = explode("\"", $searchstring);
foreach ($searchstring as $phrase) {
$phrase = trim($phrase);
if ($phrase != "") {
if ($in_quote == true) {
$words[] = "\"{$phrase}\"";
} else {
$split_words = preg_split("#\s{1,}#", $phrase, -1);
if (!is_array($split_words)) {
continue;
}
/**
* AddLimit
* This function creates the SQL to add a limit clause to an sql statement.
*
* @param Int $offset Where to start fetching the results
* @param Int $numtofetch Number of results to fetch
*
* @return String The string to add to the end of the sql statement
*/
function AddLimit($offset=0, $numtofetch=0)
{
$offset = intval($offset);
$numtofetch = intval($numtofetch);
/**
* FreeResult
* Frees the result from memory.
*
* @param String $resource The result resource you want to free up.
*
* @return Boolean Whether freeing the result worked or not.
*/
function FreeResult($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$this->SetError('Resource '.$resource.' is not really a resource');
return false;
}
$result = mysql_free_result($resource);
return $result;
}
/**
* CountResult
* Returns the number of rows returned for the resource passed in
*
* @param String $resource The result from calling Query
*
* @see Query
* @see SetError
*
* @return Int Number of rows from the result
*/
function CountResult($resource=null)
{
if ($resource === null) {
$this->SetError('Resource is a null object');
return false;
}
if (!is_resource($resource)) {
$resource = $this->Query($resource);
}
$count = mysql_num_rows($resource);
return $count;
}
/**
* Concat
* Concatentates multiple strings together. This method is mysql specific. It doesn't matter how many arguments you pass in, it will handle them all.
* If you pass in one argument, it will return it straight away.
* Otherwise, it will use the mysql specific CONCAT function to put everything together and return the new string.
*
* @return String Returns the new string with all of the arguments concatenated together.
*/
function Concat()
{
$num_args = func_num_args();
if ($num_args < 1) {
return func_get_arg(0);
}
$all_args = func_get_args();
$returnstring = 'CONCAT('.implode(',', $all_args).')';
return $returnstring;
}
/**
* Quote
* Quotes the string ready for database queries. Runs mysql_escape_string or mysql_real_escape_string depending on the php version.
*
* @param Mixed $var Variable you want to quote ready for database entry.
*
* @return Mixed $var with quotes applied to it appropriately
*/
function Quote($var='')
{
if (is_string($var) || is_numeric($var) || is_null($var)) {
if ($this->use_real_escape) {
return @mysql_real_escape_string($var, $this->connection);
} else {
return @mysql_escape_string($var, $this->connection);
}
} else if (is_array($var)) {
return array_map(array($this, 'Quote'), $var);
} else if (is_bool($var)) {
return (int) $var;
} else {
trigger_error("Invalid type passed to DB quote ".gettype($var), E_USER_ERROR);
return false;
}
}
/**
* LastId
*
* Returns the last insert id
*
* @return Int Returns mysql_insert_id from the database.
*/
function LastId($seq='')
{
return mysql_insert_id($this->connection);
}
/**
* CheckSequence
*
* Checks to make sure a sequence doesn't have multiple entries.
*
* @return Boolean Returns true if there is exactly 1 entry in the sequence table, otherwise returns false.
*/
function CheckSequence($seq='')
{
if (!$seq) {
return false;
}
$query = "SELECT COUNT(*) AS count FROM " . $seq;
$count = $this->FetchOne($query, 'count');
if ($count == 1) {
return true;
}
return false;
}
/**
* ResetSequence
*
* Resets a sequence to a new id.
*
* @param String $seq The sequence name to reset.
* @param Int $newid The new id to set the sequence to.
*
* @return Boolean Returns true if the sequence is reset, otherwise false.
*/
function ResetSequence($seq='', $newid=0)
{
if (!$seq) {
return false;
}
// since a sequence table only has one field, we don't care what the fieldname is.
$query = "INSERT INTO " . $seq . " VALUES (" . $newid . ")";
$result = $this->Query($query);
if (!$result) {
return false;
}
return $this->CheckSequence($seq);
}
/**
* OptimizeTable
*
* Runs "optimize" over the tablename passed in. This is useful to keep the database reasonably speedy.
*
* @param String $tablename The tablename to optimize.
*
* @see Query
*
* @return Mixed If no tablename is passed in, this returns false straight away. Otherwise it calls Query and returns the result from that.
*/
function OptimizeTable($tablename='')
{
if (!$tablename) {
return false;
}
$query = "OPTIMIZE TABLE " . $tablename;
return $this->Query($query);
}
/**
* NumAffected
*
* Returns the number of affected rows on success, and -1 if the last query failed.
*
* @param mixed $null Placeholder for postgres compatability
*
* @return int
*/
function NumAffected($null=null)
{
return mysql_affected_rows($this->connection);
}
Load balance : Server 05
