Home Page
Projects > DSQL
Search:
DSQL Thumbnail
DSQL v2.0
Completed
A MySQL library for PHP with functionality to help facilitate cleaner and quicker SQL access
Rating: 9 (This has been an ongoing library for a long time that has constantly shaped and evolved into exactly what I want and need (Along with DWCF))
Project Time: 2007-2014: Unknown due to alot of changes over time
Languages: PHP, MySQL
Requirements: PHP, MySQL, PHP MySQL lib
Downloads: Source (See any updates below for prior versions)
Sections: Information, Content, Concepts, Updates, Comments
Information

I’ve found using php’s included MySQL functions to be cumbersome, take a lot more code than necessary, and create code that is not very readable. I am also not completely happy with PDO, so DSQL is my solution to this problem. I use it heavily in all of my professional web projects.

The classes in this library are written generically so they could easily be converted to any other database software.

Content

Github page: https://github.com/dakusan/DSQL

Simple examples

Create the primary connection

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

Create the example table:

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)

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
  • 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:
  • 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:

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)
  • 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()
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()

Function Notes

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)
  • 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
    )

Class DSQLResult

General information

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)

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:
  • FetchNext()
    • Fetch the next row
    • Returns FALSE if the next row does not exist
  • Fetch($RowNum=null)
  • GetKeyed()

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,
)
Concepts
SQL, Generic Libraries
Updates
More updates to DWCF and DSQL @ 2016-09-27 17:08:11
  • DSQL v2.0.2.2
    • Added ability via DSQL->$StrictMode variable to update MySQL strict mode
    • Added DSQL->RawQuery() function which takes just a query, and does no modification on it. I found this required due to extremely slow times on building very large queries.
    • Added DSQL->EscapeString() function which escapes a string for use in a mysql query. It has an optional parameter that adds single quotes around the result.
    • Added DSQL::PrepareInsertList() function which takes an array of names, and turns it into the following string: ['a', 'b', 'c'] => “(`a`, `b`, `c`) VALUES (?, ?, ?)”
  • DWCF v1.1.1
    • Added previous variable lookup to GetVars.VarArray.SQLLookup via %THEVAR-$VARNAME%
DSQL v2.0.2 @ 2016-09-15 01:10:21 - Download
DSQL v2.0.2
  • Added static member $InitialPrintAndDieOnError which DSQL.PrintAndDieOnError inherits on creation
  • Bug Fixes:
    • mysqli_set_charset is set to utf-8
    • In FormatSQLError() the date() function used for “Start Time” now uses “24-hour format of an hour with leading zeros” [date(“H”)] instead of “12-hour format of an hour without leading zeros” [date(“g”)]
Doing the Github thing @ 2014-10-22 04:02:21

I finally decided to get on the bandwagon and go ahead and start submitting my stuff to Github too. DSQL and DWCF are already on there, and I may or may not start adding a few older ones too. Here is my Dakusan profile.

DSQL Updates v2.0.1.0 @ 2014-10-22 02:20:52 - Download
DSQL has been updated to v2.0.1.0 with the following updates:
  • Added DSQLResult::Fetch() function
  • Added markdown version of readme file
  • Minor modifications to plaintext readme file
  • Altered short-open-php tag for better compatibility
DSQL v2.0 project added @ 2014-10-10 23:01:23 - Download

DSQL is a MySQL library for PHP with functionality to help facilitate cleaner and quicker SQL access. It was originally part of the Other Web Scripts project (called PHP MySQL Library [v1.0 Source]), but since the documentation got so large, and it is one of the two most helpful and utilized libraries I have and use right now, I decided it was time to give it its own page. I use this heavily in all of my professional web projects.

Comments
To add comments, please go to the forum page for this project (guest comments are allowed for the Projects, Posts, and Updates Forums).
Comments are owned by the user who posted them. We accept no responsibility for the contents of these comments.

No comments for this Project