Editor PHP 2.0.5

MysqlQuery extends Query

MySQL driver for DataTables Database Query class

@internal

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
_exec()  : mixed
_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[, mixed $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 : mixed = ''

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

_exec()

protected _exec() : mixed
Return values
mixed —

_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