Db

Type

Class

Namespace

craft\helpers

Inherits

craft\helpers\Db

Since

3.0.0

Class Db

View source (opens new window)

Public Methods

Method	Description
areColumnTypesCompatible()	Returns whether two column type definitions are relatively compatible with each other.
batchInsert()	Creates and executes a batch INSERT SQL statement.
delete()	Creates and executes a DELETE SQL statement.
deleteIfExists()	Creates and executes a DELETE SQL statement, but only if there are any rows to delete, avoiding deadlock issues when deleting data from large tables.
escapeParam()	Escapes commas and asterisks in a string so they are not treated as special characters in craft\helpers\Db::parseParam().
getMaxAllowedValueForNumericColumn()	Returns the maximum number allowed for a given column type.
getMinAllowedValueForNumericColumn()	Returns the minimum number allowed for a given column type.
getNumericalColumnType()	Returns a number column type, taking the min, max, and number of decimal points into account.
getSimplifiedColumnType()	Returns a simplified version of a given column type.
getTextualColumnStorageCapacity()	Returns the maximum number of bytes a given textual column type can hold for a given database.
getTextualColumnTypeByContentLength()	Given a length of a piece of content, returns the underlying database column type to use for saving.
idByUid()	Returns the id of a row in the given table by its uid.
idsByUids()	Returns an array uid:id pairs from a given table, by their uids.
insert()	Creates and executes an INSERT SQL statement.
isNumericColumnType()	Returns whether the given column type is numeric.
isTextualColumnType()	Returns whether the given column type is textual.
isTypeSupported()	Returns whether a given DB connection’s schema supports a column type.
parseBooleanParam()	Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.
parseColumnLength()	Parses a column type definition and returns just the column length/size.
parseColumnType()	Parses a column type definition and returns just the column type, if it can be determined.
parseDateParam()	Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.
parseDsn()	Parses a DSN string and returns an array with the driver and any driver params, or just a single key.
parseParam()	Parses a query param value and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.
prepareDateForDb()	Prepares a date to be sent to the database.
prepareValueForDb()	Prepares a value to be sent to the database.
prepareValuesForDb()	Prepares an array or object’s values to be sent to the database.
replace()	Creates and executes a SQL statement for replacing some text with other text in a given table column.
reset()	Resets the memoized database connection.
uidById()	Returns the uid of a row in the given table by its id.
uidsByIds()	Returns an array id:uid pairs from a given table, by their ids.
update()	Creates and executes an UPDATE SQL statement.
upsert()	Creates and executes a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.
url2config()	Generates a DB config from a database connection URL.

areColumnTypesCompatible()

Returns whether two column type definitions are relatively compatible with each other.

View source (opens new window)

Arguments

• $typeA (string (opens new window))
• $typeB (string (opens new window))

Returns

boolean (opens new window)

batchInsert()

Since

3.5.0

Creates and executes a batch INSERT SQL statement.

The method will properly escape the column names, and bind the values to be inserted.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table that new rows will be inserted into
• $columns (array (opens new window)) – The column names
• $rows (array (opens new window)) – The rows to be batch inserted into the table
• $includeAuditColumns (boolean (opens new window)) – Whether dateCreated, dateUpdated, and uid values should be added to $columns
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

delete()

Since

3.5.0

Creates and executes a DELETE SQL statement.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table where the data will be deleted from
• $condition (array (opens new window), string (opens new window)) – The conditions that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify conditions.
• $params (array (opens new window)) – The parameters to be bound to the query.
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

deleteIfExists()

Since

3.0.12

Creates and executes a DELETE SQL statement, but only if there are any rows to delete, avoiding deadlock issues when deleting data from large tables.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table where the data will be deleted from
• $condition (string (opens new window), array (opens new window)) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition.
• $params (array (opens new window)) – The parameters to be bound to the command
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – Number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  execution failed

escapeParam()

Escapes commas and asterisks in a string so they are not treated as special characters in craft\helpers\Db::parseParam().

View source (opens new window)

Arguments

• $value (string (opens new window)) – The param value.

Returns

string (opens new window) – The escaped param value.

getMaxAllowedValueForNumericColumn()

Returns the maximum number allowed for a given column type.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

integer (opens new window), false (opens new window) – The max allowed number, or false if it can't be determined

getMinAllowedValueForNumericColumn()

Returns the minimum number allowed for a given column type.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

integer (opens new window), false (opens new window) – The min allowed number, or false if it can't be determined

getNumericalColumnType()

