Simple examples

Create the primary connection

• Code: $MyConn=new DSQL('localhost', 'root', '', 'example_db');

Create the example table:

• Code: DSQL::Query('CREATE TABLE example_data (w int, x int, y int NULL, z varchar(255))');
• Notes: Example of statically using the globally remembered connection

Table prefix:

• Code: $MyConn->QueryReplacements=Array('/_TBL_/'=>'example_');
• Notes: All instances of “TBL” are now replaced with “example”

Query creation with values:

• Code:

$Values=Array(1.0, Array('30.0', null), "test ' string");
$MyConn->Query('INSERT INTO _TBL_data VALUES (?, ?, ?, ?)', $Values);
$MyConn->Query('INSERT INTO _TBL_data VALUES (?, ?, ?, ?), (?, ?, ?, ?)', 1, 2, 3, 4, 5, 6, 7, 8);

• Ran Queries:

INSERT INTO example_data VALUES (1.0, '30.0', NULL, 'test \' string')
INSERT INTO example_data VALUES (1, 2, 3, 4), (5, 6, 7, 8)

• Notes: This is also an example of flattening

Cleaning a query:

• Code:

$Data=$MyConn->CleanQuery('
  SELECT T1.w AS W1, T2.w AS W2, T1.x AS X1, T2.x AS X2
  FROM _TBL_data AS T1
  INNER JOIN example_data AS T2 ON T2.w=T1.w
  WHERE T1.x=?', 30);

• Ran Queries:

SELECT T1.w AS W1, T2.w AS W2, T1.x AS X1, T2.x AS X2 FROM example_data AS T1 INNER JOIN example_data AS T2 ON T2.w=T1.w WHERE T1.x=30

Fetching all data:

• Code:

$Result=$Data->FetchAll(); //Uses $Data from “Cleaning a query”

• Results:

Array(
  Array('1', '1', '30', '30'),
  Array('1', '1', '30', '2')
)

Fetching scalars:

• Code:

$Result=$MyConn->Query('SELECT x FROM example_data')->FetchAll();

• Return:

Array('30', '2', '6');

• Notes:
  • Since there is only 1 value returned, each item in the result set is returned as a scalar instead of an array

Fetching keyed data:

• Code:

$Result=$MyConn->Query('SELECT w, x, y, z FROM example_data WHERE x!=?', 2)->GetKeyed();

• Result:

Array(
  1=>Array('x'=>'30', 'y'=>NULL, 'z'=>"test ' string"),
  5=>Array('x'=>'6', 'y'=>'7', 'z'=>'8')
)

• Notes:
  • If only 2 return fields are requested with GetKeyed, the value of each item would instead be a scalar

Class DSQL

General information

• MySQL interface
• Statically called functions use the remembered DSQL object in $GlobalConnection
  • This allows the [default] DSQL object to not have to be remembered or passed externally. This is useful since generally only 1 database connection is established.
  • A general exception is thrown if the $GlobalConnection is not valid

Implementation specific information

• This implementation is MySQL specific
• UTF8 is automatically used as the character set
• The time zone for SQL is pulled from the php default time zone

Members

• Debug
  • If set to true, query information is retained, and error functions also output the query parameters and compiled query
• StrictMode
  • Set strict mode for the session
    • 0=Do nothing
    • 1=Turn on
    • 2=Turn off
• PrintAndDieOnError
  • If true, outputs the error as html and dies. Otherwise, throws the appropriate DSQL exception type
  • This is set to `DSQL::$InitialPrintAndDieOnError` on initialization
• QuerysInfo
  • When debug is turned on, Query() adds items to this list with its information
  • Each item contains an array of:
    • StartTime: The unix timestamp of when the query started
    • ExecutionTime: The number of seconds (including fractions) the query took to execute. This includes the round trip to the server
    • QueryFormat: The query format after replacements by $QueryReplacements
    • Values: An array of the values filled into the query
• GlobalConnection (static)
  • This DSQL is used for static calls
  • The default remembered object is the first DSQL object made (set again if a new object is made after the previously stored DSQL object is closed)
• QueryReplacements
  • An array of: RegularExpression => Replacement
  • Replaces a queries query format parameter with given regular expressions
  • For example:

Array(
  '/_TBL_/'=>'MyForum_',               //Changes all instance of “_TBL_” to “MyForum_” (useful for adding table prefixes)
  '/^(INSERT)(\s+)/i'=>'$1 IGNORE$2',  //Changes all queries starting with “INSERT ” to “INSERT IGNORE ” (for example: “insert   into foo” to “insert INGORE   into foo”)
  '/\s*;?\s*$/'=>''                    //Removes a semicolon at the end of the query and/or any whitespace at the end
)

• This happens at the top of the Query() function, so all errors and information regarding the query will reflect these replacements

Object Creation

• new DSQL($Server='localhost', $UserName='', $Password='', $Database=NULL)
  • Connect to the server
  • If database is not specified, none is selected
  • In all construct functions, if a parameter is not specified, the default is used
  • This is used if 2 or more parameters are passed to the constructor
  • Possible Errors:
    • (DSQLException) Connect failed: ...
• new DSQL($Vars)
  • Calls the primary construct with the members found in the array as parameters
  • This is used if 1 parameter is passed to the constructor
• new DSQL()
  • Takes a global variable “$DSQLInfo” and calls the DSQL($Vars) construct with it
  • This is used if no parameters are passed to the constructor
  • Possible Errors:
    • (DSQLException) Cannot find global SQL connecting information

Functions

• Close()
  • Close and clear the database connection
• Query($QueryFormat, $Variables...)
  • Execute a query
  • The query contains question marks where its variables need to be inserted. The number of variables must equal the number of question marks
  • All passed parameters (except the query format) are flattened to find the final list of variables
    • For example:

Query('INSERT INTO foo VALUES (?, ?, ?, ?, ?)', Array(01, Array('01', 1.10), '1.10'), null)

• • Will execute a query of:

INSERT INTO foo VALUES (1, '01', 1.1, '1.10', NULL)

• • NULL and numbers are passed unescaped
  • Returns a DSQLResult
  • Possible Errors:
    • (DSQLException) Query data count does not match
    • (DSQLException) SQL query error: ...
• RawQuery($FinalQuery)
  • Executes a query with no modification to the query string
• CleanQuery($QueryFormat, $Variables...)
  • Wrapper for Query(), but changes all consecutive whitespace characters into a single space, and trims beginning and end white space
• GetAffectedRows()
  • Returns the number of affected rows in the last statement
• GetInsertID()
  • Returns the insert ID for the last statement
• GetSQLConn()
  • Returns the SQL connection object ([private] SQLConn member)
• GetSQLConnectionParms()
  • Returns an array containing the connection parameters

Array('Server'=>, 'Username'=>, 'Password'=>, 'Database'=>)

• • Database is null if not initially specified
• EscapeString($Str, $Quote=false)
  • Escapes a string for use in a MySQL query
  • It has an optional parameter that adds single quotes around the result
• Self()
  • Returns the DSQL object
  • Useful for when calling statically

Function Notes

• All functions except GetSQLConn(), GetSQLConnectionParms(), and Self() can throw a (DSQLException) “Connection is not open”
• All functions are internally named starting with an underscore to facilitate the dual static/nonstatic calling functionality. For example: DSQL::_Query

Helper (static) functions

• FlattenArray($Array)
  • Flattens all nested arrays into a single array
• PrepareList($List)
  • Returns a string of question marks separated by commas whose list length is equal to the array length of the parameter
  • For example:

$Values=Array('a'=>5, 'b'=>10, 'c'=>15); //This is an example array in which the keys are the column names and the values are also the SQL’s field values. This array can be passed directly to Query()
PrepareList($Values); //The keys are not needed for this function; only the list length

• • Will return: '?, ?, ?'
• PrepareUpdateList($NameList)
  • Returns a string in the format “NAME=?, NAME=?, ..., NAME=?”
  • For example:

PrepareUpdateList(array_keys($Values)) //See $Values from above example

• • Will return: 'a=?, b=?, c=?'
  • This does not account for reserved field names that need to be enclosed in backticks
• PrepareInsertList($NameList)
  • Returns a string in the format “(`NAME1`, `NAME2`, ..., `NAME#`), (?, ?, ..., ?)”
  • For example:

PrepareInsertList(array_keys($Values)) //See $Values from above example

• • Will return: '(`a`, `b`, `c`) VALUES (?, ?, ?)'
• EscapeSearchField($S)
  • Create a LIKE search string [for MySQL]
  • This is done by:
    • Escaping with a backslash all backslashes, underscores, and percent signs
    • Adding a percent sign to the beginning and end of the string
  • For example:

EscapeSearchField('ab%c_d\\e')

• • Will return:

'%ab\\%c\\_d\\\\e%'

• • Un-php-stringified: %ab\%c\_d\\e%

Exceptions and Errors

• The error functions may be overwritten in a derived class
• EXCEPTION DSQLException
  • Standard exception with just an error message
• EXCEPTION DSQLSqlException
  • More specific exception for SQL queries
  • See “virtual SQLError” for members
• virtual Error($Msg)
  • Throws a DSQLException (if not $PrintAndDieOnError)
• virtual SQLError(

  $Error:                  The error message
  $QueryFormat:            The passed query format
  $QueryParameters:        The passed parameters. This is not flattened on the “Query data count does not match” error
  $CompiledQuery:          The compiled query with question marks replaced
  $StartTime:              The unix timestamp of when the query started
  )

  • Throws a DSQLSqlException (if not $PrintAndDieOnError)

Class DSQLResult

General information

• MySQL interface for returns from executed statements

Implementation specific information

• This implementation is MySQL specific
• If only 1 return field is requested in the SQL statement, each row is returned as just a scalar instead of an array

Gettable Members

• Parent: The DSQL parent
• SQLResult: The result object returned from SQL. TRUE if no result
• CurRowNum: The current row number ready to be fetched
• NumRows: The number of rows in the result set
• NumFields: The number of fields in the return rows
• Finished: If all rows have been retrieved
• QueryFormat, QueryParameters, CompiledQuery, StartTime: See parameter explanations from “DSQL::SQLError”
• ExecutionTime: The unix time the query took to execute with fractions of a second. It includes the round trip to the server

Object Creation

• new DSQLResult($Parent, $SQLResult, $QueryFormat, $QueryParameters, $CompiledQuery, $StartTime, $ExecutionTime)
  • See Gettable Members for parameter information
  • This will generally only be called from DSQL::Query

Functions

• FetchAll()
  • Fetch all rows into an array
• FetchRow($RowNum)
  • Fetch only the requested row number
  • Returns FALSE if the row does not exist
  • Possible Errors:
    • (DSQLSqlException) All rows have already been fetched
    • (DSQLSqlException) Cannot call FetchRow on a RowNum that has already been fetched
• FetchNext()
  • Fetch the next row
  • Returns FALSE if the next row does not exist
• Fetch($RowNum=null)
  • Wrapper function in which no parameter means FetchAll() and 1 parameter means FetchRow($RowNum)
• GetKeyed()
  • Wrapper for GetKeyedArray() with the passed array being retrieved from FetchAll()

Helper (static) functions

• GetKeyedArray($Array)
  • Returns an associative array of the passed array in which the key of each item becomes the first extracted value from the item
  • Original row ordering is maintained
  • The first key in each original item must have the same name
  • For example:

GetKeyedArray(Array(
  Array('v1'=>'b', 'v2'=>1, 'v3'=>2),
  Array('v1'=>'a', 'v2'=>3, 'v3'=>4),
  Array('v1'=>'c', 'v2'=>5, 'v3'=>6)
))

• • Will Return:

Array(
  'b'=>Array('v2'=>1,'v3'=>2),
  'a'=>Array('v2'=>3,'v3'=>4),
  'c'=>Array('v2'=>5,'v3'=>6)
)

• • If only 1 field remains after the key extraction, the value is just the scalar instead of an array
  • For example:

GetKeyedArray(Array(
  Array('v1'=>'b', 'v2'=>1),
  Array('v1'=>'a', 'v2'=>3)
))

• • Will Return:

Array(
  'b'=>1,
  'a'=>3,
)