Editor PHP 2.3.2

Query
in package

AbstractYes

Perform an individual query on the database.

The typical pattern for using this class is through the 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

Properties

$_supportsAsAlias  : mixed
$_whereInCnt  : mixed

Methods

__construct()  : mixed
Query instance constructor.
and_where()  : $this
Add addition where conditions to the query with an AND operator. An alias of `where` for naming consistency.
bind()  : $this
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()  : PDO
Database connection - override by the database driver.
database()  : Database
Get the Database host for this query instance.
distinct()  : $this
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()  : $this
Get fields.
group_by()  : $this
Group the results by the values in a field.
join()  : $this
Perform a JOIN operation.
left_join()  : $this
Add a left join, with common logic for handling binding or not.
limit()  : $this
Limit the result set to a certain size.
offset()  : $this
Offset the return set by a given number of records (useful for paging).
or_where()  : $this
Add addition where conditions to the query with an OR operator.
order()  : $this
Order by.
pkey()  : mixed
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()  : $this
Set fields to a given value.
table()  : $this
Set table(s) to perform the query on.
transaction()  : mixed
Start a database transaction.
where()  : $this
Where query - multiple conditions are bound as ANDs.
where_group()  : $this
Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.
where_in()  : $this
Provide a method that can be used to perform a `WHERE ... IN (...)` query with bound values and parameters.

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

and_where()

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

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

Can be used in two different ways, as where( field, value ) or as an array of conditions to use: where( ['fieldName', ...], ['value', ...] );

Parameters
$key : mixed
$value : string|int|array<string|int, string>|array<string|int, int> = 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
$this

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 ]) : $this
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
$this

commit()

Commit a transaction.

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

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

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 = '' ]) : PDO
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
PDO

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

Optional

Return values
$this

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) : $this
Parameters
$get : string|array<string|int, string>

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

Return values
$this

group_by()

Group the results by the values in a field.

public group_by(string $group_by) : $this
Parameters
$group_by : string

The field of which the values are to be grouped

Return values
$this

join()

Perform a JOIN operation.

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

Table name to do the JOIN on

$condition : string

JOIN condition

$type : string = ''

JOIN type

$bind : mixed = true
Return values
$this

left_join()

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

public left_join(mixed $joins) : $this
Parameters
$joins : mixed
Return values
$this

limit()

Limit the result set to a certain size.

public limit(int $lim) : $this
Parameters
$lim : int

The number of records to limit the result to.

Return values
$this

offset()

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

public offset(int $off) : $this
Parameters
$off : int

The number of records to offset the result by.

Return values
$this

or_where()

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

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

Can be used in two different ways, as where( field, value ) or as an array of conditions to use: where( ['fieldName', ...], ['value', ...] );

Parameters
$key : mixed
$value : string|int|array<string|int, string>|array<string|int, int> = 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
$this

order()

Order by.

public order(string|array<string|int, string> $order) : $this
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
$this

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 ]) : mixed
Parameters
$pkey : array<string|int, string> = null

Primary keys

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

set()

Set fields to a given value.

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

Can be used in two different ways, as set( field, value ) or as an array of fields to set: set( [ '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
$this

table()

Set table(s) to perform the query on.

public table(string|array<string|int, string> $table) : $this
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
$this

transaction()

Start a database transaction.

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

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

where()

Where query - multiple conditions are bound as ANDs.

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

Can be used in two different ways, as where( field, value ) or as an array of conditions to use: where( ['fieldName', ...], ['value', ...] );

Parameters
$key : mixed
$value : string|int|array<string|int, string>|array<string|int, int> = 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).

Tags
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' );
    } );
Return values
$this

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(mixed $inOut[, string $op = 'AND' ]) : $this

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 : mixed
$op : string = 'AND'

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

Tags
example 
$query->where_group( function ($q) {
  $q->where( 'location', 'Edinburgh' );
  $q->where( 'position', 'Manager' );
} );
Return values
$this

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' ]) : $this

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
$this 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results