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 $rows

List of rows to be inserted

protected string $into

The table name to insert into

Methods

public
__get($field) deprecated

No description

public
__set($field, $value) deprecated

No description

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 = array())

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 = array())

Construct a new SQLInsert object

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

Construct a new SQLInsert object

public
setInto(string $into)

Sets the table name to insert into

public
string
getInto()

Gets the table name to insert into

public
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
setRows(array $rows)

Sets all rows to the given array

public
addRows(array $rows)

Adds the list of rows to the array

public
currentRow(bool $create = false)

Returns the currently set row

public
addAssignments(array $assignments)

Adds assignments for a list of several fields.

public
setAssignments(array $assignments)

Sets the list of assignments to the given list

public
array
getAssignments()

Retrieves the list of assignments in parameterised format

public
assign(string $field, mixed $value)

Set the value for a single field

public
assignSQL(string $field, string $sql)

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

public
clearRow()

Clears all currently set assigment values on the current row

public
clear()

Clears all rows

Details

__get($field) deprecated

deprecated since version 4.0

No description

Parameters

$field

__set($field, $value) deprecated

deprecated since version 4.0

No description

Parameters

$field
$value

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 = array())

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

SS_Query execute()

Execute this query.

Return Value

SS_Query

protected copyTo(SQLExpression $object)

Copies the query parameters contained in this object to another SQLExpression

Parameters

SQLExpression $object

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

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 = array())

Construct a new SQLInsert object

Parameters

string $into

Table name to insert into (ANSI quoted)

array $assignments

List of column assignments

SQLInsert setInto(string $into)

Sets the table name to insert into

Parameters

string $into

Single table name (ANSI quoted)

Return Value

SQLInsert

The self reference to this query

string getInto()

Gets the table name to insert into

Return Value

string

Single table name

SQLInsert 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

SQLInsert

The self reference to this query

getRows()

Returns the current list of rows

array getColumns()

Returns the list of distinct column names used in this insert

Return Value

array

SQLInsert setRows(array $rows)

Sets all rows to the given array

Parameters

array $rows

the list of rows

Return Value

SQLInsert

The self reference to this query

SQLInsert addRows(array $rows)

Adds the list of rows to the array

Parameters

array $rows

the list of rows

Return Value

SQLInsert

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

SQLWriteExpression 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(array(
    '"Object"."Title"' => 'Bob',
    '"Object"."Description"' => 'Bob was here'
))

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

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

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

Parameters

array $assignments

The list of fields to assign

Return Value

SQLWriteExpression

Self reference

SQLWriteExpression 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

SQLWriteExpression

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 assigments. The key of this array will be the column to assign, and the value a parameterised array in the format array('SQL' => array(parameters));

SQLWriteExpression 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"', array('?' => 'Bob'));

// Complex parameters
$query->assign('"Object"."Score"', array('MAX(?,?)' => array(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

SQLWriteExpression

Self reference

SQLWriteExpression 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

SQLWriteExpression

Self reference

SQLInsert clearRow()

Clears all currently set assigment values on the current row

Return Value

SQLInsert

The self reference to this query

SQLInsert clear()

Clears all rows

Return Value

SQLInsert

The self reference to this query