Perform an individual query on the database.
The typical pattern for using this class is through the DataTables\Database::query()
method (and it's 'select', etc short-cuts).
Typically it would not be initialised directly.
Note that this is a stub class that a driver will extend and complete as
required for individual database types. Individual drivers could add additional
methods, but this is discouraged to ensure that the API is the same for all
database types.
Methods summary
public
|
|
#
__construct( $db, $type, $table = null )
Query instance constructor.
Query instance constructor.
Note that typically instances of this class will be automatically created
through the DataTables\Database::query() method.
Parameters
- $db
DataTables\Database
$db Database instance
- $type
string
$type Query type - 'select', 'insert', 'update' or 'delete'
- $table
string|string[]
$table Tables to operate on - see table.
|
public static
|
|
#
commit( $dbh )
Commit a transaction.
Parameters
- $dbh
*
$dbh The Database handle (typically a PDO object, but not always).
|
public static
|
DataTables\Database\Query
|
#
connect( $user, $pass = '', $host = '', $port = '', $db = '', $dsn = '' )
Database connection - override by the database driver.
Database connection - override by the database driver.
Parameters
- $user
string|array
$user User name or all parameters in an array
- $pass
string
$pass Password
- $host
string
$host Host name
- $port
string
$db Database name
- $db
- $dsn
Returns
|
public static
|
|
#
transaction( $dbh )
Start a database transaction
Start a database transaction
Parameters
- $dbh
*
$dbh The Database handle (typically a PDO object, but not always).
|
public static
|
|
#
rollback( $dbh )
Rollback the database state to the start of the transaction.
Rollback the database state to the start of the transaction.
Parameters
- $dbh
*
$dbh The Database handle (typically a PDO object, but not always).
|
public
|
DataTables\Database\Query
|
#
distinct( $dis )
Set a distinct flag for a select query. Note that this has no
effect on any of the other query types.
Set a distinct flag for a select query. Note that this has no
effect on any of the other query types.
Parameters
- $dis
boolean
$dis Optional
Returns
|
public
|
DataTables\Database\Result
|
#
exec( $sql = null )
Execute the query.
Parameters
- $sql
string
$sql SQL string to execute (only if _type is 'raw').
Returns
|
public
|
self
|
#
get( $get )
Get fields.
Parameters
- $get
string|string[]
$get,... Fields to get - can be specified as individual fields, an array of
fields, a string of comma separated fields or any combination of those.
Returns
self
|
public
|
self
|
#
join( $table, $condition, $type = '' )
Perform a JOIN operation
Parameters
- $table
sting
$table Table name to do the JOIN on
- $condition
string
$condition JOIN condition
- $type
string
$type JOIN type
Returns
self
|
public
|
self
|
#
limit( $lim )
Limit the result set to a certain size.
Limit the result set to a certain size.
Parameters
- $lim
integer
$lim The number of records to limit the result to.
Returns
self
|
public
|
self
|
#
table( $table )
Set table(s) to perform the query on.
Set table(s) to perform the query on.
Parameters
- $table
string|string[]
$table,... Table(s) to use - can be specified as individual names, an array
of names, a string of comma separated names or any combination of those.
Returns
self
|
public
|
self
|
#
offset( $off )
Offset the return set by a given number of records (useful for paging).
Offset the return set by a given number of records (useful for paging).
Parameters
- $off
integer
$off The number of records to offset the result by.
Returns
self
|
public
|
self
|
#
order( $order )
Order by
Parameters
- $order
string|string[]
$order Columns and direction to order by - can be specified as individual
names, an array of names, a string of comma separated names or any combination
of those.
Returns
self
|
public
|
self
|
#
set( $set, $val = null )
Set fields to a given value.
Set fields to a given value.
Can be used in two different ways, as set( field, value ) or as an array of
fields to set: set( array( 'fieldName' => 'value', ...) );
Parameters
- $set
string|string[]
$set Can be given as a single string, when then $val must be set, or as an
array of key/value pairs to be set.
- $val
string
$val When $set is given as a simple string, $set is the field name and this
is the field's value.
Returns
self
|
public
|
self
|
#
where( $key, $value = null, $op = "=", $bind = true )
Where query - multiple conditions are bound as ANDs.
Where query - multiple conditions are bound as ANDs.
Can be used in two different ways, as where( field, value ) or as an array of
conditions to use: where( array('fieldName', ...), array('value', ...) );
Parameters
- $key
string|string[]|function
$key Single field name, or an array of field names. If given as a function
(i.e. a closure), the function is called, passing the query itself in as the
only parameter, so the function can add extra conditions with parentheses around
the additional parameters.
- $value
string|string[]
$value Single field value, or an array of values. Can be null to search for
IS NULL or IS NOT NULL (depending on the value of
$op which should be = or != .
- $op
string
$op Condition operator: <, >, = etc
- $bind
boolean
$bind Escape the value (true, default) or not (false).
Returns
self
Example
The following will produce 'WHERE name='allan' AND (
location='Scotland' OR location='Canada' ) :
$query
->where( 'name', 'allan' )
->where( function ($q) {
$q->where( 'location', 'Scotland' );
$q->where( 'location', 'Canada' );
} );
|
public
|
self
|
#
and_where( $key, $value = null, $op = "=", $bind = true )
Add addition where conditions to the query with an AND operator. An alias of
where for naming consistency.
Add addition where conditions to the query with an AND operator. An alias of
where for naming consistency.
Can be used in two different ways, as where( field, value ) or as an array of
conditions to use: where( array('fieldName', ...), array('value', ...) );
Parameters
- $key
string|string[]|function
$key Single field name, or an array of field names. If given as a function
(i.e. a closure), the function is called, passing the query itself in as the
only parameter, so the function can add extra conditions with parentheses around
the additional parameters.
- $value
string|string[]
$value Single field value, or an array of values. Can be null to search for
IS NULL or IS NOT NULL (depending on the value of
$op which should be = or != .
- $op
string
$op Condition operator: <, >, = etc
- $bind
boolean
$bind Escape the value (true, default) or not (false).
Returns
self
|
public
|
self
|
#
or_where( $key, $value = null, $op = "=", $bind = true )
Add addition where conditions to the query with an OR operator.
Add addition where conditions to the query with an OR operator.
Can be used in two different ways, as where( field, value ) or as an array of
conditions to use: where( array('fieldName', ...), array('value', ...) );
Parameters
- $key
string|string[]|function
$key Single field name, or an array of field names. If given as a function
(i.e. a closure), the function is called, passing the query itself in as the
only parameter, so the function can add extra conditions with parentheses around
the additional parameters.
- $value
string|string[]
$value Single field value, or an array of values. Can be null to search for
IS NULL or IS NOT NULL (depending on the value of
$op which should be = or != .
- $op
string
$op Condition operator: <, >, = etc
- $bind
boolean
$bind Escape the value (true, default) or not (false).
Returns
self
|
public
|
self
|
#
where_group( $inOut, $op = 'AND' )
Provide grouping for WHERE conditions. Calling this function with
true as the first parameter will open a bracket, and
false will then close it.
Provide grouping for WHERE conditions. Calling this function with
true as the first parameter will open a bracket, and
false will then close it.
Parameters
- $inOut
boolean
$inOut true to open brackets, false to close
- $op
string
$op Conditional operator to use to join to the preceding condition. Default
AND .
Returns
self
|