Overview

Namespaces

  • DataTables
    • Database
    • Editor
    • Vendor

Classes

  • Query
  • Result
  • Overview
  • Namespace
  • Class

Class Query

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.

Namespace: DataTables\Database
Located at Database/Query.php
Methods summary
public
# __construct( DataTables\Database $dbHost, string $type, string|string[] $table = null )

Query instance constructor.

Query instance constructor.

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

Parameters

$dbHost
$db Database instance
$type
Query type - 'select', 'insert', 'update' or 'delete'
$table
Tables to operate on - see DataTables\Database\Query::table().
public static
# commit( PDO $dbh )

Commit a transaction.

Commit a transaction.

Parameters

$dbh
The Database handle (typically a PDO object, but not always).
public static DataTables\Database\Query
# connect( string|array $user, string $pass = '', string $host = '', string $port = '', $db = '', $dsn = '' )

Database connection - override by the database driver.

Database connection - override by the database driver.

Parameters

$user
User name or all parameters in an array
$pass
Password
$host
Host name
$port
$db Database name
$db
$dsn

Returns

DataTables\Database\Query
public static
# transaction( PDO $dbh )

Start a database transaction

Start a database transaction

Parameters

$dbh
The Database handle (typically a PDO object, but not always).
public static
# rollback( PDO $dbh )

Rollback the database state to the start of the transaction.

Rollback the database state to the start of the transaction.

Parameters

$dbh
The Database handle (typically a PDO object, but not always).
public DataTables\Database\Query
# bind( string $name, string $value, mixed $type = null )

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.

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.

Parameters

$name
Parameter name. This should include a leading colon
$value
Value to bind
$type

Data type. See the PHP PDO documentation: http://php.net/manual/en/pdo.constants.php

Returns

DataTables\Database\Query
public DataTable
# database( )

Get the Database host for this query instance

Get the Database host for this query instance

Returns

DataTable
Database class instance
public DataTables\Database\Query
# distinct( boolean $dis )

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

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

Parameters

$dis
Optional

Returns

DataTables\Database\Query
public DataTables\Database\Result
# exec( string $sql = null )

Execute the query.

Execute the query.

Parameters

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

Returns

DataTables\Database\Result
public DataTables\Database\Query
# get( string|string[] $get )

Get fields.

Get fields.

Parameters

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

Returns

DataTables\Database\Query
public DataTables\Database\Query
# join( string $table, string $condition, string $type = '' )

Perform a JOIN operation

Perform a JOIN operation

Parameters

$table
Table name to do the JOIN on
$condition
JOIN condition
$type
JOIN type

Returns

DataTables\Database\Query
public DataTables\Database\Query
# limit( integer $lim )

Limit the result set to a certain size.

Limit the result set to a certain size.

Parameters

$lim
The number of records to limit the result to.

Returns

DataTables\Database\Query
public DataTables\Database\Query
# group_by( string $group_by )

Group the results by the values in a field

Group the results by the values in a field

Parameters

$group_by
field of which the values are to be grouped

Returns

DataTables\Database\Query
public DataTables\Database\Query|string[]
# pkey( string[] $pkey = null )

Get / set the primary key column name(s) so they can be easily returned after an insert.

Get / set the primary key column name(s) so they can be easily returned after an insert.

Parameters

$pkey
Primary keys

Returns

DataTables\Database\Query|string[]
public DataTables\Database\Query
# table( string|string[] $table )

Set table(s) to perform the query on.

Set table(s) to perform the query on.

Parameters

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

Returns

DataTables\Database\Query
public DataTables\Database\Query
# offset( integer $off )

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

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

Parameters

$off
The number of records to offset the result by.

Returns

DataTables\Database\Query
public DataTables\Database\Query
# order( string|string[] $order )

Order by

Order by

Parameters

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

Returns

DataTables\Database\Query
public DataTables\Database\Query
# set( string|string[] $set, string $val = null, boolean $bind = true )

Set fields to a given value.

Set fields to a given value.

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

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

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

$bind
Should the value be bound or not

Returns

DataTables\Database\Query
public DataTables\Database\Query
# where( string|string[]|callable $key, string|string[] $value = null, string $op = "=", boolean $bind = true )

Where query - multiple conditions are bound as ANDs.

Where query - multiple conditions are bound as ANDs.

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

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

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
Condition operator: <, >, = etc
$bind
Escape the value (true, default) or not (false).

Returns

DataTables\Database\Query

Example

The following will produce 'WHERE name='allan' AND ( location='Scotland' OR location='Canada' ):

<pre><span class="php-var">$query</span>
    -&gt;where( <span class="php-quote">'name'</span>, <span class="php-quote">'allan'</span> )
    -&gt;where( <span class="php-keyword1">function</span> (<span class="php-var">$q</span>) {
      <span class="php-var">$q</span>-&gt;where( <span class="php-quote">'location'</span>, <span class="php-quote">'Scotland'</span> );
      <span class="php-var">$q</span>-&gt;where( <span class="php-quote">'location'</span>, <span class="php-quote">'Canada'</span> );
    } );</pre>

public DataTables\Database\Query
# and_where( string|string[]|callable $key, string|string[] $value = null, string $op = "=", boolean $bind = true )

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

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

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

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

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
Condition operator: <, >, = etc
$bind
Escape the value (true, default) or not (false).

Returns

DataTables\Database\Query
public DataTables\Database\Query
# or_where( string|string[]|callable $key, string|string[] $value = null, string $op = "=", boolean $bind = true )

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

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

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

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

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
Condition operator: <, >, = etc
$bind
Escape the value (true, default) or not (false).

Returns

DataTables\Database\Query
public DataTables\Database\Query
# where_group( boolean|callable $inOut, string $op = 'AND' )

Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.

Provide grouping for WHERE conditions. Use it with a callback function to automatically group any conditions applied inside the method.

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

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

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

Returns

DataTables\Database\Query

Example

$query->where_group( function ($q) {
      $q->where( 'location', 'Edinburgh' );
      $q->where( 'position', 'Manager' );
    } );
public DataTables\Database\Query
# where_in( string $field, array $arr, string $operator = "AND" )

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

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

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

Parameters

$field
name
$arr
Values
$operator

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

Returns

DataTables\Database\Query
Properties summary
protected boolean $_supportsAsAlias
# true
protected integer $_whereInCnt
# 1
DataTables Editor 1.9.4 - PHP libraries API documentation generated by ApiGen