Returns a number column type, taking the min, max, and number of decimal points into account.

View source (opens new window)

Arguments

• $min (integer (opens new window), null (opens new window))
• $max (integer (opens new window), null (opens new window))
• $decimals (integer (opens new window), null (opens new window))

Returns

string (opens new window)

Throws

• yii\base\Exception (opens new window)
  if no column types can contain this

getSimplifiedColumnType()

Returns a simplified version of a given column type.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

string (opens new window)

getTextualColumnStorageCapacity()

Returns the maximum number of bytes a given textual column type can hold for a given database.

View source (opens new window)

Arguments

• $columnType (string (opens new window)) – The textual column type to check
• $db (craft\db\Connection, null (opens new window)) – The database connection

Returns

integer (opens new window), null (opens new window), false (opens new window) – The storage capacity of the column type in bytes, null if unlimited, or false or it can't be determined.

getTextualColumnTypeByContentLength()

Given a length of a piece of content, returns the underlying database column type to use for saving.

View source (opens new window)

Arguments

• $contentLength (integer (opens new window))
• $db (craft\db\Connection, null (opens new window)) – The database connection

Returns

string (opens new window)

Throws

• yii\base\Exception (opens new window)
  if using an unsupported connection type

idByUid()

Since

3.1.0

Returns the id of a row in the given table by its uid.

View source (opens new window)

Arguments

• $table (string (opens new window))
• $uid (string (opens new window))
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window), null (opens new window)

idsByUids()

Since

3.1.0

Returns an array uid:id pairs from a given table, by their uids.

View source (opens new window)

Arguments

• $table (string (opens new window))
• $uids (string (opens new window)[])
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

string (opens new window)[]

insert()

Since

3.5.0

Creates and executes an INSERT SQL statement.

The method will properly escape the column names, and bind the values to be inserted.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table that new rows will be inserted into
• $columns (array (opens new window)) – The column data (name=>value) to be inserted into the table
• $includeAuditColumns (boolean (opens new window)) – Whether to include the data for the audit columns (dateCreated, dateUpdated, and uid)
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

isNumericColumnType()

Returns whether the given column type is numeric.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

boolean (opens new window)

isTextualColumnType()

Returns whether the given column type is textual.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

boolean (opens new window)

isTypeSupported()

Returns whether a given DB connection’s schema supports a column type.

View source (opens new window)

Arguments

• $type (string (opens new window))
• $db (craft\db\Connection, null (opens new window))

Returns

boolean (opens new window)

Throws

• yii\base\NotSupportedException (opens new window)

parseBooleanParam()

Since

3.4.15

Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.

The follow values are supported:

• true or false
• :empty: or :notempty: (normalizes to false and true)
• 'not x' or '!= x' (normalizes to the opposite of the boolean value of x)
• Anything else (normalizes to the boolean value of itself)

If $defaultValue is set, and it matches the normalized $value, then the resulting condition will match any null values as well.

View source (opens new window)

Arguments

• $column (string (opens new window)) – The database column that the param is targeting.
• $value (string (opens new window), boolean (opens new window)) – The param value
• $defaultValue (boolean (opens new window), null (opens new window)) – How null values should be treated

Returns

mixed

parseColumnLength()

Parses a column type definition and returns just the column length/size.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

integer (opens new window), null (opens new window)

parseColumnType()

Parses a column type definition and returns just the column type, if it can be determined.

View source (opens new window)

Arguments

• $columnType (string (opens new window))

Returns

string (opens new window), null (opens new window)

parseDateParam()

Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.

View source (opens new window)

Arguments

• $column (string (opens new window)) – The database column that the param is targeting.
• $value (string (opens new window), array (opens new window), DateTime (opens new window)) – The param value
• $defaultOperator (string (opens new window)) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)

Returns

mixed

parseDsn()

Since

3.4.0

Parses a DSN string and returns an array with the driver and any driver params, or just a single key.

View source (opens new window)

Arguments

• $dsn (string (opens new window))
• $key (string (opens new window), null (opens new window)) – The key that is needed from the DSN. If this is

Returns

array (opens new window), string (opens new window), false (opens new window) – The full array, or the specific key value, or false if $key is a param that doesn’t exist in the DSN string.

Throws

• yii\base\InvalidArgumentException (opens new window)
  if $dsn is invalid

parseParam()

Parses a query param value and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.

If the $value is a string, it will automatically be converted to an array, split on any commas within the string (via craft\helpers\ArrayHelper::toArray()). If that is not desired behavior, you can escape the comma with a backslash before it.

