Menu

## Query
in package

Query.php : 34

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() : self
  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() : Query
  Database connection - override by the database driver.
database() : Database
  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.
exec() : Result
  Execute the query.
get() : self
  Get fields.
group_by() : self
  Group the results by the values in a field.
join() : self
  Perform a JOIN operation.
left_join() : mixed
  Add a left join, with common logic for handling binding or not.
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() : self
  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() : self
  Set fields to a given value.
table() : self
  Set table(s) to perform the query on.
transaction() : mixed
  Start a database transaction.
where() : self
  Where query - multiple conditions are bound as ANDs.
where_group() : self
  Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.
where_in() : self
  Provide a method that can be used to perform a `WHERE ... IN (...)` query with bound values and parameters.
_escape_field() : mixed
  Escape quotes in a field identifier.

### Properties

#### $_supportsAsAlias

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

#### $_whereInCnt

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

### Methods

#### __construct()

Query.php : 50

Query instance constructor.

`public __construct(Database $dbHost, string $type[, string|array<string|int, string> $table = null ]) : mixed`

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

##### Parameters

$dbHost : Database

  Database instance

$type : string

  Query type - 'select', 'insert', 'update' or 'delete'

$table : string|array<string|int, string> = null

  Tables to operate on - see Query->table().

##### Return values

mixed —

#### and_where()

Query.php : 674

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[, string|array<string|int, string> $value = null ][, string $op = '=' ][, bool $bind = true ]) : self`

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.

$value : string|array<string|int, string> = null

  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 = '='

  Condition operator: <, >, = etc

$bind : bool = true

  Escape the value (true, default) or not (false).

##### Return values

self —

#### bind()

Query.php : 267

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 : 186

Commit a transaction.

`public static commit(PDO $dbh) : mixed`

##### Parameters

$dbh : PDO

  The Database handle (typically a PDO object, but not always).

##### Return values

mixed —

#### connect()

Query.php : 201

Database connection - override by the database driver.

`public static connect(string|array<string|int, mixed> $user[, string $pass = '' ][, string $host = '' ][, mixed $port = '' ][, string $db = '' ][, mixed $dsn = '' ]) : Query`

##### Parameters

$user : string|array<string|int, mixed>

  User name or all parameters in an array

$pass : string = ''

  Password

$host : string = ''

  Host name

$port : mixed = ''
$db : string = ''

  Database name

$dsn : mixed = ''

##### Return values

Query —

#### database()

Query.php : 283

Get the Database host for this query instance.

`public database() : Database`

##### Return values

Database —

Database class instance

#### distinct()

Query.php : 296

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

`public distinct(bool $dis) : Query`

##### Parameters

$dis : bool

  Optional

##### Return values

Query —

#### exec()

Query.php : 310

Execute the query.

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

##### Parameters

$sql : string = null

  SQL string to execute (only if _type is 'raw').

##### Return values

Result —

#### get()

Query.php : 339

Get fields.

`public get(string|array<string|int, string> ...$get) : self`

##### Parameters

$get : string|array<string|int, string>

  Fields to get - can be specified as individual fields or an array of fields.

##### Return values

self —

#### group_by()

Query.php : 458

Group the results by the values in a field.

`public group_by(string $group_by) : self`

##### Parameters

$group_by : string

  The field of which the values are to be grouped

##### Return values

self —

#### join()

Query.php : 379

Perform a JOIN operation.

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

##### Parameters

$table : string

  Table name to do the JOIN on

$condition : string

  JOIN condition

$type : string = ''

  JOIN type

$bind : mixed = true

##### Return values

self —

#### left_join()

Query.php : 406

Add a left join, with common logic for handling binding or not.

`public left_join(mixed $joins) : mixed`

##### Parameters

$joins : mixed

##### Return values

mixed —

#### limit()

Query.php : 444

Limit the result set to a certain size.

`public limit(int $lim) : self`

##### Parameters

$lim : int

  The number of records to limit the result to.

##### Return values

self —

#### offset()

Query.php : 523

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

`public offset(int $off) : self`

##### Parameters

$off : int

  The number of records to offset the result by.

##### Return values

self —

#### or_where()

Query.php : 697

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

`public or_where(string|array<string|int, string>|callable $key[, string|array<string|int, string> $value = null ][, string $op = '=' ][, bool $bind = true ]) : self`

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.

$value : string|array<string|int, string> = null

  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 = '='

  Condition operator: <, >, = etc

$bind : bool = true

  Escape the value (true, default) or not (false).

##### Return values

self —

#### order()

Query.php : 539

Order by.

`public order(string|array<string|int, string> $order) : self`

##### Parameters

$order : string|array<string|int, string>

  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.

##### Return values

self —

#### pkey()

Query.php : 473

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 : 223

Rollback the database state to the start of the transaction.

`public static rollback(PDO $dbh) : mixed`

##### Parameters

$dbh : PDO

  The Database handle (typically a PDO object, but not always).

##### Return values

mixed —

#### set()

Query.php : 582

Set fields to a given value.

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

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.

$val : string = null

  When $set is given as a simple string, $set is the field name and this is the field's value.

$bind : bool = true

  Should the value be bound or not

##### Return values

self —

#### table()

Query.php : 493

Set table(s) to perform the query on.

`public table(string|array<string|int, string> ...$table) : self`

##### Parameters

$table : string|array<string|int, string>

  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.

##### Return values

self —

#### transaction()

Query.php : 213

Start a database transaction.

`public static transaction(PDO $dbh) : mixed`

##### Parameters

$dbh : PDO

  The Database handle (typically a PDO object, but not always).

##### Return values

mixed —

#### where()

Query.php : 636

Where query - multiple conditions are bound as ANDs.

`public where(string|array<string|int, string>|callable $key[, string|array<string|int, string> $value = null ][, string $op = '=' ][, bool $bind = true ]) : self`

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.

$value : string|array<string|int, string> = null

  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 = '='

  Condition operator: <, >, = etc

$bind : bool = true

  Escape the value (true, default) or not (false).

##### Return values

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' );
        } );

#### where_group()

Query.php : 744

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[, string $op = 'AND' ]) : self`

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.

$op : string = 'AND'

  Conditional operator to use to join to the preceding condition. Default `AND`.

##### Return values

self —

@example

    $query->where_group( function ($q) {
      $q->where( 'location', 'Edinburgh' );
      $q->where( 'position', 'Manager' );
    } );

#### where_in()

Query.php : 771

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

`public where_in(string $field, array<string|int, mixed> $arr[, string $operator = 'AND' ]) : self`

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

##### Parameters

$field : string

  Field name

$arr : array<string|int, mixed>

  Values

$operator : string = 'AND'

  Conditional operator to use to join to the preceding condition. Default `AND`.

##### Return values

self —

#### _escape_field()

Query.php : 1051

Escape quotes in a field identifier.

`protected _escape_field(mixed $field) : mixed`

@internal

##### Parameters

$field : mixed

##### Return values

mixed —

## Search results