interface Connection (View source)

Interface for database connections that provide basic query functionality to an SQL database.

Constants

ORDER_ASCENDING

Ascending sorting order for select queries.

ORDER_DESCENDING

Descending sorting order for select queries.

Methods

PDO
getConnection()

Returns the underlying PDO connection to the database.

insert(string $table, array $values, string|null $primaryKey = null)

Inserts a row the database with given column values.

select(array $fields, string $table, array $where, array $orderBy = [], int $limit = null)

Selects rows from the database.

update(string $table, array $values, array $where)

Updates rows in the database.

delete(string $table, array $where)

Deletes rows from the database.

query(string $sql, array $parameters = [])

Executes an arbitrary SQL query in the database.

string
formatTable(string $table, string $alias = '')

Returns an escaped table name aliased to the given alias.

string
formatFields(array $fields, string $table = '', string $prefix = '')

Returns a comma separated list of escaped field names from given table aliased with given prefix.

Details

PDO getConnection()

Returns the underlying PDO connection to the database.

Return Value

PDO The underlying PDO connection to the database

PDOStatement insert(string $table, array $values, string|null $primaryKey = null)

Inserts a row the database with given column values.

Parameters

string $table Name of the table to insert
array $values Associative array of column values for the insert
string|null $primaryKey Name of automatic primary key column that will be overwritten with its value

Return Value

PDOStatement The resulting PDO statement after executing the query

PDOStatement select(array $fields, string $table, array $where, array $orderBy = [], int $limit = null)

Selects rows from the database.

The conditions provided to the query must be an associative array of columns and expected values. Any scalar value and null are supported for equal comparison. If an array is provided as an value for the column, then the value must by any of the provided values in the array, i.e. an "IN" SQL comparison. Null is also supported as one of the values in the array.

The sorting order provides list of columns to sort by in order of importance with the appropriate sorting direction. Note that the limit argument is ignore if no sorting columns have been provided, as the order of rows is undefined.

Parameters

array $fields List of fields to select from the database
string $table The name of the table to select
array $where Conditions for the select query
array $orderBy Associative array of columns to sort by and the sorting direction for each column
int $limit Maximum number of rows to return on sorted statement

Return Value

PDOStatement The resulting PDO statement after executing the query

PDOStatement update(string $table, array $values, array $where)

Updates rows in the database.

Parameters

string $table The table to update
array $values Associative array of columns and values to update
array $where Conditions for the updated row, similar to select queries

Return Value

PDOStatement The resulting PDO statement after executing the query

PDOStatement delete(string $table, array $where)

Deletes rows from the database.

Parameters

string $table The table where to delete rows
array $where Conditions for the delete query, similar to select query

Return Value

PDOStatement The resulting PDO statement after executing the query

PDOStatement query(string $sql, array $parameters = [])

Executes an arbitrary SQL query in the database.

Parameters

string $sql The SQL query to execute
array $parameters Array of parameters to pass to the query

Return Value

PDOStatement The resulting PDO statement after executing the query

string formatTable(string $table, string $alias = '')

Returns an escaped table name aliased to the given alias.

Parameters

string $table The name of the table to escape
string $alias The alias for the table or empty string for no alias

Return Value

string The escaped table name for an SQL query

string formatFields(array $fields, string $table = '', string $prefix = '')

Returns a comma separated list of escaped field names from given table aliased with given prefix.

Parameters

array $fields List of fields to escape
string $table The name or alias of the table for the fields
string $prefix Prefix to use for each field in the alias

Return Value

string Comma separated list of escaped fields