XELOS\Framework\XF\DB\MySQLAdapter

class MySQLAdapter

Class MySQLAdapter

Properties

mysqli_result	$last_result
int	$query_count
protected int	$debug_query_count
array	$debug_profile
string\|false	$debug_last_query
int\|false	$debug_last_status
protected mysqli\|null	$link
protected string	$server
protected string	$username
protected string	$password
protected string	$db
protected int	$port
protected int	$charset
protected int	$connection_retries
protected bool	$autocommit
protected bool	$fatal_on_error
static array	$smartTimestampFields

Methods

__construct(string $server = XF_DB_SERVER, string $username = XF_DB_USER, string $password = XF_DB_PASS, string $db = XF_DB_DATABASE, string $charset = 'utf8', bool $fatal_on_error = true, int $port = 3306)

Constructur for Database Wrapper

void

__destruct()

Disconnect Server in case of object destruction

void

__sleep()

PHP __sleep -> Disconnect Server in case of object serialization

void

__wakeup()

PHP __wakeup ->Re-Connect Server in case of object unserialize

mixed

auto_query(string $type, string $table, array $values = null, array $params = array(), string $FILE = __FILE__, int $LINE = __LINE__, bool $query_type = false, bool $i18nMode = false)

No description

void

begin_transaction()

No description

bool|null

can_create_tmp_tables()

Can the current user create temporary tables?

string

check_query(string $query)

Checks the passed SQL query string for system-wide table placeholder markers and returns the query with the corresponding replacements e.g. ###sys|tablename### will be replaced to use the correct system table prefix

string

check_query_cb(array $matches)

put your comment there.

bool

close()

Close DB Connection

collect_debug_profiling(mixed $force = false, string $query = null, bool|string $file = false, bool|string $line = false, bool|mysqli_result $result = false, $cacheHit = false)

Collect Profiling Infos from Database if in DEVMODE

bool

commit()

No description

null

connect(string $charset = 'utf8')

Connects to the database

void

debug()

Prints information about the last query and it's result code

int

errno()

Returns the error code for the most recent function call

string

error()

Returns a string description of the last error

string

escape_string(mixed $string, bool $auto_format_number = true)

No description

bool

executeMysqlDump(string $filename)

Take a mysqldump backup file

executeMysqlRestore(string $filename)

Restore mysql database from a mysqldump backup file

array

extract_search_term(string $search_term)

No description

static string|string[]|null

formatQueryForSinglelineOutput($Query)

Helper to Format multi-line SQL statements into a single line for log output NOTE: The resulting query string is for display purposes only and should not be used again as query as also field values may be shortened

int|null

getMariaDBServerVersion()

Return the MariaDB Server Version if MariaDB is detected

int

getMySQLServerVersion()

Return Server Version as MYSQL Version This function will try to manipulate mariadb versions to match corresponding mysql version

string

get_charset()

No description

array

get_database_status()

Returns the "SHOW STATUS" result in an array

mysqli

get_link()

Returns the mysqli link object

int

get_query_count()

Returns the number of executed queries since connecting the database server

array

get_server_vars()

Server Variables

array|bool

get_table_structure(string $table_name)

No description

bool

hasIntDisplayWidthSupport()

Display width specification for integer data types was deprecated in MySQL 8.0.17

bool

hasJsonSupport()

Does this mysql server support JSON fields?

bool

hasUtf8mb4Support()

Returns true if the current mySQL server supports utf8mb4 (Emoji)

bool

hasUuidBinSupport()

Is UUID_TO_BIN and BIN_TO_UUID available?

bool|mixed

insert(string $table, array $values)

Inserts the passed values into the given table

bool|null

isMariaDB()

Detect if the server is a MariaDB server (used to catch some specialties of MariaDB)

bool

is_cluster()

Is Database a Galera Cluster?

mixed

lpquery($query, $params, string $file = __FILE__, int $line = __LINE__, int $cache_ttl = 0, null $cache_group = null)

Execute PQuery Wrapper for Languages

mixed|null

lquery($query, string $result_type = "mysql_fetch_array", string $file = __FILE__, int $line = __LINE__, bool $cache_ttl = false, bool $cache_group = false, bool $query_type = false)

Execute Query Wrapper for Languages

true|array

multiQuery(array $queries, bool $returnResults = false, bool $useTransaction = true)

Executes multiple queries in one request -> Preferred way e.g. to mass insert data into database as this is much faster than many single queries If an error occures the execution stops and all remaining queries won't be executed.

