Editor PHP 2.0.5

Query

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.

Table of Contents

$_supportsAsAlias  : mixed
$_whereInCnt  : mixed
__construct()  : mixed
Query instance constructor.
and_where()  : mixed
Add addition where conditions to the query with an AND operator. An alias of `where` for naming consistency.
bind()  : Query
Safely bind an input value to a parameter. This is evaluated when the query is executed. This allows user input to be safely executed without risk of an SQL injection attack.
commit()  : mixed
Commit a transaction.
connect()  : mixed
Database connection - override by the database driver.
database()  : DataTable
Get the Database host for this query instance
distinct()  : Query
Set a distinct flag for a `select` query. Note that this has no effect on any of the other query types.
dsnPostfix()  : Query
Common helper for the drivers to handle a PDO DSN postfix
exec()  : Result
Execute the query.
get()  : self
Get fields.
group_by()  : self
Group the results by the values in a field
join()  : mixed
Perform a JOIN operation
limit()  : self
Limit the result set to a certain size.
offset()  : self
Offset the return set by a given number of records (useful for paging).
or_where()  : mixed
Add addition where conditions to the query with an OR operator.
order()  : self
Order by
pkey()  : Query|array<string|int, string>
Get / set the primary key column name(s) so they can be easily returned after an insert.
rollback()  : mixed
Rollback the database state to the start of the transaction.
set()  : mixed
Set fields to a given value.
table()  : self
Set table(s) to perform the query on.
transaction()  : mixed
Start a database transaction
where()  : mixed
Where query - multiple conditions are bound as ANDs.
where_group()  : mixed
Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.
where_in()  : mixed
Provide a method that can be used to perform a `WHERE ... IN (...)` query with bound values and parameters.
_build_limit()  : string
Create the LIMIT / OFFSET string
_prepare()  : void
Prepare the SQL query by populating the bound variables.

Properties

$_supportsAsAlias

protected mixed $_supportsAsAlias = true

$_whereInCnt

protected mixed $_whereInCnt = 1

Methods

__construct()

Query instance constructor.

public __construct(mixed $dbHost, mixed $type[, mixed $table = null ]) : mixed

Note that typically instances of this class will be automatically created through the \DataTables\Database->query() method.

Parameters
$dbHost : mixed
$type : mixed
$table : mixed = null
Return values
mixed —

and_where()

Add addition where conditions to the query with an AND operator. An alias of `where` for naming consistency.

public and_where(string|array<string|int, string>|callable $key[, mixed $value = null ][, mixed $op = "=" ][, mixed $bind = true ]) : mixed

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|array<string|int, string>|callable

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. @param 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 !=. @param string $op Condition operator: <, >, = etc @param boolean $bind Escape the value (true, default) or not (false). @return self

$value : mixed = null
$op : mixed = "="
$bind : mixed = true
Return values
mixed —

bind()

Safely bind an input value to a parameter. This is evaluated when the query is executed. This allows user input to be safely executed without risk of an SQL injection attack.

public bind(string $name, string $value[, mixed $type = null ]) : Query
Parameters
$name : string

Parameter name. This should include a leading colon

$value : string

Value to bind

$type : mixed = null

Data type. See the PHP PDO documentation: http://php.net/manual/en/pdo.constants.php

Return values
Query —

commit()

Commit a transaction.

public static commit(mixed $dbh) : mixed

@param \PDO $dbh The Database handle (typically a PDO object, but not always).

Parameters
$dbh : mixed
Return values
mixed —

connect()

Database connection - override by the database driver.

public static connect(mixed $user[, string $pass = '' ][, mixed $host = '' ][, mixed $port = '' ][, mixed $db = '' ][, mixed $dsn = '' ]) : mixed

@param string|array $user User name or all parameters in an array

Parameters
$user : mixed
$pass : string = ''

Password @param string $host Host name @param string $db Database name @return Query

$host : mixed = ''
$port : mixed = ''
$db : mixed = ''
$dsn : mixed = ''
Return values
mixed —

database()

Get the Database host for this query instance

public database() : DataTable
Return values
DataTable —

Database class instance

distinct()

Set a distinct flag for a `select` query. Note that this has no effect on any of the other query types.

public distinct(mixed $dis) : Query

@param boolean $dis Optional

Parameters
$dis : mixed
Return values
Query —

dsnPostfix()

Common helper for the drivers to handle a PDO DSN postfix

public static dsnPostfix(mixed $dsn) : Query

@param string $dsn DSN postfix to use

Parameters
$dsn : mixed
Return values
Query —

@internal

exec()

Execute the query.

public exec([mixed $sql = null ]) : Result

@param string $sql SQL string to execute (only if _type is 'raw').

Parameters
$sql : mixed = null
Return values
Result —

get()

Get fields.

