class DatabaseInterface implements DbalInterface (View source)

Main interface for database interactions

Constants

QUERY_BUFFERED

Force STORE_RESULT method, ignored by classic MySQL.

QUERY_UNBUFFERED

Do not read all rows immediately.

GETVAR_SESSION

Get session variable.

GETVAR_GLOBAL

Get global variable.

Properties

static self|null $instance
Types $types
float $lastQueryExecutionTime

Methods

__construct(DbiExtension $extension)

No description

getInstance() deprecated

No description

query(string $query, ConnectionType $connectionType = ConnectionType::User, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query

getCache()

No description

mixed
tryQuery(string $query, ConnectionType $connectionType = ConnectionType::User, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query and returns the result

bool
tryMultiQuery(string $multiQuery = '', ConnectionType $connectionType = ConnectionType::User)

Send multiple SQL queries to the database server and execute the first one

queryAsControlUser(string $sql)

Executes a query as controluser.

ResultInterface|false
tryQueryAsControlUser(string $sql)

Executes a query as controluser.

array
getTables(string $database, ConnectionType $connectionType = ConnectionType::User)

returns array with table names for given db

array
getTablesFull(string $database, string|array $table = '', bool $tableIsGroup = false, int $limitOffset = 0, bool|int $limitCount = false, string $sortBy = 'Name', string $sortOrder = 'ASC', string|null $tableType = null, ConnectionType $connectionType = ConnectionType::User)

returns array of all tables in given db or dbs this function expects unquoted names: RIGHT: my_database WRONG: my_database WRONG: my_database if $tbl_is_group is true, $table is used as filter for table names

array
getDatabasesFull(string|null $database = null, bool $forceStats = false, ConnectionType $connectionType = ConnectionType::User, string $sortBy = 'SCHEMA_NAME', string $sortOrder = 'ASC', int $limitOffset = 0, bool|int $limitCount = false)

returns array with databases containing extended infos about them

array
getColumnsFull(string|null $database = null, string|null $table = null, string|null $column = null, ConnectionType $connectionType = ConnectionType::User)

returns detailed array with all columns for given table in database, or all tables/databases

Column|null
getColumn(string $database, string $table, string $column, bool $full = false, ConnectionType $connectionType = ConnectionType::User)

Returns description of a $column in given table

array
getColumns(string $database, string $table, bool $full = false, ConnectionType $connectionType = ConnectionType::User)

Returns descriptions of columns in given table

array
getColumnNames(string $database, string $table, ConnectionType $connectionType = ConnectionType::User)

Returns all column names in given table

array
getTableIndexes(string $database, string $table, ConnectionType $connectionType = ConnectionType::User)

Returns indexes of a table

false|string|null
getVariable(string $var, int $type = self::GETVAR_SESSION, ConnectionType $connectionType = ConnectionType::User)

returns value of given mysql server variable

bool
setVariable(string $var, string $value, ConnectionType $connectionType = ConnectionType::User)

Sets new value for a variable if it is different from the current value

string
getDefaultCharset()

No description

string
getDefaultCollation()

No description

void
postConnect(Server $currentServer)

Function called just after a connection to the MySQL database server has been established. It sets the connection collation, and determines the version of MySQL which is running.

void
setCollation(string $collation)

Sets collation connection for user link

string|false|null
fetchValue(string $query, int|string $field = 0, ConnectionType $connectionType = ConnectionType::User)

returns a single value from the given result or query, if the query or the result has more than one row or field the first field of the first row is returned

array|null
fetchSingleRow(string $query, string $type = DbalInterface::FETCH_ASSOC, ConnectionType $connectionType = ConnectionType::User)

Returns only the first row from the result or null if result is empty.

array
fetchResult(string $query, string|int|array|null $key = null, string|int|null $value = null, ConnectionType $connectionType = ConnectionType::User)

returns all rows in the resultset in one array

array
getCompatibilities()

Get supported SQL compatibility modes

array
getWarnings(ConnectionType $connectionType = ConnectionType::User)

returns warnings for last query

string
getCurrentUser()

gets the current user with host

array
getCurrentRoles()

gets the current role with host. Role maybe multiple separated by comma Support start from MySQL 8.x / MariaDB 10.0.5

bool
isSuperUser()

Checks if current user is superuser

bool
isGrantUser()

No description

bool
isCreateUser()

No description

bool
isConnected()

No description

array
getCurrentUserAndHost()

Get the current user and host

array
getCurrentRolesAndHost()

Get the current role and host.

int
getLowerCaseNames()

Returns value for lower_case_table_names variable

Connection|null
connect(Server $currentServer, ConnectionType $connectionType, ConnectionType|null $target = null)

Connects to the database server.

bool
selectDb(DatabaseName $dbname, ConnectionType $connectionType = ConnectionType::User)

selects given database

ResultInterface|false
nextResult(ConnectionType $connectionType = ConnectionType::User)

Prepare next result from multi_query

string|bool
getHostInfo(ConnectionType $connectionType = ConnectionType::User)

Returns a string representing the type of connection used

int|bool
getProtoInfo(ConnectionType $connectionType = ConnectionType::User)

Returns the version of the MySQL protocol used

string
getClientInfo()

returns a string that represents the client library version

string
getError(ConnectionType $connectionType = ConnectionType::User)

Returns last error message or an empty string if no errors occurred.

string|int
queryAndGetNumRows(string $query)

returns the number of rows returned by last query used with tryQuery as it accepts false

int
insertId(ConnectionType $connectionType = ConnectionType::User)

returns last inserted auto_increment id for given $link or $GLOBALS['userlink']

int|string
affectedRows(ConnectionType $connectionType = ConnectionType::User, bool $getFromCache = true)

returns the number of rows affected by last query

array
getFieldsMeta(ResultInterface $result)

returns metainfo for fields in $result

string
quoteString(string $str, ConnectionType $connectionType = ConnectionType::User)

Returns properly quoted string for use in MySQL queries.

string
escapeMysqlWildcards(string $str)

Returns properly escaped string for use in MySQL LIKE clauses.

bool
isAmazonRds()

Checks if this database server is running on Amazon RDS.

string
getKillQuery(int $process)

Gets SQL for killing a process.

getTable(string $dbName, string $tableName)

Get a table with database name and table name

string
getDbCollation(string $db)

returns collation of given db

string
getServerCollation()

returns default server collation from show variables

int
getVersion()

Server version as number

string
getVersionString()

Server version

string
getVersionComment()

Server version comment

bool
isMariaDB()

Whether connection is MariaDB

bool
isPercona()

Whether connection is PerconaDB

void
setVersion(array $version)

Set version

Statement|null
prepare(string $query, ConnectionType $connectionType = ConnectionType::User)

Prepare an SQL statement for execution.

getDatabaseList()

No description

Details

__construct(DbiExtension $extension)

No description

Parameters

DbiExtension $extension

Object to be used for database queries

static DatabaseInterface getInstance() deprecated

deprecated Use dependency injection instead.

No description

Return Value

DatabaseInterface

ResultInterface query(string $query, ConnectionType $connectionType = ConnectionType::User, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query

Parameters

string $query

SQL query to execute

ConnectionType $connectionType
int $options

optional query options

bool $cacheAffectedRows

whether to cache affected rows

Return Value

ResultInterface

Cache getCache()

No description

Return Value

Cache

mixed tryQuery(string $query, ConnectionType $connectionType = ConnectionType::User, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query and returns the result

Parameters

string $query

query to run

ConnectionType $connectionType
int $options

query options

bool $cacheAffectedRows

whether to cache affected row

Return Value

mixed

bool tryMultiQuery(string $multiQuery = '', ConnectionType $connectionType = ConnectionType::User)

Send multiple SQL queries to the database server and execute the first one

Parameters

string $multiQuery

multi query statement to execute

ConnectionType $connectionType

Return Value

bool

ResultInterface queryAsControlUser(string $sql)

Executes a query as controluser.

The result is always buffered and never cached

Parameters

string $sql

the query to execute

Return Value

ResultInterface

the result set

ResultInterface|false tryQueryAsControlUser(string $sql)

Executes a query as controluser.

The result is always buffered and never cached

Parameters

string $sql

the query to execute

Return Value

ResultInterface|false

the result set, or false if the query failed

array getTables(string $database, ConnectionType $connectionType = ConnectionType::User)

returns array with table names for given db

Parameters

string $database

name of database

ConnectionType $connectionType

Return Value

array

tables names

array getTablesFull(string $database, string|array $table = '', bool $tableIsGroup = false, int $limitOffset = 0, bool|int $limitCount = false, string $sortBy = 'Name', string $sortOrder = 'ASC', string|null $tableType = null, ConnectionType $connectionType = ConnectionType::User)

returns array of all tables in given db or dbs this function expects unquoted names: RIGHT: my_database WRONG: my_database WRONG: my_database if $tbl_is_group is true, $table is used as filter for table names

$dbi->getTablesFull('my_database');
$dbi->getTablesFull('my_database', 'my_table'));
$dbi->getTablesFull('my_database', 'my_tables_', true));

move into Table

Parameters

string $database database
string|array $table

table name(s)

bool $tableIsGroup

$table is a table group

int $limitOffset

zero-based offset for the count

bool|int $limitCount

number of tables to return

string $sortBy

table attribute to sort by

string $sortOrder

direction to sort (ASC or DESC)

string|null $tableType

whether table or view

ConnectionType $connectionType

Return Value

array

list of tables in given db(s)

array getDatabasesFull(string|null $database = null, bool $forceStats = false, ConnectionType $connectionType = ConnectionType::User, string $sortBy = 'SCHEMA_NAME', string $sortOrder = 'ASC', int $limitOffset = 0, bool|int $limitCount = false)

returns array with databases containing extended infos about them

move into ListDatabase?

Parameters

string|null $database database
bool $forceStats

retrieve stats also for MySQL < 5

ConnectionType $connectionType
string $sortBy

column to order by

string $sortOrder

ASC or DESC

int $limitOffset

starting offset for LIMIT

bool|int $limitCount

row count for LIMIT or true for $cfg['MaxDbList']

Return Value

array

array getColumnsFull(string|null $database = null, string|null $table = null, string|null $column = null, ConnectionType $connectionType = ConnectionType::User)

returns detailed array with all columns for given table in database, or all tables/databases

Parameters

string|null $database

name of database

string|null $table

name of table to retrieve columns from

string|null $column

name of specific column

ConnectionType $connectionType

Return Value

array

Column|null getColumn(string $database, string $table, string $column, bool $full = false, ConnectionType $connectionType = ConnectionType::User)

Returns description of a $column in given table

Parameters

string $database

name of database

string $table

name of table to retrieve columns from

string $column

name of column

bool $full

whether to return full info or only column names

ConnectionType $connectionType

Return Value

Column|null

array getColumns(string $database, string $table, bool $full = false, ConnectionType $connectionType = ConnectionType::User)

Returns descriptions of columns in given table

Parameters

string $database

name of database

string $table

name of table to retrieve columns from

bool $full

whether to return full info or only column names

ConnectionType $connectionType

Return Value

array

array getColumnNames(string $database, string $table, ConnectionType $connectionType = ConnectionType::User)

Returns all column names in given table

Parameters

string $database

name of database

string $table

name of table to retrieve columns from

ConnectionType $connectionType

Return Value

array

array getTableIndexes(string $database, string $table, ConnectionType $connectionType = ConnectionType::User)

Returns indexes of a table

Parameters

string $database

name of database

string $table

name of the table whose indexes are to be retrieved

ConnectionType $connectionType

Return Value

array

false|string|null getVariable(string $var, int $type = self::GETVAR_SESSION, ConnectionType $connectionType = ConnectionType::User)

returns value of given mysql server variable

Parameters

string $var

mysql server variable name

int $type

DatabaseInterface::GETVAR_SESSION | DatabaseInterface::GETVAR_GLOBAL

ConnectionType $connectionType

Return Value

false|string|null

value for mysql server variable

bool setVariable(string $var, string $value, ConnectionType $connectionType = ConnectionType::User)

Sets new value for a variable if it is different from the current value

Parameters

string $var

variable name

string $value

value to set

ConnectionType $connectionType

Return Value

bool

string getDefaultCharset()

No description

Return Value

string

string getDefaultCollation()

No description

Return Value

string

void postConnect(Server $currentServer)

Function called just after a connection to the MySQL database server has been established. It sets the connection collation, and determines the version of MySQL which is running.

Parameters

Server $currentServer

Return Value

void

void setCollation(string $collation)

Sets collation connection for user link

Parameters

string $collation

collation to set

Return Value

void

string|false|null fetchValue(string $query, int|string $field = 0, ConnectionType $connectionType = ConnectionType::User)

returns a single value from the given result or query, if the query or the result has more than one row or field the first field of the first row is returned

$sql = 'SELECT `name` FROM `user` WHERE `id` = 123';
$user_name = $dbi->fetchValue($sql);
// produces
// $user_name = 'John Doe'

Parameters

string $query

The query to execute

int|string $field

field to fetch the value from, starting at 0, with 0 being default

ConnectionType $connectionType

Return Value

string|false|null

value of first field in first row from result or false if not found

array|null fetchSingleRow(string $query, string $type = DbalInterface::FETCH_ASSOC, ConnectionType $connectionType = ConnectionType::User)

Returns only the first row from the result or null if result is empty.

$sql = 'SELECT * FROM `user` WHERE `id` = 123';
$user = $dbi->fetchSingleRow($sql);
// produces
// $user = array('id' => 123, 'name' => 'John Doe')

Parameters

string $query

The query to execute

string $type

NUM|ASSOC returned array should either numeric associative or both

ConnectionType $connectionType

Return Value

array|null

array fetchResult(string $query, string|int|array|null $key = null, string|int|null $value = null, ConnectionType $connectionType = ConnectionType::User)

returns all rows in the resultset in one array

$sql = 'SELECT * FROM `user`';
$users = $dbi->fetchResult($sql);
// produces
// $users[] = array('id' => 123, 'name' => 'John Doe')

$sql = 'SELECT `id`, `name` FROM `user`';
$users = $dbi->fetchResult($sql, 'id');
// produces
// $users['123'] = array('id' => 123, 'name' => 'John Doe')

$sql = 'SELECT `id`, `name` FROM `user`';
$users = $dbi->fetchResult($sql, 0);
// produces
// $users['123'] = array(0 => 123, 1 => 'John Doe')

$sql = 'SELECT `id`, `name` FROM `user`';
$users = $dbi->fetchResult($sql, 'id', 'name');
// or
$users = $dbi->fetchResult($sql, 0, 1);
// produces
// $users['123'] = 'John Doe'

$sql = 'SELECT `name` FROM `user`';
$users = $dbi->fetchResult($sql);
// produces
// $users[] = 'John Doe'

$sql = 'SELECT `group`, `name` FROM `user`'
$users = $dbi->fetchResult($sql, array('group', null), 'name');
// produces
// $users['admin'][] = 'John Doe'

$sql = 'SELECT `group`, `name` FROM `user`'
$users = $dbi->fetchResult($sql, array('group', 'name'), 'id');
// produces
// $users['admin']['John Doe'] = '123'

Parameters

string $query

query to execute

string|int|array|null $key

field-name or offset used as key for array or array of those

string|int|null $value

value-name or offset used as value for array

ConnectionType $connectionType

Return Value

array

resultrows or values indexed by $key

array getCompatibilities()

Get supported SQL compatibility modes

Return Value

array

supported SQL compatibility modes

array getWarnings(ConnectionType $connectionType = ConnectionType::User)

returns warnings for last query

Parameters

ConnectionType $connectionType

Return Value

array warnings

string getCurrentUser()

gets the current user with host

Return Value

string

the current user i.e. user@host

array getCurrentRoles()

gets the current role with host. Role maybe multiple separated by comma Support start from MySQL 8.x / MariaDB 10.0.5

bool isSuperUser()

Checks if current user is superuser

Return Value

bool

bool isGrantUser()

No description

Return Value

bool

bool isCreateUser()

No description

Return Value

bool

bool isConnected()

No description

Return Value

bool

array getCurrentUserAndHost()

Get the current user and host

Return Value

array

array of username and hostname

array getCurrentRolesAndHost()

Get the current role and host.

Return Value

array

array of role and hostname

Connection|null connect(Server $currentServer, ConnectionType $connectionType, ConnectionType|null $target = null)

Connects to the database server.

Parameters

Server $currentServer
ConnectionType $connectionType
ConnectionType|null $target

How to store connection link, defaults to $connectionType

Return Value

Connection|null

bool selectDb(DatabaseName $dbname, ConnectionType $connectionType = ConnectionType::User)

selects given database

Parameters

DatabaseName $dbname

database name to select

ConnectionType $connectionType

Return Value

bool

ResultInterface|false nextResult(ConnectionType $connectionType = ConnectionType::User)

Prepare next result from multi_query

Parameters

ConnectionType $connectionType

Return Value

ResultInterface|false

string|bool getHostInfo(ConnectionType $connectionType = ConnectionType::User)

Returns a string representing the type of connection used

Parameters

ConnectionType $connectionType

Return Value

string|bool

type of connection used

int|bool getProtoInfo(ConnectionType $connectionType = ConnectionType::User)

Returns the version of the MySQL protocol used

Parameters

ConnectionType $connectionType

Return Value

int|bool

version of the MySQL protocol used

string getClientInfo()

returns a string that represents the client library version

Return Value

string

MySQL client library version

string getError(ConnectionType $connectionType = ConnectionType::User)

Returns last error message or an empty string if no errors occurred.

Parameters

ConnectionType $connectionType

Return Value

string

string|int queryAndGetNumRows(string $query)

returns the number of rows returned by last query used with tryQuery as it accepts false

Parameters

string $query

query to run

Return Value

string|int

int insertId(ConnectionType $connectionType = ConnectionType::User)

returns last inserted auto_increment id for given $link or $GLOBALS['userlink']

Parameters

ConnectionType $connectionType

Return Value

int

int|string affectedRows(ConnectionType $connectionType = ConnectionType::User, bool $getFromCache = true)

returns the number of rows affected by last query

Parameters

ConnectionType $connectionType
bool $getFromCache

whether to retrieve from cache

Return Value

int|string

array getFieldsMeta(ResultInterface $result)

returns metainfo for fields in $result

Parameters

ResultInterface $result

result set identifier

Return Value

array

meta info for fields in $result

string quoteString(string $str, ConnectionType $connectionType = ConnectionType::User)

Returns properly quoted string for use in MySQL queries.

Parameters

string $str

string to be quoted

ConnectionType $connectionType

Return Value

string

string escapeMysqlWildcards(string $str)

Returns properly escaped string for use in MySQL LIKE clauses.

This method escapes only _, %, and /. It does not escape quotes or any other characters.

Parameters

string $str

string to be escaped

Return Value

string

a MySQL escaped LIKE string

bool isAmazonRds()

Checks if this database server is running on Amazon RDS.

Return Value

bool

string getKillQuery(int $process)

Gets SQL for killing a process.

Parameters

int $process

Process ID

Return Value

string

Table getTable(string $dbName, string $tableName)

Get a table with database name and table name

Parameters

string $dbName

DB name

string $tableName

Table name

Return Value

Table

string getDbCollation(string $db)

returns collation of given db

Parameters

string $db

name of db

Return Value

string

collation of $db

string getServerCollation()

returns default server collation from show variables

Return Value

string

int getVersion()

Server version as number

Return Value

int

Examples

80011

string getVersionString()

Server version

Return Value

string

string getVersionComment()

Server version comment

Return Value

string

bool isMariaDB()

Whether connection is MariaDB

Return Value

bool

bool isPercona()

Whether connection is PerconaDB

Return Value

bool

void setVersion(array $version)

Set version

Parameters

array $version

Database version information

Return Value

void

Statement|null prepare(string $query, ConnectionType $connectionType = ConnectionType::User)

Prepare an SQL statement for execution.

Parameters

string $query

The query, as a string.

ConnectionType $connectionType

Return Value

Statement|null

ListDatabase getDatabaseList()

No description

Return Value

ListDatabase