class SQLInsert extends SQLExpression implements SQLWriteExpression (View source)

Object representing a SQL INSERT query.

The various parts of the SQL query can be manipulated individually.

Properties

protected array $replacementsOld

Keep an internal register of find/replace pairs to execute when it's time to actually get the query SQL.

from  SQLExpression
protected array $replacementsNew

Keep an internal register of find/replace pairs to execute when it's time to actually get the query SQL.

from  SQLExpression
protected SQLAssignmentRow[] $rows

List of rows to be inserted

protected string $into

The table name to insert into

Methods

public
replaceText(string $old, string $new)

Swap some text in the SQL query with another.

public
string
__toString()

Return the generated SQL string for this query

public
renameTable(string $old, string $new)

Swap the use of one table with another.

public
bool
isEmpty()

Determine if this query is empty, and thus cannot be executed

public
string
sql(array $parameters = [])

Generate the SQL statement for this query.

public
execute()

Execute this query.

protected
copyTo(SQLExpression $object)

Copies the query parameters contained in this object to another SQLExpression

public static 
create(string $into = null, array $assignments = [])

Construct a new SQLInsert object

public
__construct(string $into = null, array $assignments = [])

Construct a new SQLInsert object

public
$this
setInto(string $into)

Sets the table name to insert into

public
string
getInto()

Gets the table name to insert into

public
$this
addRow(array|SQLAssignmentRow $data = null)

Appends a new row to insert

public
getRows()

Returns the current list of rows

public
array
getColumns()

Returns the list of distinct column names used in this insert

public
$this
setRows(array $rows)

Sets all rows to the given array

public
$this
addRows(array $rows)

Adds the list of rows to the array

public
currentRow(bool $create = false)

Returns the currently set row

public
$this
addAssignments(array $assignments)

Adds assignments for a list of several fields.

public
$this
setAssignments(array $assignments)

Sets the list of assignments to the given list

public
array
getAssignments()

Retrieves the list of assignments in parameterised format

public
$this
assign(string $field, mixed $value)

Set the value for a single field

public
$this
assignSQL(string $field, string $sql)

Assigns a value to a field using the literal SQL expression, rather than a value to be escaped

public
$this
clearRow()

Clears all currently set assignment values on the current row

public
$this
clear()

Clears all rows

Details

replaceText(string $old, string $new)

Swap some text in the SQL query with another.

Note that values in parameters will not be replaced

Parameters

string $old

The old text (escaped)

string $new

The new text (escaped)

string __toString()

Return the generated SQL string for this query

Is it ok for this to consider parameters? Test cases here!

Return Value

string

renameTable(string $old, string $new)

Swap the use of one table with another.

Parameters

string $old

Name of the old table (unquoted, escaped)

string $new

Name of the new table (unquoted, escaped)

bool isEmpty()

Determine if this query is empty, and thus cannot be executed

Return Value

bool

Flag indicating that this query is empty

string sql(array $parameters = [])

Generate the SQL statement for this query.

Parameters

array $parameters

Out variable for parameters required for this query

Return Value

string

The completed SQL query

Query execute()

Execute this query.

Return Value

Query

protected copyTo(SQLExpression $object)

Copies the query parameters contained in this object to another SQLExpression

Parameters

SQLExpression $object

The object to copy properties to

static SQLInsert create(string $into = null, array $assignments = [])

Construct a new SQLInsert object

Parameters

string $into

Table name to insert into (ANSI quoted)

array $assignments

List of column assignments

Return Value

SQLInsert

__construct(string $into = null, array $assignments = [])

Construct a new SQLInsert object

Parameters

string $into

Table name to insert into (ANSI quoted)

array $assignments

List of column assignments

$this setInto(string $into)

Sets the table name to insert into

Parameters

string $into

Single table name (ANSI quoted)

Return Value

$this

The self reference to this query

string getInto()

Gets the table name to insert into

Return Value

string

Single table name

$this addRow(array|SQLAssignmentRow $data = null)

Appends a new row to insert

Parameters

array|SQLAssignmentRow $data

A list of data to include for this row

Return Value

$this

The self reference to this query

SQLAssignmentRow[] getRows()

Returns the current list of rows

Return Value

SQLAssignmentRow[]

array getColumns()

Returns the list of distinct column names used in this insert

Return Value

array

$this setRows(array $rows)

Sets all rows to the given array

Parameters

array $rows

the list of rows

Return Value

$this

The self reference to this query

$this addRows(array $rows)

Adds the list of rows to the array

Parameters

array $rows

the list of rows

Return Value

$this

The self reference to this query

SQLAssignmentRow|false currentRow(bool $create = false)

Returns the currently set row

Parameters

bool $create

Flag to indicate if a row should be created if none exists

Return Value

SQLAssignmentRow|false

The row, or false if none exists

$this addAssignments(array $assignments)

Adds assignments for a list of several fields.

For multi-row objects this applies this to the current row.

Note that field values must not be escaped, as these will be internally parameterised by the database engine.


// Basic assignments
$query->addAssignments([
     '"Object"."Title"' => 'Bob',
     '"Object"."Description"' => 'Bob was here'
])

// Parameterised assignments
$query->addAssignments([
     '"Object"."Title"' => ['?' => 'Bob'],
     '"Object"."Description"' => ['?' => null]
])

// Complex parameters
$query->addAssignments([
     '"Object"."Score"' => ['MAX(?,?)' => [1, 3]]
]);

// Assignment of literal SQL for a field. The empty array is
// important to denote the zero-number parameter list
$query->addAssignments([
     '"Object"."Score"' => ['NOW()' => []]
]);

Parameters

array $assignments

The list of fields to assign

Return Value

$this

Self reference

$this setAssignments(array $assignments)

Sets the list of assignments to the given list

For multi-row objects this applies this to the current row.

Parameters

array $assignments

Return Value

$this

Self reference

array getAssignments()

Retrieves the list of assignments in parameterised format

For multi-row objects returns assignments for the current row.

Return Value

array

List of assignments. The key of this array will be the column to assign, and the value a parameterised array in the format ['SQL' => [parameters]];

$this assign(string $field, mixed $value)

Set the value for a single field

For multi-row objects this applies this to the current row.

E.g.


// Literal assignment
$query->assign('"Object"."Description"', 'lorum ipsum'));

// Single parameter
$query->assign('"Object"."Title"', ['?' => 'Bob']);

// Complex parameters
$query->assign('"Object"."Score"', ['MAX(?,?)' => [1, 3]]);

Parameters

string $field

The field name to update

mixed $value

The value to assign to this field. This could be an array containing a parameterised SQL query of any number of parameters, or a single literal value.

Return Value

$this

Self reference

$this assignSQL(string $field, string $sql)

Assigns a value to a field using the literal SQL expression, rather than a value to be escaped

For multi-row objects this applies this to the current row.

Parameters

string $field

The field name to update

string $sql

The SQL to use for this update. E.g. "NOW()"

Return Value

$this

Self reference

$this clearRow()

Clears all currently set assignment values on the current row

Return Value

$this

The self reference to this query

$this clear()

Clears all rows

Return Value

$this

The self reference to this query