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();
137 # choose config variable to use based on server version number
139 ?
"storage_engine" :
"default_storage_engine";
141 # set storage engine in database
142 $this->
Query(
"SET ".$ConfigVar.
" = ".$Engine);
151 # retrieve version string
152 $Version = $this->
Query(
"SELECT VERSION() AS ServerVer",
"ServerVer");
154 # strip off any build/config suffix
155 $Pieces = explode(
"-", $Version);
156 $Version = array_shift($Pieces);
158 # return version number to caller
192 # if cache setting has changed
193 if (($NewSetting !== NULL) && ($NewSetting != self::$CachingFlag))
196 self::$CachingFlag = $NewSetting;
198 # clear any existing cached results
199 self::$QueryResultCache = array();
202 # return current setting to caller
203 return self::$CachingFlag;
218 if ($NewSetting !== NULL)
220 self::$AdvancedCachingFlag = $NewSetting;
222 return self::$AdvancedCachingFlag;
241 $this->ErrorsToIgnore = $ErrorsToIgnore;
254 function Query($QueryString, $FieldName =
"")
256 # if caching is enabled
257 if (self::$CachingFlag)
259 # if SQL statement is read-only
260 if ($this->IsReadOnlyStatement($QueryString))
262 # if we have statement in cache
263 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
265 if (self::$QueryDebugOutputFlag)
266 { print(
"DB-C: $QueryString<br>\n"); }
268 # make sure query result looks okay
269 $this->QueryHandle = TRUE;
271 # increment cache hit counter
272 self::$CachedQueryCounter++;
274 # make local copy of results
275 $this->QueryResults = self::$QueryResultCache[$QueryString];
276 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
278 # set flag to indicate that results should be retrieved from cache
279 $this->GetResultsFromCache = TRUE;
283 # execute SQL statement
284 $this->QueryHandle = $this->RunQuery($QueryString);
285 if (!is_resource($this->QueryHandle)) {
return FALSE; }
287 # save number of rows in result
288 $this->NumRows = mysql_num_rows($this->QueryHandle);
290 # if too many rows to cache
291 if ($this->NumRows >= 50)
293 # set flag to indicate that query results should not
294 # be retrieved from cache
295 $this->GetResultsFromCache = FALSE;
299 # if advanced caching is enabled
300 if (self::$AdvancedCachingFlag)
302 # save tables accessed by query
303 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
304 $this->TablesAccessed($QueryString);
308 if ($this->NumRows > 0)
311 for ($Row = 0; $Row < $this->NumRows; $Row++)
313 $this->QueryResults[$Row] =
314 mysql_fetch_assoc($this->QueryHandle);
317 # cache query results
318 self::$QueryResultCache[$QueryString] = $this->QueryResults;
322 # clear local query results
323 unset($this->QueryResults);
326 # cache number of rows
327 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
329 # set flag to indicate that query results should be retrieved from cache
330 $this->GetResultsFromCache = TRUE;
336 # if advanced caching is enabled
337 if (self::$AdvancedCachingFlag)
339 # if table modified by statement is known
340 $TableModified = $this->TableModified($QueryString);
343 # for each cached query
344 foreach (self::$QueryResultCache
345 as $CachedQueryString => $CachedQueryResult)
347 # if we know what tables were accessed
348 if ($CachedQueryResult[
"TablesAccessed"])
350 # if tables accessed include the one we may modify
351 if (in_array($TableModified, $CachedQueryResult[
"TablesAccessed"]))
353 # clear cached query results
354 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
359 # clear cached query results
360 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
366 # clear entire query result cache
367 self::$QueryResultCache = array();
372 # clear entire query result cache
373 self::$QueryResultCache = array();
376 # execute SQL statement
377 $this->QueryHandle = $this->RunQuery($QueryString);
378 if ($this->QueryHandle === FALSE) {
return FALSE; }
380 # set flag to indicate that query results should not be retrieved from cache
381 $this->GetResultsFromCache = FALSE;
385 $this->RowCounter = 0;
387 # increment query counter
388 self::$QueryCounter++;
392 # execute SQL statement
393 $this->QueryHandle = $this->RunQuery($QueryString);
394 if ($this->QueryHandle === FALSE) {
return FALSE; }
397 if (($FieldName !=
"") && ($this->QueryHandle != FALSE))
403 return $this->QueryHandle;
422 $FHandle = fopen($FileName,
"r");
424 # if file open succeeded
425 if ($FHandle !== FALSE)
427 # while lines left in file
430 while (!feof($FHandle))
432 # read in line from file
433 $Line = fgets($FHandle, 32767);
435 # trim whitespace from line
438 # if line is not empty and not a comment
439 if (!preg_match(
"/^#/", $Line)
440 && !preg_match(
"/^--/", $Line)
443 # add line to current query
446 # if line completes a query
447 if (preg_match(
"/;$/", $Line))
451 $Result = $this->
Query($Query);
454 # if query resulted in an error that is not ignorable
455 if ($Result === FALSE)
457 # stop processing queries and set error code
469 # return number of executed queries to caller
480 return $this->ErrMsg;
501 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
502 return self::$DisplayErrors;
511 # if caching is enabled and query was cached
512 if (self::$CachingFlag && $this->GetResultsFromCache)
514 # return cached number of rows to caller
515 return $this->NumRows;
519 # call to this method after an unsuccessful query
520 if (!is_resource($this->QueryHandle))
525 # retrieve number of rows and return to caller
526 return mysql_num_rows($this->QueryHandle);
537 # if caching is enabled and query was cached
538 if (self::$CachingFlag && $this->GetResultsFromCache)
540 # if rows left to return
541 if ($this->RowCounter < $this->NumRows)
543 # retrieve row from cache
544 $Result = $this->QueryResults[$this->RowCounter];
546 # increment row counter
557 # call to this method after successful query
558 if (is_resource($this->QueryHandle))
560 $Result = mysql_fetch_assoc($this->QueryHandle);
563 # call to this method after unsuccessful query
570 # return row to caller
582 # assume no rows will be returned
585 # for each available row
587 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL))
595 # return array of rows to caller
620 if ($IndexFieldName != NULL)
622 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
626 $Array[] = $Record[$FieldName];
643 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
654 return (
int)$this->
Query(
655 "SELECT LAST_INSERT_ID() AS InsertId",
674 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
676 # expand condition if supplied
677 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
679 # read cached record from database if not already loaded
680 if (!isset($CachedRecord))
682 $this->
Query(
"SELECT * FROM `".$TableName.
"` ".$Condition);
686 # if new value supplied
689 # update value in database
690 $this->
Query(
"UPDATE `".$TableName.
"` SET `".$FieldName.
"` = "
691 .(($NewValue === NULL) ?
"NULL" :
"'"
692 .mysql_real_escape_string($NewValue).
"'")
695 # update value in cached record
696 $CachedRecord[$FieldName] = $NewValue;
699 # return value from cached record to caller
700 return isset($CachedRecord[$FieldName])
701 ? $CachedRecord[$FieldName] : NULL;
721 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
725 $Condition, $CachedRecord);
745 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
749 $Condition, $CachedRecord);
763 $this->
Query(
"-- ".$String);
774 $this->
Query(
"DESC ".$TableName);
775 while ($CurrentFieldName = $this->
FetchField(
"Field"))
777 if ($CurrentFieldName == $FieldName) {
return TRUE; }
790 $this->
Query(
"DESC ".$TableName);
801 self::$QueryDebugOutputFlag = $NewSetting;
811 return self::$QueryCounter;
822 return self::$CachedQueryCounter;
832 if (self::$QueryCounter)
834 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
844 # ---- PRIVATE INTERFACE -------------------------------------------------
852 private $QueryHandle;
853 private $QueryResults;
856 private $GetResultsFromCache;
857 private $ErrorsToIgnore = NULL;
858 private $ErrMsg = NULL;
859 private $ErrNo = NULL;
861 private static $DisplayErrors = FALSE;
863 private static $GlobalDBUserName;
864 private static $GlobalDBPassword;
865 private static $GlobalDBHostName;
866 private static $GlobalDBName;
869 private static $QueryDebugOutputFlag = FALSE;
870 # flag for whether caching is turned on
871 private static $CachingFlag = TRUE;
872 # query result advanced caching flag
873 private static $AdvancedCachingFlag = FALSE;
874 # global cache for query results
875 private static $QueryResultCache = array();
877 private static $QueryCounter = 0;
878 private static $CachedQueryCounter = 0;
879 # database connection link handles
880 private static $ConnectionHandles = array();
887 private function IsReadOnlyStatement($QueryString)
889 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
898 private function TableModified($QueryString)
900 # assume we're not going to be able to determine table
903 # split query into pieces
904 $QueryString = trim($QueryString);
905 $Words = preg_split(
"/\s+/", $QueryString);
907 # if INSERT statement
909 if (strtoupper($Words[0]) ==
"INSERT")
911 # skip over modifying keywords
912 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
913 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
914 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
915 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
920 # next word is table name
921 $TableName = $Words[$WordIndex];
923 # else if UPDATE statement
924 elseif (strtoupper($Words[0]) ==
"UPDATE")
926 # skip over modifying keywords
927 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
928 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
933 # if word following next word is SET
934 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
936 # next word is table name
937 $TableName = $Words[$WordIndex];
940 # else if DELETE statement
941 elseif (strtoupper($Words[0]) ==
"DELETE")
943 # skip over modifying keywords
944 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
945 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
946 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
951 # if next term is FROM
952 if (strtoupper($Words[$WordIndex]) ==
"FROM")
954 # next word is table name
956 $TableName = $Words[$WordIndex];
960 # discard table name if it looks at all suspicious
963 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
969 # return table name (or lack thereof) to caller
979 private function TablesAccessed($QueryString)
981 # assume we're not going to be able to determine tables
984 # split query into pieces
985 $QueryString = trim($QueryString);
986 $Words = preg_split(
"/\s+/", $QueryString);
987 $UQueryString = strtoupper($QueryString);
988 $UWords = preg_split(
"/\s+/", $UQueryString);
990 # if SELECT statement
991 if ($UWords[0] ==
"SELECT")
993 # keep going until we hit FROM or last word
995 while (($UWords[$WordIndex] !=
"FROM")
996 && strlen($UWords[$WordIndex]))
1002 if ($UWords[$WordIndex] ==
"FROM")
1004 # for each word after FROM
1006 while (strlen($UWords[$WordIndex]))
1008 # if current word ends with comma
1009 if (preg_match(
"/,$/", $Words[$WordIndex]))
1011 # strip off comma and add word to table name list
1012 $TableNames[] = substr($Words[$WordIndex], 0, -1);
1016 # add word to table name list
1017 $TableNames[] = $Words[$WordIndex];
1019 # if next word is not comma
1021 if ($Words[$WordIndex] !=
",")
1023 # if word begins with comma
1024 if (preg_match(
"/^,/", $Words[$WordIndex]))
1026 # strip off comma (NOTE: modifies $Words array!)
1027 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
1029 # decrement index so we start with this word next pass
1034 # stop scanning words (non-basic JOINs not yet handled)
1046 # discard table names if they look at all suspicious
1049 foreach ($TableNames as $Name)
1051 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
1053 $TableNames = FALSE;
1059 # return table name (or lack thereof) to caller
1069 private function RunQuery($QueryString)
1071 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
1072 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
1073 if (self::$QueryDebugOutputFlag)
1075 print
"DB: ".$QueryString.
" ["
1076 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
1079 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
1081 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
1083 if (preg_match($SqlPattern, $QueryString)
1084 && preg_match($ErrMsgPattern, mysql_error($this->Handle)))
1086 $this->QueryHandle = TRUE;
1092 if ($this->QueryHandle === FALSE)
1094 $this->ErrMsg = mysql_error($this->Handle);
1095 $this->ErrNo = mysql_errno($this->Handle);
1097 if (self::$DisplayErrors)
1099 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
1100 .
"</i> (".$this->ErrNo.
")<br/>\n");
1101 print(
"<b>SQL Statement:</b> <i>"
1102 .htmlspecialchars($QueryString).
"</i><br/>\n");
1103 $Trace = debug_backtrace();
1104 array_shift($Trace);
1106 $OurFile = __FILE__;
1108 foreach ($Trace as $Loc)
1111 while ($Loc[
"file"][$Index] == $OurFile[$Index]) { $Index++; }
1112 $PrefixLen = min($PrefixLen, $Index);
1114 foreach ($Trace as $Loc)
1118 foreach ($Loc[
"args"] as $Arg)
1121 switch (gettype($Arg))
1124 $ArgString .= $Arg ?
"TRUE" :
"FALSE";
1133 $ArgString .=
'"<i>'.htmlspecialchars(substr($Arg, 0, 40))
1134 .((strlen($Arg) > 40) ?
"..." :
"").
'</i>"';
1140 $ArgString .= strtoupper(gettype($Arg));
1144 $ArgString .= get_class($Arg);
1147 case "unknown type":
1148 $ArgString .=
"UNKNOWN";
1153 $Loc[
"file"] = substr($Loc[
"file"], $PrefixLen);
1154 $LocString .=
" ";
1155 if (array_key_exists(
"class", $Loc))
1156 { $LocString .= $Loc[
"class"].
"::"; }
1157 $LocString .= $Loc[
"function"].
"(".$ArgString.
")"
1158 .
" - ".$Loc[
"file"].
":".$Loc[
"line"]
1161 print(
"<b>Trace:</b><br>\n".$LocString);
1164 return $this->QueryHandle;
1168 # define return values (numerical values correspond to MySQL error codes)
1169 define(
"DB_OKAY", 0);
1170 define(
"DB_ERROR", 1);
1171 define(
"DB_ACCESSDENIED", 2);
1172 define(
"DB_UNKNOWNDB", 3);
1173 define(
"DB_UNKNOWNTABLE", 4);
1174 define(
"DB_SYNTAXERROR", 5);
1175 define(
"DB_DBALREADYEXISTS", 6);
1176 define(
"DB_DBDOESNOTEXIST", 7);
1177 define(
"DB_DISKFULL", 8);
1179 # define value to designate omitted arguments (so DB values can be set to NULL)
1180 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
1182 # 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.
SetDefaultStorageEngine($Engine)
Set default database storage engine.
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.
GetServerVersion()
Get database server version number.
DBUserName()
Get name used to connect with database server.
Database($UserName=NULL, $Password=NULL, $DatabaseName=NULL, $HostName=NULL)
Object constructor.
FetchRow()
Get next database row retrieved by most recent query.
LastInsertId()
Get ID of row added by the last SQL "INSERT" statement.
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.