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;
186 if ($NewSetting !== NULL)
188 self::$AdvancedCachingFlag = $NewSetting;
190 return self::$AdvancedCachingFlag;
209 $this->ErrorsToIgnore = $ErrorsToIgnore;
221 function Query($QueryString, $FieldName =
"")
223 # if caching is enabled
224 if (self::$CachingFlag)
226 # if SQL statement is read-only
227 if ($this->IsReadOnlyStatement($QueryString))
229 # if we have statement in cache
230 if (isset(self::$QueryResultCache[$QueryString][
"NumRows"]))
232 if (self::$QueryDebugOutputFlag)
233 { print(
"DB-C: $QueryString<br>\n"); }
235 # make sure query result looks okay
236 $this->QueryHandle = TRUE;
238 # increment cache hit counter
239 self::$CachedQueryCounter++;
241 # make local copy of results
242 $this->QueryResults = self::$QueryResultCache[$QueryString];
243 $this->NumRows = self::$QueryResultCache[$QueryString][
"NumRows"];
245 # set flag to indicate that results should be retrieved from cache
246 $this->GetResultsFromCache = TRUE;
250 # execute SQL statement
251 $this->QueryHandle = $this->RunQuery($QueryString);
252 if ($this->QueryHandle === FALSE) {
return FALSE; }
254 # save number of rows in result
255 $this->NumRows = mysql_num_rows($this->QueryHandle);
257 # if too many rows to cache
258 if ($this->NumRows >= 50)
260 # set flag to indicate that query results should not be retrieved from cache
261 $this->GetResultsFromCache = FALSE;
265 # if advanced caching is enabled
266 if (self::$AdvancedCachingFlag)
268 # save tables accessed by query
269 self::$QueryResultCache[$QueryString][
"TablesAccessed"] =
270 $this->TablesAccessed($QueryString);
274 if ($this->NumRows > 0)
277 for ($Row = 0; $Row < $this->NumRows; $Row++)
279 $this->QueryResults[$Row] =
280 mysql_fetch_assoc($this->QueryHandle);
283 # cache query results
284 self::$QueryResultCache[$QueryString] = $this->QueryResults;
288 # clear local query results
289 unset($this->QueryResults);
292 # cache number of rows
293 self::$QueryResultCache[$QueryString][
"NumRows"] = $this->NumRows;
295 # set flag to indicate that query results should be retrieved from cache
296 $this->GetResultsFromCache = TRUE;
302 # if advanced caching is enabled
303 if (self::$AdvancedCachingFlag)
305 # if table modified by statement is known
306 $TableModified = $this->TableModified($QueryString);
309 # for each cached query
310 foreach (self::$QueryResultCache as $CachedQueryString => $CachedQueryResult)
312 # if we know what tables were accessed
313 if ($CachedQueryResult[
"TablesAccessed"])
315 # if tables accessed include the one we may modify
316 if (in_array($TableModified, $CachedQueryResult[
"TablesAccessed"]))
318 # clear cached query results
319 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
324 # clear cached query results
325 unset($GLOBALS[
"APDBQueryResultCache"][$CachedQueryString]);
331 # clear entire query result cache
332 self::$QueryResultCache = array();
337 # clear entire query result cache
338 self::$QueryResultCache = array();
341 # execute SQL statement
342 $this->QueryHandle = $this->RunQuery($QueryString);
343 if ($this->QueryHandle === FALSE) {
return FALSE; }
345 # set flag to indicate that query results should not be retrieved from cache
346 $this->GetResultsFromCache = FALSE;
350 $this->RowCounter = 0;
352 # increment query counter
353 self::$QueryCounter++;
357 # execute SQL statement
358 $this->QueryHandle = $this->RunQuery($QueryString);
359 if ($this->QueryHandle === FALSE) {
return FALSE; }
362 if (($FieldName !=
"") && ($this->QueryHandle != FALSE))
368 return $this->QueryHandle;
379 return $this->ErrMsg;
400 if ($NewValue !== NULL) { self::$DisplayErrors = $NewValue; }
401 return self::$DisplayErrors;
410 # if caching is enabled and query was cached
411 if (self::$CachingFlag && $this->GetResultsFromCache)
413 # return cached number of rows to caller
414 return $this->NumRows;
418 # call to this method after an unsuccessful query
419 if (!is_resource($this->QueryHandle))
424 # retrieve number of rows and return to caller
425 return mysql_num_rows($this->QueryHandle);
435 # if caching is enabled and query was cached
436 if (self::$CachingFlag && $this->GetResultsFromCache)
438 # if rows left to return
439 if ($this->RowCounter < $this->NumRows)
441 # retrieve row from cache
442 $Result = $this->QueryResults[$this->RowCounter];
444 # increment row counter
455 # call to this method after successful query
456 if (is_resource($this->QueryHandle))
458 $Result = mysql_fetch_assoc($this->QueryHandle);
461 # call to this method after unsuccessful query
468 # return row to caller
479 # assume no rows will be returned
482 # for each available row
484 while ((($RowsFetched < $NumberOfRows) || ($NumberOfRows == NULL)) && ($Row = $this->
FetchRow()))
491 # return array of rows to caller
513 if ($IndexFieldName != NULL)
515 $Array[$Record[$IndexFieldName]] = $Record[$FieldName];
519 $Array[] = $Record[$FieldName];
536 return isset($Record[$FieldName]) ? $Record[$FieldName] : NULL;
548 return (
int)$this->
Query(
549 "SELECT LAST_INSERT_ID() AS InsertId FROM ".$TableName,
565 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
567 # expand condition if supplied
568 if ($Condition != NULL) { $Condition =
" WHERE ".$Condition; }
570 # read cached record from database if not already loaded
571 if (!isset($CachedRecord))
573 $this->
Query(
"SELECT * FROM ".$TableName.
" ".$Condition);
577 # if new value supplied
580 # update value in database
581 $this->
Query(
"UPDATE $TableName SET $FieldName = "
582 .(($NewValue === NULL) ?
"NULL" :
"'".addslashes($NewValue).
"'")
585 # update value in cached record
586 $CachedRecord[$FieldName] = $NewValue;
589 # return value from cached record to caller
590 return isset($CachedRecord[$FieldName])
591 ? $CachedRecord[$FieldName] : NULL;
608 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
612 $Condition, $CachedRecord);
629 $TableName, $FieldName, $NewValue, $Condition, &$CachedRecord)
633 $Condition, $CachedRecord);
647 $this->
Query(
"-- ".$String);
658 $this->
Query(
"DESC ".$TableName);
659 while ($CurrentFieldName = $this->
FetchField(
"Field"))
661 if ($CurrentFieldName == $FieldName) {
return TRUE; }
673 self::$QueryDebugOutputFlag = $NewSetting;
683 return self::$QueryCounter;
694 return self::$CachedQueryCounter;
704 if (self::$QueryCounter)
706 return (self::$CachedQueryCounter / self::$QueryCounter) * 100;
716 # ---- PRIVATE INTERFACE -------------------------------------------------
724 private $QueryHandle;
725 private $QueryResults;
728 private $GetResultsFromCache;
729 private $ErrorsToIgnore = NULL;
730 private $ErrMsg = NULL;
731 private $ErrNo = NULL;
733 private static $DisplayErrors = FALSE;
735 private static $GlobalDBUserName;
736 private static $GlobalDBPassword;
737 private static $GlobalDBHostName;
738 private static $GlobalDBName;
741 private static $QueryDebugOutputFlag = FALSE;
742 # flag for whether caching is turned on
743 private static $CachingFlag = TRUE;
744 # query result advanced caching flag
745 private static $AdvancedCachingFlag = FALSE;
746 # global cache for query results
747 private static $QueryResultCache = array();
749 private static $QueryCounter = 0;
750 private static $CachedQueryCounter = 0;
751 # database connection link handles
752 private static $ConnectionHandles = array();
754 # determine whether SQL statement is one that modifies data
755 private function IsReadOnlyStatement($QueryString)
757 return preg_match(
"/^[ ]*SELECT /i", $QueryString) ? TRUE : FALSE;
760 # try to determine table modified by statement (returns FALSE if unknown)
761 private function TableModified($QueryString)
763 # assume we're not going to be able to determine table
766 # split query into pieces
767 $QueryString = trim($QueryString);
768 $Words = preg_split(
"/\s+/", $QueryString);
770 # if INSERT statement
772 if (strtoupper($Words[0]) ==
"INSERT")
774 # skip over modifying keywords
775 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
776 || (strtoupper($Words[$WordIndex]) ==
"DELAYED")
777 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
778 || (strtoupper($Words[$WordIndex]) ==
"INTO"))
783 # next word is table name
784 $TableName = $Words[$WordIndex];
786 # else if UPDATE statement
787 elseif (strtoupper($Words[0]) ==
"UPDATE")
789 # skip over modifying keywords
790 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
791 || (strtoupper($Words[$WordIndex]) ==
"IGNORE"))
796 # if word following next word is SET
797 if (strtoupper($Words[$WordIndex + 1]) ==
"SET")
799 # next word is table name
800 $TableName = $Words[$WordIndex];
803 # else if DELETE statement
804 elseif (strtoupper($Words[0]) ==
"DELETE")
806 # skip over modifying keywords
807 while ((strtoupper($Words[$WordIndex]) ==
"LOW_PRIORITY")
808 || (strtoupper($Words[$WordIndex]) ==
"IGNORE")
809 || (strtoupper($Words[$WordIndex]) ==
"QUICK"))
814 # if next term is FROM
815 if (strtoupper($Words[$WordIndex]) ==
"FROM")
817 # next word is table name
819 $TableName = $Words[$WordIndex];
823 # discard table name if it looks at all suspicious
826 if (!preg_match(
"/[a-zA-Z0-9]+/", $TableName))
832 # return table name (or lack thereof) to caller
836 # try to determine tables accessed by statement (returns FALSE if unknown)
837 private function TablesAccessed($QueryString)
839 # assume we're not going to be able to determine tables
842 # split query into pieces
843 $QueryString = trim($QueryString);
844 $Words = preg_split(
"/\s+/", $QueryString);
845 $UQueryString = strtoupper($QueryString);
846 $UWords = preg_split(
"/\s+/", $UQueryString);
848 # if SELECT statement
849 if ($UWords[0] ==
"SELECT")
851 # keep going until we hit FROM or last word
853 while (($UWords[$WordIndex] !=
"FROM")
854 && strlen($UWords[$WordIndex]))
860 if ($UWords[$WordIndex] ==
"FROM")
862 # for each word after FROM
864 while (strlen($UWords[$WordIndex]))
866 # if current word ends with comma
867 if (preg_match(
"/,$/", $Words[$WordIndex]))
869 # strip off comma and add word to table name list
870 $TableNames[] = substr($Words[$WordIndex], 0, -1);
874 # add word to table name list
875 $TableNames[] = $Words[$WordIndex];
877 # if next word is not comma
879 if ($Words[$WordIndex] !=
",")
881 # if word begins with comma
882 if (preg_match(
"/^,/", $Words[$WordIndex]))
884 # strip off comma (NOTE: modifies $Words array!)
885 $Words[$WordIndex] = substr($Words[$WordIndex], 1);
887 # decrement index so we start with this word next pass
892 # stop scanning words (non-basic JOINs not yet handled)
904 # discard table names if they look at all suspicious
907 foreach ($TableNames as $Name)
909 if (!preg_match(
"/^[a-zA-Z0-9]+$/", $Name))
917 # return table name (or lack thereof) to caller
921 private function RunQuery($QueryString)
923 if (self::$QueryDebugOutputFlag) { $QueryStartTime = microtime(TRUE); }
924 $this->QueryHandle = mysql_query($QueryString, $this->Handle);
925 if (self::$QueryDebugOutputFlag)
927 print
"DB: ".$QueryString.
" ["
928 .sprintf(
"%.2f", microtime(TRUE) - $QueryStartTime)
931 if (($this->QueryHandle === FALSE) && $this->ErrorsToIgnore)
933 foreach ($this->ErrorsToIgnore as $SqlPattern => $ErrMsgPattern)
935 if (preg_match($SqlPattern, $QueryString)
936 && preg_match($ErrMsgPattern, mysql_error($this->Handle)))
938 $this->QueryHandle = TRUE;
944 if ($this->QueryHandle === FALSE)
946 $this->ErrMsg = mysql_error($this->Handle);
947 $this->ErrNo = mysql_errno($this->Handle);
949 if (self::$DisplayErrors)
951 print(
"<b>SQL Error:</b> <i>".$this->ErrMsg
952 .
"</i> (".$this->ErrNo.
")<br/>\n");
953 print(
"<b>SQL Statement:</b> <i>"
954 .htmlspecialchars($QueryString).
"</i><br/>\n");
957 return $this->QueryHandle;
961 # define return values (numerical values correspond to MySQL error codes)
962 define(
"DB_OKAY", 0);
963 define(
"DB_ERROR", 1);
964 define(
"DB_ACCESSDENIED", 2);
965 define(
"DB_UNKNOWNDB", 3);
966 define(
"DB_UNKNOWNTABLE", 4);
967 define(
"DB_SYNTAXERROR", 5);
968 define(
"DB_DBALREADYEXISTS", 6);
969 define(
"DB_DBDOESNOTEXIST", 7);
970 define(
"DB_DISKFULL", 8);
972 # define value to designate omitted arguments (so DB values can be set to NULL)
973 define(
"DB_NOVALUE",
"!-_-_-DB_NOVALUE-_-_-!");
975 # MySQL error code mapping