Menu

## Query

Query.php : 39

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

Query.php : 155
`protected mixed $_supportsAsAlias = true`

#### $_whereInCnt

Query.php : 157
`protected mixed $_whereInCnt = 1`

### Methods

#### __construct()

Query.php : 53

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

Query.php : 614

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

Query.php : 242

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

Query.php : 169

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

Query.php : 182

Database connection - override by the database driver.

`public abstract 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()

Query.php : 258

Get the Database host for this query instance

`public database() : DataTable`

##### Return values

DataTable —

Database class instance

#### distinct()

Query.php : 270

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

Query.php : 211

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

Query.php : 282

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

Query.php : 315

Get fields.

`public get(mixed $get) : self`

@param string|string[] $get,... Fields to get - can be specified as individual fields or an array of fields.

##### Parameters

$get : mixed

##### Return values

self —

#### group_by()

Query.php : 399

Group the results by the values in a field

`public group_by(mixed $group_by) : self`

##### Parameters

$group_by : mixed

##### Return values

self —

#### join()

Query.php : 356

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

Query.php : 387

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

Query.php : 462

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

Query.php : 636

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

Query.php : 477

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

Query.php : 413

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

Query.php : 199

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

Query.php : 520

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

Query.php : 432

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

Query.php : 189

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

Query.php : 574

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

Query.php : 684

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

Query.php : 712

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

Query.php : 806

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

Query.php : 1007

Prepare the SQL query by populating the bound variables.

`protected abstract _prepare(mixed $sql) : void`

Provided by the driver

##### Parameters

$sql : mixed

##### Return values

void —

@internal

## Search results