mixed

pquery(string $query, array $params = array(), string $file = __FILE__, int|string $line = __LINE__, int $cache_ttl = 0, null $cache_group = null)

Prepared Statement Query

prepare_debug_profiling()

Prepare Profiling Infos from Database if in DEVMODE

false|int|array|mysqli_result

query(string $query, string $result_type = "mysql_fetch_array", string $file = __FILE__, int $line = __LINE__, int $cache_ttl = null, string $cache_group = null, string $query_type = null)

Execute Query (Return Result Array/Count)

mixed

query_with_params(string $query, array $params, string $result_type = "mysql_fetch_array", string $file = __FILE__, int $line = __LINE__, int $cache_ttl = null, string $cache_group = null, string $query_type = null)

Execute Query (Return Result Array/Count) with passing of parameters e.g. use "SELECT * FROM tbl WHERE id = :id" as query and pass the value for "id" as params array ['id'=>2] All passed values will be automatically escaped for security reasons

void

rclose()

Free/Close Result Query (Use after rquery)

static string|string[]|null

removeSensitiveData($Query)

Helper to remove unwanted private and sensitive data from an SQL query

null|string|string[]

replace_query_placeholder(string $query, array $vars, string $format = 'value', array $value_list = [])

Pass a query with placeholders and pass values for placeholders as keyed array. The function will replace all placeholder with their respective values from the array and escape them accordingly

bool

rollback()

No description

bool|mysqli_result

rquery(string $query)

Execute Query (Return Ressource uses MYSQLI_USE_RESULT)

no builtin Error Handling (ON PURPOSE)

static bool

secure_query(string $query, int $allowed = XF_QUERY_ALLOW_NONE)

Check Query to avoid SQL Injections

bool

set_autocommit(bool $mode)

No description

void

set_timezone(string $timezone)

Set the TimeZone for the current mySQL Connection

bool

table_exists(string $table_name)

No description

bool

table_field_exists(string $table, string $field)

No description

bool|mixed

update(string $table, array $values, array $where, int $limit = 1)

Updates the table using the passed values

Details

at line 90
`__construct(string $server = XF_DB_SERVER, string $username = XF_DB_USER, string $password = XF_DB_PASS, string $db = XF_DB_DATABASE, string $charset = 'utf8', bool $fatal_on_error = true, int $port = 3306)`

Constructur for Database Wrapper

Initializes database connection

Parameters

string	$server
string	$username
string	$password
string	$db
string	$charset	Database Server host (IP or hostname)
bool	$fatal_on_error	fatal abbort on connection error, default true
int	$port

Returns DB->query result

Exceptions

DatabaseException

at line 1827
`void begin_transaction()`

No description

Return Value

void

at line 199
`bool|null can_create_tmp_tables()`

Can the current user create temporary tables?

Return Value

bool|null

at line 691
`string check_query(string $query)`

Parameters

string

$query

