5 # A Simple SQL Database Abstraction Object
7 # Copyright 1999-2002 Axis Data
8 # This code is free software that can be used or redistributed under the
9 # terms of Version 2 of the GNU General Public License, as published by the
10 # Free Software Foundation (http://www.fsf.org).
12 # Author: Edward Almasy (almasy@axisdata.com)
14 # Part of the AxisPHP library v1.2.5
15 # For more information see http://www.axisdata.com/AxisPHP/
24 # ---- PUBLIC INTERFACE --------------------------------------------------
41 $UserName = NULL, $Password = NULL, $DatabaseName = NULL, $HostName = NULL)
43 # save DB access parameter values
44 $this->
DBUserName = $UserName ? $UserName : self::$GlobalDBUserName;
45 $this->DBPassword = $Password ? $Password : self::$GlobalDBPassword;
47 (isset(self::$GlobalDBHostName) ? self::$GlobalDBHostName
49 $this->
DBName = $DatabaseName ? $DatabaseName : self::$GlobalDBName;
51 # if we don't already have a connection or DB access parameters were supplied
53 if (!array_key_exists($HandleIndex, self::$ConnectionHandles)
54 || $UserName || $Password || $DatabaseName || $HostName)
56 # open connection to DB server
57 self::$ConnectionHandles[$HandleIndex] = mysql_connect(
59 $this->DBPassword, TRUE)
60 or die(
"Could not connect to database: ".mysql_error());
62 # set local connection handle
63 $this->Handle = self::$ConnectionHandles[$HandleIndex];
66 mysql_select_db($this->
DBName, $this->Handle)
67 or die(mysql_error($this->Handle));
71 # set local connection handle
72 $this->Handle = self::$ConnectionHandles[$HandleIndex];
82 return array(
"DBUserName",
"DBPassword",
"DBHostName",
"DBName");
89 # open connection to DB server
90 $this->Handle = mysql_connect(
92 or die(
"could not connect to database");
95 mysql_select_db($this->
DBName, $this->Handle)
96 or die(mysql_error($this->Handle));
109 # save default DB access parameters
110 self::$GlobalDBUserName = $UserName;
111 self::$GlobalDBPassword = $Password;
112 self::$GlobalDBHostName = $HostName;
114 # clear any existing DB connection handles
115 self::$ConnectionHandles = array();
124 # save new default DB name
125 self::$GlobalDBName = $DatabaseName;
127 # clear any existing DB connection handles
128 self::$ConnectionHandles = array();
161 # if cache setting has changed
162 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
165 self::$CachingFlag = $NewSetting;
167 # clear any existing cached results
168 self::$QueryResultCache = array();
171 # return current setting to caller
172 return self::$CachingFlag;
187 if ($NewSetting !== NULL)
189 self::$AdvancedCachingFlag = $NewSetting;
191 return self::$AdvancedCachingFlag;
210 $this->ErrorsToIgnore = $ErrorsToIgnore;
223 function Query($QueryString, $FieldName =
"")
225 # if caching is enabled
226 if (self::$CachingFlag)
228 # if SQL statement is read-only
229 if ($this->IsReadOnlyStatement($QueryString))
231 # if we have statement in cache
232 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
234 if (self::$QueryDebugOutputFlag)
235 { print(
"DB-C: $QueryString<br>\n"); }
237 # make sure query result looks okay
238 $this->QueryHandle = TRUE;
240 # increment cache hit counter
241 self::$CachedQueryCounter++;
243 # make local copy of results
244 $this->QueryResults = self::$QueryResultCache[$QueryString];
245 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
247 # set flag to indicate that results should be retrieved from cache
248 $this->GetResultsFromCache = TRUE;
252 # execute SQL statement
253 $this->QueryHandle = $this->RunQuery($QueryString);
254 if (!is_resource($this->QueryHandle)) {
return FALSE; }
256 # save number of rows in result
257 $this->NumRows = mysql_num_rows($this->QueryHandle);
259 # if too many rows to cache
260 if ($this->NumRows >= 50)
262 # set flag to indicate that query results should not
263 # be retrieved from cache
264 $this->GetResultsFromCache = FALSE;
268 # if advanced caching is enabled
269 if (self::$AdvancedCachingFlag)
271 # save tables accessed by query
272 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
273 $this->TablesAccessed($QueryString);
277 if ($this->NumRows > 0)
280 for ($Row = 0; $Row < $this->NumRows; $Row++)
282 $this->QueryResults[$Row] =
283 mysql_fetch_assoc($this->QueryHandle);
286 # cache query results
287 self::$QueryResultCache[$QueryString] = $this->QueryResults;
291 # clear local query results
292 unset($this->QueryResults);
295 # cache number of rows
296 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
298 # set flag to indicate that query results should be retrieved from cache
299 $this->GetResultsFromCache = TRUE;
305 # if advanced caching is enabled
306 if (self::$AdvancedCachingFlag)
308 # if table modified by statement is known
309 $TableModified = $this->TableModified($QueryString);
312 # for each cached query
313 foreach (self::$QueryResultCache
314 as $CachedQueryString => $CachedQueryResult)
316 # if we know what tables were accessed
317 if ($CachedQueryResult[
"TablesAccessed"])
319 # if tables accessed include the one we may modify
320 if (in_array($TableModified, $CachedQueryResult[
"TablesAccessed"]))
322 # clear cached query results
323 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
328 # clear cached query results
329 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
335 # clear entire query result cache
336 self::$QueryResultCache = array();
341 # clear entire query result cache
342 self::$QueryResultCache = array();
345 # execute SQL statement
346 $this->QueryHandle = $this->RunQuery($QueryString);
347 if ($this->QueryHandle === FALSE) {
return FALSE; }
349 # set flag to indicate that query results should not be retrieved from cache
350 $this->GetResultsFromCache = FALSE;
354 $this->RowCounter = 0;
356 # increment query counter
357 self::$QueryCounter++;
361 # execute SQL statement
362 $this->QueryHandle = $this->RunQuery($QueryString);
363 if ($this->QueryHandle === FALSE) {
return FALSE; }
366 if (($FieldName !=
"") && ($this->QueryHandle != FALSE))
372 return $this->QueryHandle;
391 $FHandle = fopen($FileName,
"r");
393 # if file open succeeded
394 if ($FHandle !== FALSE)
396 # while lines left in file
399 while (!feof($FHandle))
401 # read in line from file
402 $Line = fgets($FHandle, 32767);
404 # trim whitespace from line
407 # if line is not empty and not a comment
408 if (!preg_match(
"/^#/", $Line)
409 && !preg_match(
"/^--/", $Line)
412 # add line to current query
415 # if line completes a query
416 if (preg_match(
"/;$/", $Line))
420 $Result = $this->
Query($Query);
423 # if query resulted in an error that is not ignorable
424 if ($Result === FALSE)
426 # stop processing queries and set error code
438 # return number of executed queries to caller
449 return $this->ErrMsg;
470 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
471 return self::$DisplayErrors;
480 # if caching is enabled and query was cached
481 if (self::$CachingFlag && $this->GetResultsFromCache)
483 # return cached number of rows to caller
484 return $this->NumRows;
488 # call to this method after an unsuccessful query
489 if (!is_resource($this->QueryHandle))
494 # retrieve number of rows and return to caller
495 return mysql_num_rows($this->QueryHandle);
506 # if caching is enabled and query was cached
507 if (self::$CachingFlag && $this->GetResultsFromCache)
509 # if rows left to return
510 if ($this->RowCounter < $this->NumRows)
512 # retrieve row from cache
513 $Result = $this->QueryResults[$this->RowCounter];
515 # increment row counter
526 # call to this method after successful query
527 if (is_resource($this->QueryHandle))
529 $Result = mysql_fetch_assoc($this->QueryHandle);
532 # call to this method after unsuccessful query
539 # return row to caller
551 # assume no rows will be returned
554 # for each available row
556 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
564 # return array of rows to caller
589 if ($IndexFieldName != NULL)
591 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
595 $Array[] = $Record[$FieldName];
612 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
624 return (
int)$this->
Query(
625 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
644 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
646 # expand condition if supplied
647 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
649 # read cached record from database if not already loaded
650 if (!isset($CachedRecord))
652 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
656 # if new value supplied
659 # update value in database
660 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = "
661 .(($NewValue === NULL) ?
"NULL" :
"'"
662 .mysql_real_escape_string($NewValue).
"'")
665 # update value in cached record
666 $CachedRecord[$FieldName] = $NewValue;
669 # return value from cached record to caller
670 return isset($CachedRecord[$FieldName])
671 ? $CachedRecord[$FieldName] : NULL;
691 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
695 $Condition, $CachedRecord);
715 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
719 $Condition, $CachedRecord);
733 $this->
Query(
"-- ".$String);
744 $this->
Query(
"DESC ".$TableName);
745 while ($CurrentFieldName = $this->
FetchField(
"Field"))
747 if ($CurrentFieldName == $FieldName) {
return TRUE; }
760 $this->
Query(
"DESC ".$TableName);
771 self::$QueryDebugOutputFlag = $NewSetting;
781 return self::$QueryCounter;
792 return self::$CachedQueryCounter;
802 if (self::$QueryCounter)
804 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
814 # ---- PRIVATE INTERFACE -------------------------------------------------
822 private $QueryHandle;
823 private $QueryResults;
826 private $GetResultsFromCache;
827 private $ErrorsToIgnore = NULL;
828 private $ErrMsg = NULL;
829 private $ErrNo = NULL;
831 private static $DisplayErrors = FALSE;
833 private static $GlobalDBUserName;
834 private static $GlobalDBPassword;
835 private static $GlobalDBHostName;
836 private static $GlobalDBName;
839 private static $QueryDebugOutputFlag = FALSE;
840 # flag for whether caching is turned on
841 private static $CachingFlag = TRUE;
842 # query result advanced caching flag
843 private static $AdvancedCachingFlag = FALSE;
844 # global cache for query results
845 private static $QueryResultCache = array();
847 private static $QueryCounter = 0;
848 private static $CachedQueryCounter = 0;
849 # database connection link handles
850 private static $ConnectionHandles = array();
857 private function IsReadOnlyStatement($QueryString)
859 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
868 private function TableModified($QueryString)
870 # assume we're not going to be able to determine table
873 # split query into pieces
874 $QueryString = trim($QueryString);
875 $Words = preg_split(
"/\s+/", $QueryString);
877 # if INSERT statement
879 if (strtoupper($Words[0]) ==
"INSERT")
881 # skip over modifying keywords
882 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
883 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
884 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
885 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
890 # next word is table name
891 $TableName = $Words[$WordIndex];
893 # else if UPDATE statement
894 elseif (strtoupper($Words[0]) ==
"UPDATE")
896 # skip over modifying keywords
897 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
898 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
903 # if word following next word is SET
904 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
906 # next word is table name
907 $TableName = $Words[$WordIndex];
910 # else if DELETE statement
911 elseif (strtoupper($Words[0]) ==
"DELETE")
913 # skip over modifying keywords
914 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
915 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
916 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
921 # if next term is FROM
922 if (strtoupper($Words[$WordIndex]) ==
"FROM")
924 # next word is table name
926 $TableName = $Words[$WordIndex];
930 # discard table name if it looks at all suspicious
933 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
939 # return table name (or lack thereof) to caller
949 private function TablesAccessed($QueryString)
951 # assume we're not going to be able to determine tables
954 # split query into pieces
955 $QueryString = trim($QueryString);
956 $Words = preg_split(
"/\s+/", $QueryString);
957 $UQueryString = strtoupper($QueryString);
958 $UWords = preg_split(
"/\s+/", $UQueryString);
960 # if SELECT statement
961 if ($UWords[0] ==
"SELECT")
963 # keep going until we hit FROM or last word
965 while (($UWords[$WordIndex] !=
"FROM")
966 && strlen($UWords[$WordIndex]))
972 if ($UWords[$WordIndex] ==
"FROM")
974 # for each word after FROM
976 while (strlen($UWords[$WordIndex]))
978 # if current word ends with comma
979 if (preg_match(
"/,$/", $Words[$WordIndex]))
981 # strip off comma and add word to table name list
982 $TableNames[] = substr($Words[$WordIndex], 0, -1);
986 # add word to table name list
987 $TableNames[] = $Words[$WordIndex];
989 # if next word is not comma
991 if ($Words[$WordIndex] !=
",")
993 # if word begins with comma
994 if (preg_match(
"/^,/", $Words[$WordIndex]))
996 # strip off comma (NOTE: modifies $Words array!)
997 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
999 # decrement index so we start with this word next pass
1004 # stop scanning words (non-basic JOINs not yet handled)
1016 # discard table names if they look at all suspicious
1019 foreach ($TableNames as $Name)
1021 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1023 $TableNames = FALSE;
1029 # return table name (or lack thereof) to caller
1039 private function RunQuery($QueryString)
1041 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1042 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
1043 if (self::$QueryDebugOutputFlag)
1045 print
"DB: ".$QueryString.
" ["
1046 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1049 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1051 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1053 if (preg_match($SqlPattern, $QueryString)
1054 && preg_match($ErrMsgPattern, mysql_error($this->Handle)))
1056 $this->QueryHandle = TRUE;
1062 if ($this->QueryHandle === FALSE)
1064 $this->ErrMsg = mysql_error($this->Handle);
1065 $this->ErrNo = mysql_errno($this->Handle);
1067 if (self::$DisplayErrors)
1069 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1070 .
"</i> (".$this->ErrNo.
")<br/>\n");
1071 print(
"<b>SQL Statement:</b> <i>"
1072 .htmlspecialchars($QueryString).
"</i><br/>\n");
1075 return $this->QueryHandle;
1079 # define return values (numerical values correspond to MySQL error codes)
1080 define(
"DB_OKAY", 0);
1081 define(
"DB_ERROR", 1);
1082 define(
"DB_ACCESSDENIED", 2);
1083 define(
"DB_UNKNOWNDB", 3);
1084 define(
"DB_UNKNOWNTABLE", 4);
1085 define(
"DB_SYNTAXERROR", 5);
1086 define(
"DB_DBALREADYEXISTS", 6);
1087 define(
"DB_DBDOESNOTEXIST", 7);
1088 define(
"DB_DISKFULL", 8);
1090 # define value to designate omitted arguments (so DB values can be set to NULL)
1091 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1093 # MySQL error code mapping
QueryErrMsg()
Get most recent error message text set by Query().
static Caching($NewSetting=NULL)
Get or set whether query result caching is currently enabled.
static SetGlobalDatabaseName($DatabaseName)
Set default database name.
SetQueryErrorsToIgnore($ErrorsToIgnore)
Set query errors to ignore.
ExecuteQueriesFromFile($FileName)
Execute queries from specified file.
UpdateIntValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set an integer value in the database.
SQL database abstraction object with smart query caching.
DBUserName()
Get name used to connect with database server.
LastInsertId($TableName)
Get ID of row added by the last SQL "INSERT" statement.
Database($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
FetchRow()
Get next database row retrieved by most recent query.
static SetGlobalServerInfo($UserName, $Password, $HostName="localhost")
GetFieldType($TableName, $FieldName)
Get field (column) type.
NumRowsSelected()
Get number of rows returned by last query.
FetchRows($NumberOfRows=NULL)
Get specified number of database rows retrieved by most recent query.
static QueryDebugOutput($NewSetting)
Enable or disable debugging output for queries.
Query($QueryString, $FieldName="")
Query database (with caching if enabled).
FetchField($FieldName)
Pull next row from last DB query and get a specific value from that row.
FieldExists($TableName, $FieldName)
Get whether specified field exists in specified table.
static NumCacheHits()
Get the number of queries that have resulted in cache hits since program execution began...
FetchColumn($FieldName, $IndexFieldName=NULL)
Get all available values for specified database field retrieved by most recent query.
DBHostName()
Get host name of system on which database server resides.
UpdateFloatValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a float value in the database.
QueryErrNo()
Get most recent error code set by Query().
UpdateValue($TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
A convenience function to get or set a value in the database.
static AdvancedCaching($NewSetting=NULL)
Get or set whether advanced query result cachine is currently enabled.
static DisplayQueryErrors($NewValue=NULL)
Get/set whether Query() errors will be displayed.
static CacheHitRate()
Get the ratio of query cache hits to queries as a percentage.
DBName()
Get current database name.
LogComment($String)
Peform query that consists of SQL comment statement.
__wakeup()
Restore database connection when unserialized.
static NumQueries()
Get the number of queries that have been run since program execution began.