public get(mixed $get) : self

@param 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.

Parameters
$get : mixed
Return values
self —

group_by()

Group the results by the values in a field

public group_by(mixed $group_by) : self
Parameters
$group_by : mixed
Return values
self —

join()

Perform a JOIN operation

public join(mixed $table, string $condition[, mixed $type = '' ][, mixed $bind = true ]) : mixed

@param string $table Table name to do the JOIN on

Parameters
$table : mixed
$condition : string

JOIN condition @param string $type JOIN type @return self

$type : mixed = ''
$bind : mixed = true
Return values
mixed —

limit()

Limit the result set to a certain size.

public limit(mixed $lim) : self

@param int $lim The number of records to limit the result to.

Parameters
$lim : mixed
Return values
self —

offset()

Offset the return set by a given number of records (useful for paging).

public offset(mixed $off) : self

@param int $off The number of records to offset the result by.

Parameters
$off : mixed
Return values
self —

or_where()

Add addition where conditions to the query with an OR operator.

public or_where(string|array<string|int, string>|callable $key[, mixed $value = null ][, mixed $op = "=" ][, mixed $bind = true ]) : mixed

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|array<string|int, string>|callable

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. @param 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 !=. @param string $op Condition operator: <, >, = etc @param boolean $bind Escape the value (true, default) or not (false). @return self

$value : mixed = null
$op : mixed = "="
$bind : mixed = true
Return values
mixed —

order()

Order by

public order(mixed $order) : self

@param 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.

Parameters
$order : mixed
Return values
self —

pkey()

Get / set the primary key column name(s) so they can be easily returned after an insert.

public pkey([array<string|int, string> $pkey = null ]) : Query|array<string|int, string>
Parameters
$pkey : array<string|int, string> = null

Primary keys

Return values
Query|array<string|int, string> —

rollback()

Rollback the database state to the start of the transaction.

public static rollback(mixed $dbh) : mixed

@param \PDO $dbh The Database handle (typically a PDO object, but not always).

Parameters
$dbh : mixed
Return values
mixed —

set()

Set fields to a given value.

public set(string|array<string|int, string> $set[, mixed $val = null ][, mixed $bind = true ]) : mixed

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|array<string|int, string>

Can be given as a single string, when then $val must be set, or as an array of key/value pairs to be set. @param string $val When $set is given as a simple string, $set is the field name and this is the field's value. @param boolean $bind Should the value be bound or not @return self

$val : mixed = null
$bind : mixed = true
Return values
mixed —

table()

Set table(s) to perform the query on.

public table(mixed $table) : self

@param 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.

Parameters
$table : mixed
Return values
self —

transaction()

Start a database transaction

public static transaction(mixed $dbh) : mixed

@param \PDO $dbh The Database handle (typically a PDO object, but not always).

Parameters
$dbh : mixed
Return values
mixed —

where()

Where query - multiple conditions are bound as ANDs.

public where(string|array<string|int, string>|callable $key[, mixed $value = null ][, mixed $op = "=" ][, mixed $bind = true ]) : mixed

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|array<string|int, string>|callable

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. @param 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 !=. @param string $op Condition operator: <, >, = etc @param boolean $bind Escape the value (true, default) or not (false). @return 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->or_where( 'location', 'Canada' );
    } );
$value : mixed = null
$op : mixed = "="
$bind : mixed = true
Return values
mixed —

where_group()

Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.

public where_group(bool|callable $inOut[, mixed $op = 'AND' ]) : mixed

For legacy reasons this method also provides the ability to explicitly define if a grouping bracket should be opened or closed in the query. This method is not prefer.

Parameters
$inOut : bool|callable

If callable it will create the group automatically and pass the query into the called function. For legacy operations use true to open brackets, false to close. @param string $op Conditional operator to use to join to the preceding condition. Default AND. @return self

@example

$query->where_group( function ($q) {
  $q->where( 'location', 'Edinburgh' );
  $q->where( 'position', 'Manager' );
} );
$op : mixed = 'AND'
Return values
mixed —

where_in()

Provide a method that can be used to perform a `WHERE ... IN (...)` query with bound values and parameters.

public where_in(mixed $field, mixed $arr[, mixed $operator = "AND" ]) : mixed

Note this is only suitable for local values, not a sub-query. For that use ->where() with an unbound value.

Parameters
$field : mixed
$arr : mixed
$operator : mixed = "AND"
Return values
mixed —

_build_limit()

Create the LIMIT / OFFSET string

protected _build_limit() : string

MySQL and Postgres style - anything else can have the driver override

Return values
string —

@internal

_prepare()

Prepare the SQL query by populating the bound variables.

protected _prepare(mixed $sql) : void

Provided by the driver

Parameters
$sql : mixed
Return values
void —

@internal

Search results