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.

CONNECT_USER

User connection.

CONNECT_CONTROL

Control user connection.

CONNECT_AUXILIARY

Auxiliary connection.

Used for example for replication setup.

Properties

Types $types
float $lastQueryExecutionTime

Methods

__construct(DbiExtension $ext)

No description

query(string $query, mixed $link = self::CONNECT_USER, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query

getCache()

No description

mixed
tryQuery(string $query, mixed $link = self::CONNECT_USER, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query and returns the result

bool
tryMultiQuery(string $multiQuery = '', int $linkIndex = self::CONNECT_USER)

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

queryAsControlUser(string $sql)

Executes a query as controluser.

tryQueryAsControlUser(string $sql)

Executes a query as controluser.

array
getTables(string $database, mixed $link = self::CONNECT_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, mixed $link = self::CONNECT_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
getVirtualTables(string $db)

Get VIEWs in a particular database

array
getDatabasesFull(string|null $database = null, bool $forceStats = false, int $link = self::CONNECT_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
getColumnMapFromSql(string $sqlQuery, array $viewColumns = [])

returns detailed array with all columns for sql

array
getColumnsFull(string|null $database = null, string|null $table = null, string|null $column = null, mixed $link = self::CONNECT_USER)

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

array
getColumn(string $database, string $table, string $column, bool $full = false, int $link = self::CONNECT_USER)

Returns description of a $column in given table

array
getColumns(string $database, string $table, bool $full = false, int $link = self::CONNECT_USER)

Returns descriptions of columns in given table

array
getColumnNames(string $database, string $table, mixed $link = self::CONNECT_USER)

Returns all column names in given table

array
getTableIndexes(string $database, string $table, mixed $link = self::CONNECT_USER)

Returns indexes of a table

false|string|null
getVariable(string $var, int $type = self::GETVAR_SESSION, int $link = self::CONNECT_USER)

returns value of given mysql server variable

bool
setVariable(string $var, string $value, int $link = self::CONNECT_USER)

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

void
postConnect()

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

void
postConnectControl(Relation $relation)

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.

string|false|null
fetchValue(string $query, int|string $field = 0, int $link = self::CONNECT_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, int $link = self::CONNECT_USER)

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

array
fetchResult(string $query, string|int|array $key = null, string|int $value = null, int $link = self::CONNECT_USER)

returns all rows in the resultset in one array

array
getCompatibilities()

Get supported SQL compatibility modes

array
getWarnings(int $link = self::CONNECT_USER)

returns warnings for last query

array
getProceduresOrFunctions(string $db, string $which, int $link = self::CONNECT_USER)

returns an array of PROCEDURE or FUNCTION names for a db

string|null
getDefinition(string $db, string $which, string $name, int $link = self::CONNECT_USER)

returns the definition of a specific PROCEDURE, FUNCTION, EVENT or VIEW

array
getRoutines(string $db, string|null $which = null, string $name = '')

returns details about the PROCEDUREs or FUNCTIONs for a specific database or details about a specific routine

array
getEvents(string $db, string $name = '')

returns details about the EVENTs for a specific database

array
getTriggers(string $db, string $table = '', string $delimiter = '//')

returns details about the TRIGGERs for a specific table or database

string
getCurrentUser()

gets the current user with host

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

string
getLowerCaseNames()

Returns value for lower_case_table_names variable

mixed
connect(int $mode, array|null $server = null, int|null $target = null)

connects to the database server

bool
selectDb(string|DatabaseName $dbname, int $link = self::CONNECT_USER)

selects given database

bool
moreResults(int $link = self::CONNECT_USER)

Check if there are any more query results from a multi query

bool
nextResult(int $link = self::CONNECT_USER)

Prepare next result from multi_query

mixed
storeResult(int $link = self::CONNECT_USER)

Store the result returned from multi query

string|bool
getHostInfo(int $link = self::CONNECT_USER)

Returns a string representing the type of connection used

int|bool
getProtoInfo(int $link = self::CONNECT_USER)

Returns the version of the MySQL protocol used

string
getClientInfo()

returns a string that represents the client library version

string
getError(int $link = self::CONNECT_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(int $link = self::CONNECT_USER)

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

int|string
affectedRows(int $link = self::CONNECT_USER, bool $getFromCache = true)

returns the number of rows affected by last query

array
getFieldsMeta(ResultInterface $result)

returns metainfo for fields in $result

string
escapeString(string $str, mixed $link = self::CONNECT_USER)

returns properly escaped string for use in MySQL queries

string
escapeMysqlLikeString(string $str, int $link = self::CONNECT_USER)

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.

getSystemDatabase()

Get the phpmyadmin database manager

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

load(DbiExtension|null $extension = null)

Load correct database driver

object|false
prepare(string $query, int $link = self::CONNECT_USER)

Prepare an SQL statement for execution.

Details

__construct(DbiExtension $ext)

No description

Parameters

DbiExtension $ext

Object to be used for database queries

ResultInterface query(string $query, mixed $link = self::CONNECT_USER, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query

Parameters

string $query

SQL query to execute

mixed $link

optional database link to use

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, mixed $link = self::CONNECT_USER, int $options = self::QUERY_BUFFERED, bool $cacheAffectedRows = true)

runs a query and returns the result

Parameters

string $query

query to run

mixed $link

link type

int $options

query options

bool $cacheAffectedRows

whether to cache affected row

Return Value

mixed

bool tryMultiQuery(string $multiQuery = '', int $linkIndex = self::CONNECT_USER)

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

Parameters

string $multiQuery

multi query statement to execute

int $linkIndex

index of the opened database link

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, mixed $link = self::CONNECT_USER)

returns array with table names for given db

Parameters

string $database

name of database

mixed $link

mysql link resource|object

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, mixed $link = self::CONNECT_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

mixed $link

link type

Return Value

array

list of tables in given db(s)

array getVirtualTables(string $db)

Get VIEWs in a particular database

Parameters

string $db

Database name to look in

Return Value

array

Set of VIEWs inside the database

array getDatabasesFull(string|null $database = null, bool $forceStats = false, int $link = self::CONNECT_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

int $link

link type

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 $GLOBALS['cfg']['MaxDbList']

Return Value

array

array getColumnMapFromSql(string $sqlQuery, array $viewColumns = [])

returns detailed array with all columns for sql

Parameters

string $sqlQuery

target SQL query to get columns

array $viewColumns

alias for columns

Return Value

array

array getColumnsFull(string|null $database = null, string|null $table = null, string|null $column = null, mixed $link = self::CONNECT_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

mixed $link

mysql link resource

Return Value

array

array getColumn(string $database, string $table, string $column, bool $full = false, int $link = self::CONNECT_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

int $link

link type

Return Value

array

flat array description

array getColumns(string $database, string $table, bool $full = false, int $link = self::CONNECT_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

int $link

link type

Return Value

array

array indexed by column names

array getColumnNames(string $database, string $table, mixed $link = self::CONNECT_USER)

Returns all column names in given table

Parameters

string $database

name of database

string $table

name of table to retrieve columns from

mixed $link

mysql link resource

Return Value

array

array getTableIndexes(string $database, string $table, mixed $link = self::CONNECT_USER)

Returns indexes of a table

Parameters

string $database

name of database

string $table

name of the table whose indexes are to be retrieved

mixed $link

mysql link resource

Return Value

array

false|string|null getVariable(string $var, int $type = self::GETVAR_SESSION, int $link = self::CONNECT_USER)

returns value of given mysql server variable

Parameters

string $var

mysql server variable name

int $type

DatabaseInterface::GETVAR_SESSION | DatabaseInterface::GETVAR_GLOBAL

int $link

mysql link resource|object

Return Value

false|string|null

value for mysql server variable

bool setVariable(string $var, string $value, int $link = self::CONNECT_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

int $link

mysql link resource|object

Return Value

bool

void postConnect()

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.

Return Value

void

void setCollation(string $collation)

Sets collation connection for user link

Parameters

string $collation

collation to set

Return Value

void

void postConnectControl(Relation $relation)

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

Relation $relation

Return Value

void

string|false|null fetchValue(string $query, int|string $field = 0, int $link = self::CONNECT_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

int $link

link type

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, int $link = self::CONNECT_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

int $link

link type

Return Value

array|null

array fetchResult(string $query, string|int|array $key = null, string|int $value = null, int $link = self::CONNECT_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 $key

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

string|int $value

value-name or offset used as value for array

int $link

link type

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(int $link = self::CONNECT_USER)

returns warnings for last query

Parameters

int $link

link type

Return Value

array warnings

array getProceduresOrFunctions(string $db, string $which, int $link = self::CONNECT_USER)

returns an array of PROCEDURE or FUNCTION names for a db

Parameters

string $db

db name

string $which

PROCEDURE | FUNCTION

int $link

link type

Return Value

array

the procedure names or function names

string|null getDefinition(string $db, string $which, string $name, int $link = self::CONNECT_USER)

returns the definition of a specific PROCEDURE, FUNCTION, EVENT or VIEW

Parameters

string $db

db name

string $which

PROCEDURE | FUNCTION | EVENT | VIEW

string $name

the procedure|function|event|view name

int $link

link type

Return Value

string|null

the definition

array getRoutines(string $db, string|null $which = null, string $name = '')

returns details about the PROCEDUREs or FUNCTIONs for a specific database or details about a specific routine

Parameters

string $db

db name

string|null $which

PROCEDURE | FUNCTION or null for both

string $name

name of the routine (to fetch a specific routine)

Return Value

array

information about ROCEDUREs or FUNCTIONs

array getEvents(string $db, string $name = '')

returns details about the EVENTs for a specific database

Parameters

string $db

db name

string $name

event name

Return Value

array

information about EVENTs

array getTriggers(string $db, string $table = '', string $delimiter = '//')

returns details about the TRIGGERs for a specific table or database

Parameters

string $db

db name

string $table

table name

string $delimiter

the delimiter to use (may be empty)

Return Value

array

information about triggers (may be empty)

string getCurrentUser()

gets the current user with host

Return Value

string

the current user i.e. user@host

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

string getLowerCaseNames()

Returns value for lower_case_table_names variable

Return Value

string

mixed connect(int $mode, array|null $server = null, int|null $target = null)

connects to the database server

Parameters

int $mode

Connection mode on of CONNECT_USER, CONNECT_CONTROL or CONNECT_AUXILIARY.

array|null $server

Server information like host/port/socket/persistent

int|null $target

How to store connection link, defaults to $mode

Return Value

mixed

false on error or a connection object on success

bool selectDb(string|DatabaseName $dbname, int $link = self::CONNECT_USER)

selects given database

Parameters

string|DatabaseName $dbname

database name to select

int $link

link type

Return Value

bool

bool moreResults(int $link = self::CONNECT_USER)

Check if there are any more query results from a multi query

Parameters

int $link

link type

Return Value

bool

bool nextResult(int $link = self::CONNECT_USER)

Prepare next result from multi_query

Parameters

int $link

link type

Return Value

bool

mixed storeResult(int $link = self::CONNECT_USER)

Store the result returned from multi query

Parameters

int $link

link type

Return Value

mixed

false when empty results / result set when not empty

string|bool getHostInfo(int $link = self::CONNECT_USER)

Returns a string representing the type of connection used

Parameters

int $link

link type

Return Value

string|bool

type of connection used

int|bool getProtoInfo(int $link = self::CONNECT_USER)

Returns the version of the MySQL protocol used

Parameters

int $link

link type

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(int $link = self::CONNECT_USER)

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

Parameters

int $link

link type

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(int $link = self::CONNECT_USER)

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

Parameters

int $link

link type

Return Value

int

int|string affectedRows(int $link = self::CONNECT_USER, bool $getFromCache = true)

returns the number of rows affected by last query

Parameters

int $link

link type

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 escapeString(string $str, mixed $link = self::CONNECT_USER)

returns properly escaped string for use in MySQL queries

Parameters

string $str

string to be escaped

mixed $link

optional database link to use

Return Value

string

a MySQL escaped string

string escapeMysqlLikeString(string $str, int $link = self::CONNECT_USER)

returns properly escaped string for use in MySQL LIKE clauses

Parameters

string $str

string to be escaped

int $link

optional database link to use

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

SystemDatabase getSystemDatabase()

Get the phpmyadmin database manager

Return Value

SystemDatabase

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

static DatabaseInterface load(DbiExtension|null $extension = null)

Load correct database driver

Parameters

DbiExtension|null $extension

Force the use of an alternative extension

Return Value

DatabaseInterface

object|false prepare(string $query, int $link = self::CONNECT_USER)

Prepare an SQL statement for execution.

Parameters

string $query

The query, as a string.

int $link

Link type.

Return Value

object|false

A statement object or false.