SQL Query String (with system-wide table name placeholders e.g. ###sys|tablename###)

Return Value

string

SQL Query String

at line 703
`string check_query_cb(array $matches)`

put your comment there.

Parameters

array

$matches

SQL Query String

Return Value

string

SQL Query String

at line 1306
`bool close()`

Close DB Connection

Return Value

bool

errno

at line 1544
`collect_debug_profiling(mixed $force = false, string $query = null, bool|string $file = false, bool|string $line = false, bool|mysqli_result $result = false, $cacheHit = false)`

Collect Profiling Infos from Database if in DEVMODE

Parameters

mixed	$force
string	$query
bool\|string	$file
bool\|string	$line
bool\|mysqli_result	$result
	$cacheHit

at line 1837
`bool commit()`

No description

Return Value

bool	Commit Status

at line 221
`null connect(string $charset = 'utf8')`

Connects to the database

Parameters

string

int

errno

at line 1285
`string error()`

Returns a string description of the last error

Return Value

string

error

at line 1436
`string escape_string(mixed $string, bool $auto_format_number = true)`

No description

Parameters

mixed	$string	String to be escaped
bool	$auto_format_number	If enabled float values will be converted to the correct mySQL syntax

Return Value

string

escaped string

at line 1959
`bool executeMysqlDump(string $filename)`

Take a mysqldump backup file

Parameters

string

$filename

Return Value

bool

Success

at line 1932
`executeMysqlRestore(string $filename)`

Restore mysql database from a mysqldump backup file

Parameters

string

$filename

at line 1473
`array extract_search_term(string $search_term)`

No description

Parameters

string

$search_term

Search Term (e.g. master AND proxy OR tennis), Support for AND, OR, and ""

Return Value

array

2-dim array with search term parts $return['mode'], $return['term']

at line 1922
`static string|string[]|null formatQueryForSinglelineOutput($Query)`

Parameters

$Query

string SQL Query

Return Value

string|string[]|null

at line 170
`int|null getMariaDBServerVersion()`

Return the MariaDB Server Version if MariaDB is detected

Return Value

int|null

Returns null if server is not running MariaDB

at line 151
`int getMySQLServerVersion()`

Return Server Version as MYSQL Version This function will try to manipulate mariadb versions to match corresponding mysql version

Return Value

int

at line 1864
`string get_charset()`

No description

Return Value

$table_name

Return Value

array|bool

Parameters

array	$queries	Array with query SQLs or array pairs with Query and Placeholders e.g. ['SQL1', 'SQL2, ['SQL :placeholder', ['placeholder':'Hello']]]
bool	$returnResults	Set to true to return an array of results if needed
bool	$useTransaction	By default all queries are executed within a single transaction for best performance (e.g. inserts bundled in a single transaction massively reduce the need to recalculate the index)

Return Value

true|array

If returnResults is true all results are being returned as array

Exceptions

DatabaseException

at line 539
`mixed pquery(string $query, array $params = array(), string $file = FILE, int|string $line = LINE, int $cache_ttl = 0, null $cache_group = null)`

Prepared Statement Query

Parameters

string	$query	SQL Query with classic markers (?) or named marker (e.g. ':name')
array	$params	Simple Array (for classic marker - must be the same order as in SQL) OR Assoc Array (for named marker)
string	$file	DEBUG Filename (FILE)
int\|string	$line	DEBUG Line (LINE)
int	$cache_ttl	TTL in seconds
null	$cache_group	Cache PREFIX for removal

Return Value

mixed

Exceptions

DatabaseException

at line 1588
`prepare_debug_profiling()`

Prepare Profiling Infos from Database if in DEVMODE

at line 763
`false|int|array|mysqli_result query(string $query, string $result_type = "mysql_fetch_array", string $file = FILE, int $line = LINE, int $cache_ttl = null, string $cache_group = null, string $query_type = null)`

Execute Query (Return Result Array/Count)

Parameters

string	$query	SQL Query
string	$result_type	Fetch function that is used for SELECT queries [MYSQLI_ASSOC, MYSQLI_BOTH, MYSQL_NUM. Use 'mysql_result' or 'mysql_result_nobuffer' to work on mysql result object]
string	$file	DEBUG Filename (FILE)
int	$line	DEBUG Line (LINE)
int	$cache_ttl	Cache TTL (Negativ TTL invalidates and refreshes existing caches)
string	$cache_group	Cache Group
string	$query_type	reference to string indicates if query was an update/delete or an insert (INSERT, UPDATE, DELETE)

Return Value

false|int|array|mysqli_result

Returns false in case of error or no records, Returns the number of affected rows in case of UPDATE/DELETE statement, Returns a result array in case of SELECT statement

Exceptions

DatabaseException

at line 741
`mixed query_with_params(string $query, array $params, string $result_type = "mysql_fetch_array", string $file = FILE, int $line = LINE, int $cache_ttl = null, string $cache_group = null, string $query_type = null)`

Parameters

string	$query	SQL Query
array	$params	Assoc Array with placeholder values
string	$result_type	Fetch function that is used for SELECT queries [MYSQLI_ASSOC, MYSQLI_BOTH, MYSQL_NUM or 'mysql_result']
string	$file	DEBUG Filename (FILE)
int	$line	DEBUG Line (LINE)
int	$cache_ttl	Cache TTL (Negativ TTL invalidates and refreshes existing caches)
string	$cache_group	Cache Group
string	$query_type	reference to string indicates if query was an update/delete or an insert (INSERT, UPDATE, DELETE)

Return Value

mixed

Returns false in case of error, Returns the number of affected rows in case of UPDATE/DELETE statement, Returns a result array in case of SELECT statement

Exceptions

DatabaseException

at line 519
`void rclose()`

Free/Close Result Query (Use after rquery)

Return Value

void

at line 1909
`static string|string[]|null removeSensitiveData($Query)`

Helper to remove unwanted private and sensitive data from an SQL query

Parameters

$Query

string SQL Query

Return Value

string|string[]|null

at line 1178
`null|string|string[] replace_query_placeholder(string $query, array $vars, string $format = 'value', array $value_list = [])`

Pass a query with placeholders and pass values for placeholders as keyed array. The function will replace all placeholder with their respective values from the array and escape them accordingly

Parameters

string	$query	Query String with placeholders
array	$vars	Assoc-Array with keys being the placeholder names, e.g. ['firstname'=>'Daniel'] can be referenced with ' :firstname ' in query
string	$format	Return Format: 'value' -> Returns the query with the values inserted OR 'prepared_statement' -> Returns the query with ? placeholders and the respective values in $value_list
array	$value_list	Returns the used values in the order as they are being used in the query

Return Value

null|string|string[]

Exceptions

DatabaseException

at line 1848
`bool rollback()`

No description

Return Value

bool	Rollback Status

at line 503
`bool|mysqli_result rquery(string $query)`

Execute Query (Return Ressource uses MYSQLI_USE_RESULT)

no builtin Error Handling (ON PURPOSE)

Parameters

string

$query

SQL Query

Return Value

bool|mysqli_result

Returns resource to the data source

Exceptions

DatabaseException

at line 1226
`static bool secure_query(string $query, int $allowed = XF_QUERY_ALLOW_NONE)`

Check Query to avoid SQL Injections

Parameters

string	$query
int	$allowed

Return Value

bool

at line 1818
`bool set_autocommit(bool $mode)`

No description

Parameters

bool

$mode

enable or disable autocommit

Return Value

bool

success

at line 334
`void set_timezone(string $timezone)`

Set the TimeZone for the current mySQL Connection

Parameters

string

$timezone

TimeZone in mySQL Timezone format

Return Value

void

at line 1325
`bool table_exists(string $table_name)`

No description

Parameters

string

$table_name

Return Value

bool

at line 1344
`bool table_field_exists(string $table, string $field)`

No description

Parameters

string	$table
string	$field

Return Value

bool

at line 1134
`bool|mixed update(string $table, array $values, array $where, int $limit = 1)`

Updates the table using the passed values

Parameters

string	$table
array	$values	Assoc-Array, e.g. array('fieldname'=>'fieldvalue', ....)
array	$where
int	$limit

Return Value

bool|mixed

MySQLAdapter

Properties

Methods

Details

at line 90 __construct(string $server = XF_DB_SERVER, string $username = XF_DB_USER, string $password = XF_DB_PASS, string $db = XF_DB_DATABASE, string $charset = 'utf8', bool $fatal_on_error = true, int $port = 3306)

Parameters

at line 354 void __destruct()

Return Value

at line 344 void __sleep()

Return Value

at line 363 void __wakeup()

Return Value

at line 1627 mixed auto_query(string $type, string $table, array $values = null, array $params = array(), string $FILE = __FILE__, int $LINE = __LINE__, bool $query_type = false, bool $i18nMode = false)

Parameters

Return Value

Exceptions

at line 1827 void begin_transaction()

Return Value

at line 199 bool|null can_create_tmp_tables()

Return Value

at line 691 string check_query(string $query)

Parameters

Return Value

at line 703 string check_query_cb(array $matches)

Parameters

Return Value

at line 1306 bool close()

Return Value

at line 1544 collect_debug_profiling(mixed $force = false, string $query = null, bool|string $file = false, bool|string $line = false, bool|mysqli_result $result = false, $cacheHit = false)

Parameters

at line 1837 bool commit()

Return Value

at line 221 null connect(string $charset = 'utf8')

Parameters

Return Value

Exceptions

at line 1806 void debug()

Return Value

at line 1295 int errno()

Return Value

at line 1285 string error()

Return Value

at line 1436 string escape_string(mixed $string, bool $auto_format_number = true)

Parameters

Return Value

at line 1959 bool executeMysqlDump(string $filename)

Parameters

Return Value

at line 1932 executeMysqlRestore(string $filename)

Parameters

at line 1473 array extract_search_term(string $search_term)

Parameters

Return Value

at line 1922 static string|string[]|null formatQueryForSinglelineOutput($Query)

Parameters

Return Value

at line 170 int|null getMariaDBServerVersion()

Return Value

at line 151 int getMySQLServerVersion()

Return Value

See also

at line 1864 string get_charset()

Return Value

at line 1599 array get_database_status()

Return Value

at line 1856 mysqli get_link()

Return Value

at line 372 int get_query_count()

Return Value

at line 136 array get_server_vars()

Return Value

Exceptions

at line 1358 array|bool get_table_structure(string $table_name)

Parameters

Return Value

at line 1897 bool hasIntDisplayWidthSupport()

Return Value

See also

at line 1880 bool hasJsonSupport()

Return Value

at line 90
`__construct(string $server = XF_DB_SERVER, string $username = XF_DB_USER, string $password = XF_DB_PASS, string $db = XF_DB_DATABASE, string $charset = 'utf8', bool $fatal_on_error = true, int $port = 3306)`

at line 354
`void __destruct()`

at line 344
`void __sleep()`

at line 363
`void __wakeup()`

at line 1627
`mixed auto_query(string $type, string $table, array $values = null, array $params = array(), string $FILE = FILE, int $LINE = LINE, bool $query_type = false, bool $i18nMode = false)`

at line 1827
`void begin_transaction()`

at line 199
`bool|null can_create_tmp_tables()`

at line 691
`string check_query(string $query)`

at line 703
`string check_query_cb(array $matches)`

at line 1306
`bool close()`

at line 1544
`collect_debug_profiling(mixed $force = false, string $query = null, bool|string $file = false, bool|string $line = false, bool|mysqli_result $result = false, $cacheHit = false)`

at line 1837
`bool commit()`

at line 221
`null connect(string $charset = 'utf8')`

at line 1806
`void debug()`

at line 1295
`int errno()`

at line 1285
`string error()`

at line 1436
`string escape_string(mixed $string, bool $auto_format_number = true)`

at line 1959
`bool executeMysqlDump(string $filename)`

at line 1932
`executeMysqlRestore(string $filename)`

at line 1473
`array extract_search_term(string $search_term)`

at line 1922
`static string|string[]|null formatQueryForSinglelineOutput($Query)`

at line 170
`int|null getMariaDBServerVersion()`

at line 151
`int getMySQLServerVersion()`

at line 1864
`string get_charset()`

at line 1599
`array get_database_status()`

at line 1856
`mysqli get_link()`

at line 372
`int get_query_count()`

at line 136
`array get_server_vars()`

at line 1358
`array|bool get_table_structure(string $table_name)`

at line 1897
`bool hasIntDisplayWidthSupport()`

at line 1880
`bool hasJsonSupport()`

at line 1872
`bool hasUtf8mb4Support()`

at line 1888
`bool hasUuidBinSupport()`

at line 1115
`bool|mixed insert(string $table, array $values)`

at line 188
`bool|null isMariaDB()`

at line 179
`bool is_cluster()`

at line 487
`mixed lpquery($query, $params, string $file = FILE, int $line = LINE, int $cache_ttl = 0, null $cache_group = null)`

at line 390
`mixed|null lquery($query, string $result_type = "mysql_fetch_array", string $file = FILE, int $line = LINE, bool $cache_ttl = false, bool $cache_group = false, bool $query_type = false)`

at line 979
`true|array multiQuery(array $queries, bool $returnResults = false, bool $useTransaction = true)`

at line 539
`mixed pquery(string $query, array $params = array(), string $file = FILE, int|string $line = LINE, int $cache_ttl = 0, null $cache_group = null)`

at line 1588
`prepare_debug_profiling()`

at line 763
`false|int|array|mysqli_result query(string $query, string $result_type = "mysql_fetch_array", string $file = FILE, int $line = LINE, int $cache_ttl = null, string $cache_group = null, string $query_type = null)`

at line 741
`mixed query_with_params(string $query, array $params, string $result_type = "mysql_fetch_array", string $file = FILE, int $line = LINE, int $cache_ttl = null, string $cache_group = null, string $query_type = null)`

at line 519
`void rclose()`

at line 1909
`static string|string[]|null removeSensitiveData($Query)`

at line 1178
`null|string|string[] replace_query_placeholder(string $query, array $vars, string $format = 'value', array $value_list = [])`

at line 1848
`bool rollback()`

at line 503
`bool|mysqli_result rquery(string $query)`

at line 1226
`static bool secure_query(string $query, int $allowed = XF_QUERY_ALLOW_NONE)`

at line 1818
`bool set_autocommit(bool $mode)`

at line 334
`void set_timezone(string $timezone)`

at line 1325
`bool table_exists(string $table_name)`

at line 1344
`bool table_field_exists(string $table, string $field)`

at line 1134
`bool|mixed update(string $table, array $values, array $where, int $limit = 1)`