class MySQLAdapter

Class MySQLAdapter

Properties

int $server_version
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
$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 $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)

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($string, bool $auto_format_number = true)

No description

void
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

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

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

bool|mixed
insert(string $table, array $values)

Inserts the passed values into the given table

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 89
__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

at line 294
void __destruct()

Disconnect Server in case of object destruction

Return Value

void

at line 284
void __sleep()

PHP __sleep -> Disconnect Server in case of object serialization

Return Value

void

at line 303
void __wakeup()

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

Return Value

void

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

No description

Parameters

string $type

Type of query [insert, replace, select, update]

string $table

Table Name

array $values

1-dim Array with field names (select) or 2-dim Array with field names and their field values

array $params

2-dim Array with additional options $params['table_structure'] --> Pass table structure array to perform additional auto-transformation based on field information $params['where'] --> Where condition, e.g. where=>'id=1' $params['limit'] --> Limit condition, e.g. limit=>'1,10' $params['order_by'] --> Order condition, e.g. order_by='id ASC'

string $FILE
int $LINE
bool $query_type

Return Value

mixed

Returns DB->query result

Exceptions

DatabaseException

at line 1728
void begin_transaction()

No description

Return Value

void

at line 157
bool|null can_create_tmp_tables()

Can the current user create temporary tables?

Return Value

bool|null

at line 631
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

Parameters

string $query

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

Return Value

string

SQL Query String

at line 643
string check_query_cb(array $matches)

put your comment there.

..

Parameters

array $matches

SQL Query String

Return Value

string

SQL Query String

at line 1230
bool close()

Close DB Connection

Return Value

bool

errno

at line 1430
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 1738
bool commit()

No description

Return Value

bool

Commit Status

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

Connects to the database

Parameters

string $charset

Return Value

null

Exceptions

DatabaseConnectionException

at line 1707
void debug()

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

Return Value

void

at line 1219
int errno()

Returns the error code for the most recent function call

Return Value

int

errno

at line 1209
string error()

Returns a string description of the last error

Return Value

string

error

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

No description

Parameters

$string
bool $auto_format_number

If enabled float values will be converted to the correct mySQL syntax

Return Value

string

escaped string

at line 1834
void executeMysqlDump(string $filename)

Take a mysqldump backup file

Parameters

string $filename

Return Value

void

Exceptions

RuntimeException

at line 1808
executeMysqlRestore(string $filename)

Restore mysql database from a mysqldump backup file

Parameters

string $filename

at line 1360
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 1798
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

Parameters

$Query

string SQL Query

Return Value

string|string[]|null

at line 1765
string get_charset()

No description

Return Value

string

Get the current Charset

at line 1485
array get_database_status()

Returns the "SHOW STATUS" result in an array

Return Value

array

Returns the mysqli link object

Return Value

mysqli

at line 312
int get_query_count()

Returns the number of executed queries since connecting the database server

Return Value

int

at line 135
array get_server_vars()

Server Variables

Return Value

array

Exceptions

Exception

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

No description

Parameters

string $table_name

Return Value

array|bool

multi-dim array with table structure

at line 1773
bool has_utf8mb4_support()

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

Return Value

bool

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

Inserts the passed values into the given table

Parameters

string $table
array $values

Assoc-Array, e.g. array('fieldname'=>'fieldvalue', ....)

Return Value

bool|mixed

at line 148
bool is_cluster()

Is Database a Galera Cluster?

Return Value

bool

Exceptions

Exception

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

Execute PQuery Wrapper for Languages

Parameters

$query
$params
string $file
int $line
int $cache_ttl
null $cache_group

Return Value

mixed

Exceptions

DatabaseException

at line 330
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

Parameters

$query
string $result_type
string $file
int $line
bool $cache_ttl
bool $cache_group
bool $query_type

Return Value

mixed|null

Exceptions

DatabaseException

at line 909
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.

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

Return Value

true|array

If returnResults is true all results are being returned as array

Exceptions

DatabaseException

at line 479
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 1474
prepare_debug_profiling()

Prepare Profiling Infos from Database if in DEVMODE

at line 703
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 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

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 681
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

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 459
void rclose()

Free/Close Result Query (Use after rquery)

Return Value

void

at line 1785
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 1108
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 1749
bool rollback()

No description

Return Value

bool

Rollback Status

at line 443
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 1150
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 1719
bool set_autocommit(bool $mode)

No description

Parameters

bool $mode

enable or disable autocommit

Return Value

bool

success

at line 274
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 1249
bool table_exists(string $table_name)

No description

Parameters

string $table_name

Return Value

bool

at line 1268
bool table_field_exists(string $table, string $field)

No description

Parameters

string $table
string $field

Return Value

bool

at line 1064
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