Editor PHP 2.2.2

Query
in package

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

protected mixed $_supportsAsAlias = true

$_whereInCnt

protected mixed $_whereInCnt = 1

Methods

__construct()

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

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

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(PDO $dbh) : mixed
Parameters
$dbh : PDO

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

Return values
mixed —

connect()

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

Get the Database host for this query instance.

public database() : Database
Return values
Database —

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(bool $dis) : Query
Parameters
$dis : bool

Optional

Return values
Query —

exec()

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

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

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

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

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

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

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

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

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

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(PDO $dbh) : mixed
Parameters
$dbh : PDO

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

Return values
mixed —

set()

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

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

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

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

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

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

Escape quotes in a field identifier.

protected _escape_field(mixed $field) : mixed

@internal

Parameters
$field : mixed
Return values
mixed — 

        

        
    




    

    

                

    

        

            
Search results