The first value can be set to either 'and' or 'or' to define whether all of the values must match, or any. If it’s neither 'and' nor 'or', then 'or' will be assumed. Values can begin with the operators 'not ', '!=', '<=', '>=', '<', '>', or '='. If they don’t, '=' will be assumed.

Values can also be set to either ':empty:' or ':notempty:' if you want to search for empty or non-empty database values. (An “empty” value is either NULL or an empty string of text).

View source (opens new window)

Arguments

• $column (string (opens new window)) – The database column that the param is targeting.
• $value (string (opens new window), integer (opens new window), array (opens new window)) – The param value(s).
• $defaultOperator (string (opens new window)) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)
• $caseInsensitive (boolean (opens new window)) – Whether the resulting condition should be case-insensitive
• $columnType (string (opens new window), null (opens new window)) – The database column type the param is targeting

Returns

mixed

prepareDateForDb()

Prepares a date to be sent to the database.

View source (opens new window)

Arguments

• $date (mixed) – The date to be prepared
• $stripSeconds (boolean (opens new window)) – Whether the seconds should be omitted from the formatted string

Returns

string (opens new window), null (opens new window) – The prepped date, or null if it could not be prepared

prepareValueForDb()

Prepares a value to be sent to the database.

View source (opens new window)

Arguments

• $value (mixed) – The value to be prepared

Returns

mixed – The prepped value

prepareValuesForDb()

Prepares an array or object’s values to be sent to the database.

View source (opens new window)

Arguments

• $values (mixed) – The values to be prepared

Returns

array (opens new window) – The prepared values

replace()

Since

3.5.0

Creates and executes a SQL statement for replacing some text with other text in a given table column.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table to be updated
• $column (string (opens new window)) – The column to be searched
• $find (string (opens new window)) – The text to be searched for
• $replace (string (opens new window)) – The replacement text
• $condition (string (opens new window), array (opens new window)) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition.
• $params (array (opens new window)) – The parameters to be bound to the command
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

reset()

Since

3.5.12.1

Resets the memoized database connection.

View source (opens new window)

uidById()

Since

3.1.0

Returns the uid of a row in the given table by its id.

View source (opens new window)

Arguments

• $table (string (opens new window))
• $id (integer (opens new window))
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

string (opens new window), null (opens new window)

uidsByIds()

Since

3.1.0

Returns an array id:uid pairs from a given table, by their ids.

View source (opens new window)

Arguments

• $table (string (opens new window))
• $ids (integer (opens new window)[])
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

string (opens new window)[]

update()

Since

3.5.0

Creates and executes an UPDATE SQL statement.

The method will properly escape the column names and bind the values to be updated.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table to be updated
• $columns (array (opens new window)) – The column data (name => value) to be updated
• $condition (string (opens new window), array (opens new window)) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition
• $params (array (opens new window)) – The parameters to be bound to the command
• $includeAuditColumns (boolean (opens new window)) – Whether the dateUpdated value should be added to $columns
• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

upsert()

Since

3.5.0

Creates and executes a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.

The method will properly escape the column names, and bind the values to be inserted.

View source (opens new window)

Arguments

• $table (string (opens new window)) – The table that new rows will be inserted into/updated in

• $insertColumns (array (opens new window), craft\db\Query) – The column data (name => value) to be inserted into the table or instance of craft\db\Query to perform INSERT INTO ... SELECT SQL statement

• $updateColumns (array (opens new window), boolean (opens new window)) – The column data (name => value) to be updated if they already exist

• If true is passed, the column data will be updated to match the insert column data.

• If false is passed, no update will be performed if the column data already exists.

• $params (array (opens new window)) – The parameters to be bound to the command

• $includeAuditColumns (boolean (opens new window)) – Whether dateCreated, dateUpdated, and uid values should be added to $columns

• $db (craft\db\Connection, null (opens new window)) – The database connection to use

Returns

integer (opens new window) – The number of rows affected by the execution

Throws

• yii\db\Exception (opens new window)
  if execution failed

url2config()

Since

3.4.0

Generates a DB config from a database connection URL.

This can be used from config/db.php:

View source (opens new window)

Arguments

• $url (string (opens new window))

Returns

array (opens new window)

Example

• PHP

$url = craft\helpers\App::env('DB_URL');
return craft\helpers\Db::url2config($url);

Constants

Constant	Description
SIMPLE_TYPE_NUMERIC
SIMPLE_TYPE_TEXTUAL