PHP Class SqlBuilder, recess

User: Alexandr Date: 29.09.10
Mostrar archivo Open project: KrisJordan/recess Class Usage Examples

Protected Properties

Property Type Description
$assignments
$conditions * UPDATE & DELETE
$conditionsUsed
$distinct
$groupBy
$joins
$limit
$offset
$orderBy
$select * SELECT
$selectAs
$table * INSERT
$useAssignmentsAsConditions
$usingAliases

Public Methods

Method Description
assign ( string $column, mixed $value ) : SqlBuilder Assign a value to a column. Used with inserts and updates.
between ( string $column, numeric $small, numeric $big ) : SqlBuilder Shortcut alias for SqlBuilder->lessThan($column,$big)->greaterThan($column,$small)
delete ( ) : string Build a DELETE SQL string from SqlBuilder's state.
distinct ( ) : SqlBuilder Add a DISTINCT clause to SELECT SQL.
equal ( string $column, mixed $value ) : SqlBuilder Equality expression for WHERE clause of update, delete, or select statements.
from ( string $table ) : SqlBuilder Alias to specify which table is being used.
getCriteria ( )
getPdoArguments ( ) : array Return the collection of PDO named parameters and values to be applied to a parameterized PDO statement.
getTable ( )
greaterThan ( string $column, numeric $value ) : SqlBuilder Greater than expression for WHERE clause of update, delete, or select statements.
greaterThanOrEqualTo ( string $column, numeric $value ) : SqlBuilder Greater than or equal to expression for WHERE clause of update, delete, or select statements.
groupBy ( string $clause ) : SqlBuilder Add an GROUP BY expression to sql string. Example: ->groupBy('name')
in ( string $column, array $value ) : SqlBuilder IN to expression for WHERE clause of update, delete, or select statements.
innerJoin ( string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder Inner join expression for SELECT SQL statement.
insert ( ) : string Build an INSERT SQL string from SqlBuilder's state.
into ( string $table ) : SqlBuilder Alias for table (insert into)
isNotNull ( string $column ) : SqlBuilder IS NOT NULL expression for WHERE clause of update, delete, or select statements
isNull ( string $column ) : SqlBuilder IS NULL expression for WHERE clause of update, delete, or select statements
leftOuterJoin ( string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder Left outer join expression for SELECT SQL statement.
lessThan ( string $column, numeric $value ) : SqlBuilder Less than expression for WHERE clause of update, delete, or select statements.
lessThanOrEqualTo ( string $column, numeric $value ) : SqlBuilder Less than or equal to expression for WHERE clause of update, delete, or select statements.
like ( string $column, string $value ) : SqlBuilder LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
limit ( integer $size ) : SqlBuilder LIMIT results to some number of records.
notEqual ( string $column, mixed $value ) : SqlBuilder Inequality than expression for WHERE clause of update, delete, or select statements.
notLike ( string $column, string $value ) : SqlBuilder NOT LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
offset ( integer $offset ) : SqlBuilder When used in conjunction with limit($size), offset specifies which row the results will begin at.
orderBy ( string $clause ) : SqlBuilder Add an ORDER BY expression to sql string. Example: ->orderBy('name ASC')
range ( integer $start, integer $finish ) : SqlBuilder Shortcut alias to ->limit($finish - $start)->offset($start);
select ( ) : string Build a SELECT SQL string from SqlBuilder's state.
selectAs ( string $select, string $as ) : SqlBuilder Add additional field to select statement which is aliased using the AS parameter.
table ( string $table ) : SqlBuilder Set the table of focus on a sql statement.
update ( ) : string Build an UPDATE SQL string from SqlBuilder's state.
useAssignmentsAsConditions ( boolean $bool ) : SqlBuilder Handy shortcut which allows assignments to be used as conditions in a select statement.

Protected Methods

Method Description
addCondition ( string $column, mixed $value, string $operator ) : SqlBuilder Add a condition to the SqlBuilder statement. Additional logic here to prepend a table name and also keep track of which columns have already been assigned conditions to ensure we do not use two identical named parameters in PDO.
cleansedAssignmentsAsConditions ( ) : array Method for when using assignments as conditions. This purges assignments which have null values.
deleteSanityCheck ( ) Safety check used with delete.
escapeWithTicks ( $string )
groupByHelper ( )
insertSanityCheck ( ) Safety check used with insert to ensure only a table and assignments were applied.
join ( string $leftOrRight, string $innerOrOuter, string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder Generic join expression to be added to a SELECT SQL statement.
joinHelper ( )
orderByHelper ( )
rangeHelper ( )
selectSanityCheck ( ) Safety check used when creating a SELECT statement.
tableAsPrefix ( ) : string Helper method which returns the current table even when it is aliased due to joins between the same table.
updateSanityCheck ( ) Safety check used with update.
whereHelper ( ) * HELPER METHODS

Method Details

addCondition() protected method

Add a condition to the SqlBuilder statement. Additional logic here to prepend a table name and also keep track of which columns have already been assigned conditions to ensure we do not use two identical named parameters in PDO.
protected addCondition ( string $column, mixed $value, string $operator ) : SqlBuilder
$column string
$value mixed
$operator string
return SqlBuilder

assign() public method

Assign a value to a column. Used with inserts and updates.
public assign ( string $column, mixed $value ) : SqlBuilder
$column string
$value mixed
return SqlBuilder

between() public method

Shortcut alias for SqlBuilder->lessThan($column,$big)->greaterThan($column,$small)
public between ( string $column, numeric $small, numeric $big ) : SqlBuilder
$column string
$small numeric Greater than this number.
$big numeric Less than this number.
return SqlBuilder

cleansedAssignmentsAsConditions() protected method

Method for when using assignments as conditions. This purges assignments which have null values.

delete() public method

Build a DELETE SQL string from SqlBuilder's state.
public delete ( ) : string
return string DELETE string

deleteSanityCheck() protected method

Safety check used with delete.
protected deleteSanityCheck ( )

distinct() public method

Add a DISTINCT clause to SELECT SQL.
public distinct ( ) : SqlBuilder
return SqlBuilder

equal() public method

Equality expression for WHERE clause of update, delete, or select statements.
public equal ( string $column, mixed $value ) : SqlBuilder
$column string
$value mixed
return SqlBuilder

escapeWithTicks() protected static method

protected static escapeWithTicks ( $string )

from() public method

Alias to specify which table is being used.
public from ( string $table ) : SqlBuilder
$table string
return SqlBuilder

getCriteria() public method

public getCriteria ( )

getPdoArguments() public method

Return the collection of PDO named parameters and values to be applied to a parameterized PDO statement.
public getPdoArguments ( ) : array
return array

getTable() public method

public getTable ( )

greaterThan() public method

Greater than expression for WHERE clause of update, delete, or select statements.
public greaterThan ( string $column, numeric $value ) : SqlBuilder
$column string
$value numeric
return SqlBuilder

greaterThanOrEqualTo() public method

Greater than or equal to expression for WHERE clause of update, delete, or select statements.
public greaterThanOrEqualTo ( string $column, numeric $value ) : SqlBuilder
$column string
$value numeric
return SqlBuilder

groupBy() public method

Add an GROUP BY expression to sql string. Example: ->groupBy('name')
public groupBy ( string $clause ) : SqlBuilder
$clause string
return SqlBuilder

groupByHelper() protected method

protected groupByHelper ( )

in() public method

IN to expression for WHERE clause of update, delete, or select statements.
public in ( string $column, array $value ) : SqlBuilder
$column string
$value array
return SqlBuilder

innerJoin() public method

Inner join expression for SELECT SQL statement.
public innerJoin ( string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder
$table string
$tablePrimaryKey string
$fromTableForeignKey string
return SqlBuilder

insert() public method

Build an INSERT SQL string from SqlBuilder's state.
public insert ( ) : string
return string INSERT string.

insertSanityCheck() protected method

Safety check used with insert to ensure only a table and assignments were applied.
protected insertSanityCheck ( )

into() public method

Alias for table (insert into)
public into ( string $table ) : SqlBuilder
$table string
return SqlBuilder

isNotNull() public method

IS NOT NULL expression for WHERE clause of update, delete, or select statements
public isNotNull ( string $column ) : SqlBuilder
$column string
return SqlBuilder

isNull() public method

IS NULL expression for WHERE clause of update, delete, or select statements
public isNull ( string $column ) : SqlBuilder
$column string
return SqlBuilder

join() protected method

Generic join expression to be added to a SELECT SQL statement.
protected join ( string $leftOrRight, string $innerOrOuter, string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder
$leftOrRight string
$innerOrOuter string
$table string
$tablePrimaryKey string
$fromTableForeignKey string
return SqlBuilder

joinHelper() protected method

protected joinHelper ( )

leftOuterJoin() public method

Left outer join expression for SELECT SQL statement.
public leftOuterJoin ( string $table, string $tablePrimaryKey, string $fromTableForeignKey ) : SqlBuilder
$table string
$tablePrimaryKey string
$fromTableForeignKey string
return SqlBuilder

lessThan() public method

Less than expression for WHERE clause of update, delete, or select statements.
public lessThan ( string $column, numeric $value ) : SqlBuilder
$column string
$value numeric
return SqlBuilder

lessThanOrEqualTo() public method

Less than or equal to expression for WHERE clause of update, delete, or select statements.
public lessThanOrEqualTo ( string $column, numeric $value ) : SqlBuilder
$column string
$value numeric
return SqlBuilder

like() public method

LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
public like ( string $column, string $value ) : SqlBuilder
$column string
$value string
return SqlBuilder

limit() public method

LIMIT results to some number of records.
public limit ( integer $size ) : SqlBuilder
$size integer
return SqlBuilder

notEqual() public method

Inequality than expression for WHERE clause of update, delete, or select statements.
public notEqual ( string $column, mixed $value ) : SqlBuilder
$column string
$value mixed
return SqlBuilder

notLike() public method

NOT LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
public notLike ( string $column, string $value ) : SqlBuilder
$column string
$value string
return SqlBuilder

offset() public method

When used in conjunction with limit($size), offset specifies which row the results will begin at.
public offset ( integer $offset ) : SqlBuilder
$offset integer
return SqlBuilder

orderBy() public method

Add an ORDER BY expression to sql string. Example: ->orderBy('name ASC')
public orderBy ( string $clause ) : SqlBuilder
$clause string
return SqlBuilder

orderByHelper() protected method

protected orderByHelper ( )

range() public method

Shortcut alias to ->limit($finish - $start)->offset($start);
public range ( integer $start, integer $finish ) : SqlBuilder
$start integer
$finish integer
return SqlBuilder

rangeHelper() protected method

protected rangeHelper ( )

select() public method

Build a SELECT SQL string from SqlBuilder's state.
public select ( ) : string
return string

selectAs() public method

->selectAs("ABS(location - 5)", 'distance') translates to => SELECT ABS(location-5) AS distance
public selectAs ( string $select, string $as ) : SqlBuilder
$select string
$as string
return SqlBuilder

selectSanityCheck() protected method

Safety check used when creating a SELECT statement.
protected selectSanityCheck ( )

table() public method

Set the table of focus on a sql statement.
public table ( string $table ) : SqlBuilder
$table string
return SqlBuilder

tableAsPrefix() protected method

Helper method which returns the current table even when it is aliased due to joins between the same table.
protected tableAsPrefix ( ) : string
return string The current table which can be used as a prefix.

update() public method

Build an UPDATE SQL string from SqlBuilder's state.
public update ( ) : string
return string

updateSanityCheck() protected method

Safety check used with update.
protected updateSanityCheck ( )

useAssignmentsAsConditions() public method

Handy shortcut which allows assignments to be used as conditions in a select statement.
public useAssignmentsAsConditions ( boolean $bool ) : SqlBuilder
$bool boolean
return SqlBuilder

whereHelper() protected method

* HELPER METHODS
protected whereHelper ( )

Property Details

$assignments protected_oe property

protected $assignments

$conditions protected_oe property

* UPDATE & DELETE
protected $conditions

$conditionsUsed protected_oe property

protected $conditionsUsed

$distinct protected_oe property

protected $distinct

$groupBy protected_oe property

protected $groupBy

$joins protected_oe property

protected $joins

$limit protected_oe property

protected $limit

$offset protected_oe property

protected $offset

$orderBy protected_oe property

protected $orderBy

$select protected_oe property

* SELECT
protected $select

$selectAs protected_oe property

protected $selectAs

$table protected_oe property

* INSERT
protected $table

$useAssignmentsAsConditions protected_oe property

protected $useAssignmentsAsConditions

$usingAliases protected_oe property

protected $usingAliases