MYSQL_DRIVER
MYSQL_DRIVER
Handles the actual database communication. This DbManager is for MySQL. (Set $GLOBALS['SQL_DEBUG_MODE'] to true to error_log() all SQL requests.)
$FirstDimWhereOp : string
First Dimension Where Operator - You'll probably want to ignore this option. It's for reverse compatibility reasons only.
Used for joining multiple elements within the first dimension of a WHERE clause array. Can be 'AND' or 'OR'.
$DefaultOptions : array
Default options for connecting. Can be overwritten at a global scope by changing this variable in your code
Currently, these are:
public static $DefaultOptions = array(
'new_link'=>false, //for mysqli driver, when false, persistent connections are used (recommended). for mysql driver, this is the 4th parameters for mysql_connect. see mysql_connect documentation
'client_flags'=>null, //for mysql driver only- this is the 5th parameters for mysql_connect. see mysql_connect documentation
'charset'=>'utf8mb4', //if set, OpenConnection does a mysql_set_charset() with the given option (ie 'utf8mb4'). ref: http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
'collation'=>'utf8mb4_unicode_ci', //if set with charset, OpenConnection does a "SET NAMES..." with the given option (ie 'utf8mb4_unicode_ci'). ref: http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
'driver'=>'mysqli' //can be 'mysql' or 'mysqli' - the PHP extension to use for sql interaction. note- PHP v5.5+ deprecates 'mysql' driver
'timezone'=>'', //TODO: make default '+0:00'. empty will use system default time (not recommended). use hard numbers like '+0:00' cause timezone abbreviations must be defined in mysql otherwise, otherwise they may take no effect
);__construct(string $server, string $user, string $password, string $databaseName = null, array $options = null)
Constructor: pass connection values, set the db settings.
$options is an ARRAY as follows:
$options = array( //SEE self::$DefaultOptions
'new_link'=>false, //this is the 4th parameters for mysql_connect. see mysql_connect documentation
'client_flags'=>null, //this is the 5th parameters for mysql_connect. see mysql_connect documentation
'charset'=>'utf8mb4', //if set, OpenConnection does a mysql_set_charset() with the given option (ie 'utf8mb4'). ref: http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
'collation'=>'utf8mb4_unicode_ci', //if set with charset, OpenConnection does a "SET NAMES..." with the given option (ie 'utf8mb4_unicode_ci'). ref: http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
'driver'=>'mysqli', //can be 'mysql' or 'mysqli' - the PHP extension to use for sql interaction
'timezone'=>'', //recommended this is set to '+0:00'. empty will use system default time (not recommended). use hard numbers like '+0:00' cause timezone abbreviations must be defined in mysql otherwise, otherwise they may take no effect
);| string | $server | The server to connect to. Ex: "localhost" |
| string | $user | The username to connect with. Ex: "smartdb" |
| string | $password | The password to connect with. Ex: "smartdb123" |
| string | $databaseName | The database name to connect to. |
| array | $options | Assoc-array of key => value options. See description above. |
OpenConnection(array $options = null) : boolean
Establishes the connection with the MySql database based off credentials and options passed to the DbManager constructor
This function is AUTOMATICALLY invoked when the first query is made. You likely won't need to call it.
$options = array(
'skip-select-db'=>false, //doesn't do a mysql_select_db. good for creating databases and etc management
);| array | $options | See description above |
true when connected (or already connected), throws exception if there is an error
SetDatabaseName(string $databaseName, array $options = null) : null
Sets the database to use for this connection
| string | $databaseName | The database to use for this connection |
| array | $options | as follows: |
Select(array $array_select_fields, mixed $table, array $array_where = '', array $array_order = '', string $limit = '', array $options = null) : integer
Executes a SELECT statement on the currently selected database
$options = array(
'distinct' => false, //if true, does a SELECT DISTINCT for the select. Note: there must only be 1 select field, otherwise an exception is thrown
'add-column-quotes' => false, //if true, overwrites any other 'column-quotes' options (below) and will add column quotes to everything
'add-select-fields-column-quotes' => false, //if true, automatically adds `quotes` around the `column names` in select fields
'add-where-clause-column-quotes' => false, //if true, automatically adds `quotes` around the `column names` in the where clause
'add-order-clause-column-quotes' => false, //if true, automatically adds `quotes` around the `column names` in the order clause
'add-dot-notation' => false, //if true, overwrites any other 'dot-notation' options (below) and will add dot notation to everything
'add-select-fields-dot-notation' => false, //if true, automatically adds dot.notation before column names in select fields
'add-where-clause-dot-notation' => false, //if true, automatically adds dot.notation before column names in the where clause
'add-order-clause-dot-notation' => false, //if true, automatically adds dot.notation before column names in the order clause
'quote-numerics' => false, //if true, numerics will always be quoted in the where clause (ie "WHERE `price`='123'" instead of "WHERE `price`=123"). NOTE- this is for reverse compatibility. only used if $table is a string and not a SmartTable object
'force-select-db' => false, //if true, will always call mysql_select_db() with the database passed to this class
);| array | $array_select_fields | The columns to select. Ex: array("CustomerId", "Name", "EmailAddress") |
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $array_where | The WHERE clause of the query. Ex: array( array("CustomerId"=>5, "CustomerName"=>"Jack"), array("CustomerName"=>"Cindy") ) - ...WHERE (CustomerId=5 AND CustomerName='Jack') OR (CustomerName='Cindy') |
| array | $array_order | The "ORDER BY" clause. Ex: array("CustomerId"=>"asc", "CustomerName"=>"desc") ... ORDER BY CustomerId ASC, CustomerName DESC |
| string | $limit | With one argument (ie $limit="10"), the value specifies the number of rows to return from the beginning of the result set. With two arguments (ie $limit="100,10"), the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1). |
| array | $options | An array of key=>value pairs. See description above. |
Returns the number of selected rows
Insert(mixed $table, array $field_val_array, array $options = null) : integer
Executes an INSERT statement on the currently selected database
$options = array(
'add-column-quotes' => false, //if true, overwrites any other 'column-quotes' options (below) and will add column quotes to everything
'add-dot-notation' => false, //if true, overwrites any other 'dot-notation' options (below) and will add dot notation to everything
'force-select-db' => false, //if true, will call mysql_select_db() with the database passed to this class
);| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $field_val_array | Assoc array of columnName=value of values to insert. Ex: array('Name'=>'Jack', 'EmailAddress'=>'jack@frost.com') ... INSERT INTO Customer (Name, EmailAddress) VALUES ('Jack', 'jack@frost.com') |
| array | $options | An array of key=>value pairs. See description above. |
Returns the number of inserted rows (1 or 0)
Update(mixed $table, array $field_val_array, array $array_where = '', string $limit = '', array $options = null) : integer
Executes an UPDATE statement on the currently selected database
$options = array(
'add-column-quotes' => false, //if true, overwrites any other 'column-quotes' options (below) and will add column quotes to everything
'add-dot-notation' => false, //if true, overwrites any other 'dot-notation' options (below) and will add dot notation to everything
'force-select-db' => false, //if true, will call mysql_select_db() with the database passed to this class
'quote-numerics' => false, //if true, numerics will always be quoted in the where clause (ie ...WHERE `price`='123'... instead of ... WHERE `price`=123...). NOTE- this is for reverse compatibility. only used if $table is a string and not a SmartTable object
);| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $field_val_array | Assoc array of columnName=value of data to update. ex: array('col1'=>5, 'col2'=>'foo') ... UPDATE $table SET col1=5, col2='foo' ... |
| array | $array_where | The where clause. ex: array( array("id"=>5, "col1"=>"foo"), array("col2"=>"bar") ) - ...WHERE (id=5 AND col1='foo') OR (col2='bar') |
| string | $limit | The amount of rows to limit the update to (if any) from the beginning of the result set |
| array | $options | An array of key=>value pairs. See description above. |
Returns the number of updated rows
Delete(mixed $table, array $array_where = '', string $limit = '', array $options = null) : integer
Executes a DELETE statement on the currently selected database
$options = array(
'add-column-quotes' => false, //if true, overwrites any other 'column-quotes' options (below) and will add column quotes to everything
'add-dot-notation' => false, //if true, overwrites any other 'dot-notation' options (below) and will add dot notation to everything
'force-select-db' => false, //if true, will call mysql_select_db() with the database passed to this class
'quote-numerics' => false, //if true, numerics will always be quoted in the where clause (ie ...WHERE `price`='123'... instead of ... WHERE `price`=123...). NOTE- this is for reverse compatibility. only used if $table is a string and not a SmartTable object
);| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $array_where | The WHERE clause. Ex: array( array("id"=>5, "col1"=>"foo"), array("col2"=>"bar") ) - ...WHERE (id=5 AND col1='foo') OR (col2='bar') |
| string | $limit | The amount of rows to limit the delete to (if any) from the beginning of the result set |
| array | $options | An array of key=>value pairs. See description above. |
Returns the number of selected rows
Query(string $query, array $options = null) : mixed
Executes a query against the selected database. IT IS NOT RECOMMENDED THAT YOU USE THIS FUNCTION DIRECTLY.
$options = array(
'force-select-db' => false, //set to true if writing a query without dot notation (database.table.field) AND your app uses multiple databases with 1 connection (ie not using the 'new_link' flag on any database connection)
'skip-select-db' => false, //if true, will skip any call to mysql_select_db. good for creating databases and etc management
'multi-query' => false, //(NOT SUPPORTED WITH DRIVER 'mysql') - if true, will use mysqli_multi_query. use NextResult() to iterate through each query's result set. throws an exception on error
);| string | $query | The query to execute. Ex: "SELECT * FROM Customers WHERE CustomerId=1" |
| array | $options | An array of key=>value pairs. See description above. |
Returns the result of mysql_query() - For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource. For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
NextResult() : mixed
(NOT SUPPORT WITH DRIVER 'mysql') To be used with Query() and the 'multi-query' option set to true. This will get the result set of the next query in the batch from 'multi-query'. Use FetchAssoc* and FetchArray* functions to iterate over each result set of rows.
Returns true if the next result set is ready for use, false if there are no more result sets. throws an exception on error
Returns true if the next result set is ready for use, false if there are no more result sets. throws an exception on error
FetchAssocList() : array
Places the last query results into an array of ASSOC arrays, and returns it. Each row is an index in the array. Returns an empty array if there are no results.
Example:
Function returns an array similar to:
array(
0 => array(
"CustomerId" => "4",
"EmailAddress" => "jack@frost.com",
"Name" => "Jack",
),
1 => array(
"CustomerId" => "6",
"EmailAddress" => "queen@muppets.com",
"Name" => "Miss Piggy",
),
...
)An array of ASSOC arrays, and returns it. Each row is an index in the array. Returns an empty array if there are no results.
FetchArrayList() : array
Places the last query results into an array of NON-ASSOC arrays, and returns the array. Returns an empty array if there are no results.
Example:
Function returns an array similar to:
array(
0 => array(
0 => "4",
1 => "jack@frost.com",
2 => "Jack",
),
1 => array(
0 => "6",
1 => "queen@muppets.com",
2 => "Miss Piggy",
),
...
);An array of NON-ASSOC arrays, and returns the array. Returns an empty array if there are no results.
FetchAssoc() : array
Returns an ASSOC array of the last query results. Column names are the array keys. False is returned if there are no more results.
Example: if( (row = $dbManager->FetchAssoc()) ){ $row['id']...
Function returns an array similar to:
array(
"CustomerId" => "4",
"EmailAddress" => "jack@frost.com",
"Name" => "Jack",
);An ASSOC array of the last query results. Column names are the array keys. False is returned if there are no more results.
FetchArray() : array
Returns a NON-ASSOC array of the last query results. Array keys are numeric. False is returned if there are no more results.
Example - Function returns an array similar to:
array(
0 => "4",
1 => "jack@frost.com",
2 => "Jack",
);An ASSOC array of the last query results. Column names are the array keys. False is returned if there are no more results.
NumRows() : integer
Returns the number of rows returned from the last query (not affected rows!). This command is only valid for statements like SELECT or SHOW that return an actual result set. To retrieve the number of rows affected by a INSERT, UPDATE, REPLACE or DELETE query, use AffectedRows()
Returns the number of rows returned from the last query
EscapeString(string $string, array $options = null) : string
Runs mysql_real_escape_string() on the given $string and returns it.
| string | $string | The string to run mysql_real_escape_string() on. |
| array | $options | [optional] These options are passed to OpenConnection() |
Runs mysql_real_escape_string() on the given $string and returns it.
IsKeyword(string $keyword) : mixed
Checks if the given $keyword is a special keyword (ie "OR", "AND", "<", "!=", etc) and returns the match. Returns false if $keyword is not a keyword.
| string | $keyword | The keyword to check. |
The matched operator, condition, or FALSE if there is no match.
TableExists(string $databaseName, string $tableName) : boolean
Returns true if the given $tableName exists within the given $databaseName, false otherwise.
| string | $databaseName | The name of the database to check for existence |
| string | $tableName | The name of the table to check for existence within the given $databaseName |
true if the given $tableName exists within the given $databaseName, false otherwise.
DropTable(string $databaseName, string $tableName) : boolean
Removes the given $tableName from within the given $databaseName.
| string | $databaseName | The database to remove the given $tableName from |
| string | $tableName | The name of the table to remove. |
true if the table was successfully dropped or doesn't exist, false if the table exists and could not be dropped
EmptyTable(string $databaseName, string $tableName) : boolean
Emptys all rows from the given $tableName from within the given $databaseName.
| string | $databaseName | The database containing the $tableName to empty all rows from |
| string | $tableName | The name of the table to empty all rows from |
true if the table was successfully emptied, false if the table doesn't exist
CreateDatabase(string $databaseName) : integer
Returns true if the database was created, false if it already exists or was not created for some reason.
| string | $databaseName | The name of the database to create |
Returns true if the database was created or already exists, false if it not created for some reason
DropDatabase(string $databaseName) : integer
Returns true if the database was dropped, false if it doesn't exist or could not be dropped for some reason.
| string | $databaseName | The name of the database to drop |
Returns true if the database was dropped or doesn't exist, false if it could not be dropped for some reason
CopyDatabase(string $sourceDatabaseName, string $destDatabaseName, array $options = null) : boolean
Copies all structure and data from $sourceDatabaseName to $destDatabaseName. $destDatabaseName will be created if it is not already
The database user running this command will need appropriate privileges to both databases and/or the ability to create new databases
$options = array(
'create-tables' => true,
'create-database' => true,
'copy-data' => true,
'drop-existing-tables' => false,
'drop-existing-database' => false,
)| string | $sourceDatabaseName | The name of the source database |
| string | $destDatabaseName | The name of the destination database. This database will be created if it is not already |
| array | $options | An array of key-value pairs (see description above) |
true on success. May throw an exception on error.
UserExists(string $username, string $host = "localhost") : boolean
Returns true if the user exists and can connect from the given $host, false otherwise
| string | $username | The username to check for existence |
| string | $host | The host the $username can connect from |
Returns true if the user exists for the given $host, false otherwise
CreateUser(string $username, string $password, string $host = "localhost") : integer
Creates a SQL user with the given $username and $password, able to connect from the given $host
| string | $username | The username to create |
| string | $password | The password for the given $username |
| string | $host | The host that the given $username can connect from |
Returns the number of affected rows (1 if the user was created, 0 otherwise)
DropUser(string $username, string $host = "localhost") : integer
Drops the SQL user with the given $username, able to connect from the given $host
| string | $username | The username to drop |
| string | $host | The host that the given $username could connect from |
Returns the number of affected rows (1 if the user was dropped, 0 otherwise)
ChangePassword(string $username, string $password, string $host = "localhost") : integer
Changes the SQL password for the user with the given $username on the given $host
| string | $username | The username to update the password on |
| string | $password | The new password to set for this username on thie host |
| string | $host | The host that the given $username can connect from |
Returns the number of affected rows (1 if the user was created, 0 otherwise)
GrantUserPermissions( $databaseName, $username, $host = "localhost") : integer
Grants the given $username permission to the given $database, from the given $host
| $databaseName | The database name that the $username should be granted permission to |
|
| $username | The $username that should have permission to connect to the given $databaseName |
|
| $host | The host that the $username can connect from to connect to the given $databaseName |
Returns the number of affected rows (1 if the user was granted permission, 0 otherwise)
GrantGlobalFilePermissions( $username, $host = "localhost") : integer
Grants the given $username FILE permissions when connecting from the given $host
| $username | The username to grant FILE permissions to |
|
| $host | The host the given $username can connect from for FILE permissions |
Returns the number of affected rows (1 if the user was granted permission, 0 otherwise)
RevokeUserPermissions( $databaseName, $username, $host = "localhost") : integer
Revokes all permissions for the given $username to the given $databaseName, when connecting from the given $host
| $databaseName | The database name to revoke the given $username permissions from |
|
| $username | The username to revoke permissions from |
|
| $host | The host the given $username could connect from that should have permissions revoked |
Returns the number of affected rows (1 if the user was revoked permissions, 0 otherwise)
GenerateWhereClause(mixed $table, array $array_where, boolean $dotNotation = false, boolean $addColumnQuotes = false, array $options = array()) : string
Prepares the WHERE clause (from $array_where) for a SQL statement
$options = array(
'quote-numerics' => false, //if true, numerics will always be quoted in the where clause (ie ...WHERE `price`='123'... instead of ... WHERE `price`=123...). NOTE- this is for reverse compatibility. only used if $table is a string and not a SmartTable object
);| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $array_where | |
| boolean | $dotNotation | |
| boolean | $addColumnQuotes | |
| array | $options | See description above |
SetTimezone(string $timezone)
Sets the timezone for the connection. Default is empty which is system time (not recommended. store as UTC).
| string | $timezone | a valid mysql timezone. can use hard numbers like '+0:00' cause timezone abbreviations must be defined in mysql otherwise, otherwise they may take no effect |
if invalid $driver is in use
OrderArrayToString(mixed $table, array $array_order, boolean $dotNotation = false, boolean $addColumnQuotes = false) : array
Converts a sort array of structure array("id"=>"asc", "col1"=>"desc") into a string like "ORDER BY id ASC, col1 DESC"
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $array_order | |
| boolean | $dotNotation | |
| boolean | $addColumnQuotes |
Returns a sort array of structure array("id"=>"asc", "col1"=>"desc") into a string like "ORDER BY id ASC, col1 DESC"
ArrayToCSV(mixed $table, array $array, boolean $dotNotation = false, boolean $addColumnQuotes = false) : string
Converts items in $array into a comma separated value string and returns that string
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| array | $array | |
| boolean | $dotNotation | |
| boolean | $addColumnQuotes |
Converts items in $array into a comma separated value string and returns that string
GetDotNotation(mixed $table, string $column = null, boolean $addColumnQuotes = false) : string
Returns a string of appropriate dot-notation/column quites for the given $table and $column
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| string | $column | |
| boolean | $addColumnQuotes |
Returns a string of appropriate dot-notation/column quites for the given $table and $column
GenerateWhereRecursive(mixed $table, string $key, mixed $val, boolean $dotNotation = false, boolean $addColumnQuotes = false, string $column = '', string $condition = '=', string $operator = 'AND', array $options = array(), boolean $first = true) : string
Helper for GenerateWhereClause()
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| string | $key | |
| mixed | $val | |
| boolean | $dotNotation | |
| boolean | $addColumnQuotes | |
| string | $column | |
| string | $condition | |
| string | $operator | |
| array | $options | |
| boolean | $first | Ignore this. only used for recursion |
GenerateWhereSingle(mixed $table, string $column, string $condition, string $val, boolean $dotNotation, boolean $addColumnQuotes, array $options = array()) : string
Helper for GenerateWhereRecursive()
| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| string | $column | |
| string | $condition | |
| string | $val | |
| boolean | $dotNotation | |
| boolean | $addColumnQuotes | |
| array | $options |
CastQuoteValue(mixed $table, string $column, mixed $value, array $options = null) : mixed
Returns the given $value with appropriate casts and quotes for SQL queries
$options = array(
'quote-numerics' => false //for reverse compatibility. only used if $table is a string and not a SmartTable object
);| mixed | $table | The table name. Ex: "Customer". This can also be a SmartTable object- if so, data will be strongly typed and more accurate. |
| string | $column | The column name this value is used for. Used for looking up the data type in the SmartDb |
| mixed | $value | The value to check for quote usage |
| array | $options | [optional] Array of function options. See description. |
Returns the given $value with appropriate casts and quotes for SQL queries