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. |
batch() | Starts a batch query, similar to yii\db\Query::batch() (opens new window). |
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. |
dropAllForeignKeysToTable() | Drops all the foreign keys that reference a table. |
dropForeignKeyIfExists() | Creates and executes a SQL statement for dropping a foreign key if it exists. |
dropIndexIfExists() | Creates and executes a SQL statement for dropping an index if it exists. |
each() | Starts a batch query and retrieves data row by row. |
escapeCommas() | Escapes commas in a string so the value doesn’t get interpreted as an array by parseParam(). |
escapeForLike() | Escapes underscores within a value for a LIKE condition. |
escapeParam() | Escapes commas, asterisks, and colons in a string, so they are not treated as special characters in parseParam(). |
extractGlue() | Extracts a “glue” param from an a param value. |
findForeignKey() | Looks for a foreign key on the given table with the given columns, and returns its name, or null if no match is found. |
findIndex() | Looks for an index on the given table with the given columns and unique property, and returns its name, or null if no match is found. |
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 uid s. |
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. |
normalizeParam() | Normalizes a param value with a provided resolver function, unless the resolver function ever returns an empty value. |
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. |
parseMoneyParam() | Parses a query param value for a money column, and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition. |
parseNumericParam() | Parses a query param value for a numeric column and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition. |
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. |
prepareMoneyForDb() | Prepares a money object 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. |
rawTableShortName() | Returns a table name with curly brackets and percent sign removed. |
renameTable() | Renames a table and its corresponding sequence (if PostgreSQL). |
replace() | Creates and executes an SQL statement for replacing some text with other text in a given table column. |
reset() | Resets the memoized database connection. |
supportsTimeZones() | Returns whether the database supports time zone conversions. |
truncateTable() | Creates and executes a TRUNCATE TABLE SQL statement. |
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 id s. |
unescapeParam() | Unescapes commas, asterisks, and colons added to a string via escapeParam(). |
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
# batch()
- Since
- 3.7.0
Starts a batch query, similar to yii\db\Query::batch() (opens new window).
Each iteration will be a batch of rows.
foreach (Db::batch($query) as $batch) {
foreach ($batch as $row) {
// ...
}
}
If using MySQL and $db
is null, a new unbuffered (opens new window)
DB connection will be created for the query so that the data can actually be retrieved in batches,
to work around limitations (opens new window)
with query batching in MySQL. Therefore keep in mind that the data retrieved by the batch query won’t
reflect any changes that have been made over the main DB connection, if a transaction is currently
active.
View source (opens new window)
Arguments
$query
(yii\db\QueryInterface (opens new window)) – The query that should be executed$batchSize
(integer (opens new window)) – The number of rows to be fetched in each batch
Returns
yii\db\BatchQueryResult (opens new window) – The batched query to be iterated on
# 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.
If the table contains dateCreated
, dateUpdated
, and/or uid
columns, those values will be included
automatically, if not already set.
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$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
(string (opens new window), array (opens new window)) – The conditions that will be put in theWHERE
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 theWHERE
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
# dropAllForeignKeysToTable()
- Since
- 4.0.0
Drops all the foreign keys that reference a table.
View source (opens new window)
Arguments
$table
(string (opens new window)) – The table that the foreign keys should reference.$db
(craft\db\Connection, null (opens new window)) – The database connection.
# dropForeignKeyIfExists()
- Since
- 4.0.0
Creates and executes a SQL statement for dropping a foreign key if it exists.
View source (opens new window)
Arguments
$table
(string (opens new window)) – The table that the foreign key was created for.$columns
(string (opens new window), string (opens new window)[]) – The column(s) that are included in the foreign key. If there are multiple columns, separate them by commas or use an array.$db
(craft\db\Connection, null (opens new window)) – The database connection.
# dropIndexIfExists()
- Since
- 4.0.0
Creates and executes a SQL statement for dropping an index if it exists.
View source (opens new window)
Arguments
$table
(string (opens new window)) – The table that the index was created for.$columns
(string (opens new window), string (opens new window)[]) – The column(s) that are included in the index. If there are multiple columns, separate them by commas or use an array.$unique
(boolean (opens new window)) – Whether the index has a UNIQUE constraint.$db
(craft\db\Connection, null (opens new window)) – The database connection.
# each()
- Since
- 3.7.0
Starts a batch query and retrieves data row by row.
This method is similar to batch() except that in each iteration of the result, only one row of data is returned.
foreach (Db::each($query) as $row) {
// ...
}
If using MySQL and $db
is null, a new unbuffered (opens new window)
DB connection will be created for the query so that the data can actually be retrieved in batches,
to work around limitations (opens new window)
with query batching in MySQL. Therefore keep in mind that the data retrieved by the batch query won’t
reflect any changes that have been made over the main DB connection, if a transaction is currently
active.
View source (opens new window)
Arguments
$query
(yii\db\QueryInterface (opens new window)) – The query that should be executed$batchSize
(integer (opens new window)) – The number of rows to be fetched in each batch
Returns
yii\db\BatchQueryResult (opens new window) – The batched query to be iterated on
# escapeCommas()
- Since
- 4.0.0
Escapes commas in a string so the value doesn’t get interpreted as an array by parseParam().
View source (opens new window)
Arguments
$value
(string (opens new window)) – The param value.
Returns
string (opens new window) – The escaped param value.
# escapeForLike()
- Since
- 4.3.7
Escapes underscores within a value for a LIKE
condition.
View source (opens new window)
Arguments
$value
(string (opens new window)) – The value
Returns
string (opens new window) – The escaped value
# escapeParam()
Escapes commas, asterisks, and colons in a string, so they are not treated as special characters in parseParam().
View source (opens new window)
Arguments
$value
(string (opens new window)) – The param value.
Returns
string (opens new window) – The escaped param value.
# extractGlue()
- Since
- 3.7.40
Extracts a “glue” param from an a param value.
Supported glue values are and
, or
, and not
.
View source (opens new window)
Arguments
$value
(mixed
)
Returns
string (opens new window), null (opens new window)
# findForeignKey()
- Since
- 4.0.0
Looks for a foreign key on the given table with the given columns, and returns its name, or null if no match is found.
View source (opens new window)
Arguments
$tableName
(string (opens new window)) – The table that the foreign key is on$columns
(string (opens new window), string (opens new window)[]) – The column(s) that are included in the foreign key. If there are multiple columns, separate them by commas or use an array.$db
(craft\db\Connection, null (opens new window)) – The database connection.
Returns
string (opens new window), null (opens new window) – The foreign key name, or null
if there isn’t a matching one.
# findIndex()
- Since
- 4.0.0
Looks for an index on the given table with the given columns and unique property, and returns its name, or null if no match is found.
View source (opens new window)
Arguments
$tableName
(string (opens new window)) – The table that the index was created for.$columns
(string (opens new window), string (opens new window)[]) – The column(s) that are included in the index. If there are multiple columns, separate them by commas or use an array.$unique
(boolean (opens new window)) – Whether the index has a UNIQUE constraint.$db
(craft\db\Connection, null (opens new window)) – The database connection.
Returns
string (opens new window), null (opens new window) – The index name, or null
if there isn’t a matching one.
# 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
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
# 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 if 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
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 uid
s.
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
# 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.
If the table contains dateCreated
, dateUpdated
, and/or uid
columns, those values will be included
automatically, if not already set.
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$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
# isTextualColumnType()
Returns whether the given column type is textual.
View source (opens new window)
Arguments
$columnType
(string (opens new window))
Returns
# 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
Throws
# normalizeParam()
- Since
- 3.7.40
Normalizes a param value with a provided resolver function, unless the resolver function ever returns an empty value.
If the original param value began with and
, or
, or not
, that will be preserved.
View source (opens new window)
Arguments
$value
(mixed
) – The param value to be normalized$resolver
(callable (opens new window)) – Method to resolve non-model values to models
Returns
boolean (opens new window) – Whether the value was normalized
# parseBooleanParam()
- Since
- 3.4.14
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
orfalse
:empty:
or:notempty:
(normalizes tofalse
andtrue
)'not x'
or'!= x'
(normalizes to the opposite of the boolean value ofx
)- 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)) – Hownull
values should be treated
Returns
# 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), DateTimeInterface (opens new window)) – The param value$defaultOperator
(string (opens new window)) – The default operator to apply to the values (can benot
,!=
,<=
,>=
,<
,>
, or=
)
Returns
string (opens new window), array (opens new window)
# 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 only one param is needed.
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
# parseMoneyParam()
- Since
- 4.0.0
Parses a query param value for a money 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.$currency
(string (opens new window)) – The currency code to use for the money object.$value
(string (opens new window), array (opens new window),\Money\Money
) – The param value$defaultOperator
(string (opens new window)) – The default operator to apply to the values (can benot
,!=
,<=
,>=
,<
,>
, or=
)
Returns
string (opens new window), array (opens new window)
# parseNumericParam()
- Since
- 4.0.0
Parses a query param value for a numeric column and returns a yii\db\QueryInterface::where() (opens new window)-compatible condition.
The follow values are supported:
- A number
:empty:
or:notempty:
'not x'
or'!= x'
'> x'
,'>= x'
,'< x'
, or'<= x'
, or a combination of those
View source (opens new window)
Arguments
$column
(string (opens new window)) – The database column that the param is targeting.$value
(string (opens new window), string (opens new window)[]) – The param value$defaultOperator
(string (opens new window)) – The default operator to apply to the values (can benot
,!=
,<=
,>=
,<
,>
, or=
)$columnType
(string (opens new window), null (opens new window)) – The database column type the param is targeting
Returns
string (opens new window), array (opens new window)
Throws
- yii\base\InvalidArgumentException (opens new window)
if the param value isn’t numeric
# 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 not
to define whether all, any, or none of the values must match.
(or
will be assumed by default.)
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 benot
,!=
,<=
,>=
,<
,>
, 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
string (opens new window), array (opens new window)
Throws
- yii\base\InvalidArgumentException (opens new window)
if the param value isn’t compatible with the column type.
# prepareDateForDb()
Prepares a date to be sent to the database.
View source (opens new window)
Arguments
$date
(mixed
) – The date to be prepared
Returns
string (opens new window), null (opens new window) – The prepped date, or null
if it could not be prepared
# prepareMoneyForDb()
- Since
- 4.0.0
Prepares a money object to be sent to the database.
View source (opens new window)
Arguments
$money
(mixed
) – The money to be prepared
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$columnType
(string (opens new window), null (opens new window)) – The type of column the value will be stored in
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
# rawTableShortName()
- Since
- 4.4.0
Returns a table name with curly brackets and percent sign removed.
View source (opens new window)
Arguments
$name
(string (opens new window))
Returns
# renameTable()
- Since
- 4.0.0
Renames a table and its corresponding sequence (if PostgreSQL).
View source (opens new window)
Arguments
$table
(string (opens new window)) – The table to be renamed.$newName
(string (opens new window)) – The new table name.$db
(craft\db\Connection, null (opens new window)) – The database connection.
# replace()
- Since
- 3.5.0
Creates and executes an 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 theWHERE
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)
# supportsTimeZones()
- Since
- 3.6.16
Returns whether the database supports time zone conversions.
This could return false
if MySQL’s time zone tables haven’t been populated yet. See
https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html for more info.
View source (opens new window)
Arguments
Returns
# truncateTable()
- Since
- 3.6.8
Creates and executes a TRUNCATE TABLE
SQL statement.
View source (opens new window)
Arguments
$table
(string (opens new window)) – The table where the data will be deleted from$db
(craft\db\Connection, null (opens new window)) – The database connection to use
Throws
- yii\db\Exception (opens new window)
if execution failed
# 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 id
s.
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
# unescapeParam()
- Since
- 4.4.0
Unescapes commas, asterisks, and colons added to a string via escapeParam().
View source (opens new window)
Arguments
$value
(string (opens new window)) – The param value.
Returns
string (opens new window) – The escaped param value.
# 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 theWHERE
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$updateTimestamp
(boolean (opens new window)) – Whether thedateUpdated
column should be updated, if the table has one.$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.
If the table contains dateCreated
, dateUpdated
, and/or uid
columns, those values will be included
for new rows automatically, if not already set.
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), yii\db\Query (opens new window)) – The column data (name => value) to be inserted into the table or instance of craft\db\Query to performINSERT INTO ... SELECT
SQL statement$updateColumns
(array (opens new window), boolean (opens new window)) – The column data (name => value) to be updated if they already existIf
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$updateTimestamp
(boolean (opens new window)) – Whether thedateUpdated
column should be updated for existing rows, if the table has one.$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
Example
$url = craft\helpers\App::env('CRAFT_DB_URL');
return craft\helpers\Db::url2config($url);
# Constants
Constant | Description |
---|---|
GLUE_AND | |
GLUE_NOT | |
GLUE_OR | |
SIMPLE_TYPE_NUMERIC | |
SIMPLE_TYPE_TEXTUAL |
← DateTimeHelper Diff →