Rebar Project - MySQL Engine
Public Member Functions | List of all members
SelectQuery Class Reference
Inheritance diagram for SelectQuery:
ReadQuery Query

Public Member Functions

 __construct ($selectfields, $tablenames, $left_joins=null, $right_joins=null, $parameters=null, $orders=null, $limits=false)
 
 setSelectFields ($selectfields)
 
 setTablenames ($tablenames)
 
 setLeftjoins ($left_joins)
 
 setRightjoins ($right_joins)
 
 setOrders ($orders)
 
 makeQuery (&$query_string, &$dtype_string)
 
- Public Member Functions inherited from Query
 setParameters ($parameters)
 
 enableLimits ($limits)
 
 makeQuery (&$query_string, &$dtype_string)
 

Additional Inherited Members

- Static Public Attributes inherited from Query
static $OPER_EQ = ' = '
 
static $OPER_NE = ' <> '
 
static $OPER_GT = ' > '
 
static $OPER_GE = ' >= '
 
static $OPER_LT = ' < '
 
static $OPER_LE = ' <= '
 
static $BOOL_AND = ' and '
 
static $BOOL_OR = ' or '
 
static $DTYPE_D = 'd'
 
static $DTYPE_I = 'i'
 
static $DTYPE_S = 's'
 
static $ORDER_A = ' asc'
 
static $ORDER_D = ' desc'
 
static $LOCK_R = ' read'
 
static $LOCK_W = ' write'
 
- Protected Member Functions inherited from Query
 makeParameterString ($parameters, &$query_string, &$dtype_string, $where=true)
 
 makeLimitString (&$query_string, &$dtype_string)
 

Detailed Description

SVN

Id
SelectQuery.php 14 2017-02-02 21:06:24Z phil
Author
Phil Riesch phil@.nosp@m.phil.nosp@m.riesc.nosp@m.h.co.nosp@m.m

LICENSE

The PHP Rebar Project Copyright 2017 Philip Riesch phil@.nosp@m.phil.nosp@m.riesc.nosp@m.h.co.nosp@m.m

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

– Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

– Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

– Neither the name of Philip J. Riesch nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL PHILIP J. RIESCH BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

DESCRIPTION

The SelectQuery allows for the construction of SQL Select queries that can then be executed on a target database.

This class supports a multitude of features, including left and right joins, parameters, orders, and limits.

Constructor & Destructor Documentation

SelectQuery::__construct (   $selectfields,
  $tablenames,
  $left_joins = null,
  $right_joins = null,
  $parameters = null,
  $orders = null,
  $limits = false 
)

Constructor. See affiliated setter functions for expanded descriptions as to the kinds of input that these arguments expect.

Parameters
$selectfieldsarray The fields to select from the table
$tablenamesstring|array The table(s) that the select should execute on
$left_joinsarray|null Additional tables that should be left joined
$right_joinsarray|null Additional tables that should be right joined
$parametersarray|null Parameters that should be used to determine which rows are selected
$ordersarray|null Controls the ordering of the query result
$limitsboolean Whether or not limits should be applied to the query result

Member Function Documentation

SelectQuery::makeQuery ( $query_string,
$dtype_string 
)

Used by the MysqlFacet class when it is executing the query.

Parameters
&$query_stringstring The generated SQL query
&$dtype_Stringstring The generated input data type string
SelectQuery::setLeftjoins (   $left_joins)

Left join one or more tables to this query

In general, the join mechanism operates on a few assumptions. We assume that you are always going to give an alias to the table that is joined, and we always assume that you are trying to match unique keys in each table.

In SQL, this would in general look a little like this – in this example it's a left join but this also applies for right joins:

... LEFT JOIN Othertable as T on T.KeyField = Mytable.KeyField ...

Defining a join then requires us to define three things:

– The joined table and it's alias – The joined table key to match – The target table key to match

Join functions then therefore expect an array of arrays: each of these arrays contains a join specification.

Inside of these arrays we expect three arrays, corresponding to our definition above as to the information we need to construct a join.

The first array needs the name of the table to join, and the alias to assign to it. The second array defines the left hand side of the key matching equation. And the third array defines the right hand side of this equation.

So our example above would look like the following input:

array(array( array('Othertable', 'T'), array('T', 'KeyField'), array('Mytable', 'KeyField') ))

Keep in mind that you would then use the second mode of SelectQuery::setSelectFields ... to obtain the joined table's fields you would specify it as array( ... , array('T', 'SomeField'), ... )

Parameters
$left_joinsarray The left join specification
SelectQuery::setOrders (   $orders)

If you wish for the returned results from your query to be in some kind of order (i.e. a SQL Order By clause), you would specify your ordering using this function.

Ordering is very simple, it is an array of arrays where each array has two values: the first value being the field to order, and the second value being a direction of the order, either Query::$ORDER_A for ascending or Query::$ORDER_D for descending.

Leftmost items in the array take priority over rightmost items in the array.

Parameters
$ordersarray The ordering specification to use
SelectQuery::setRightjoins (   $right_joins)

Right join one or more tables to this query.

See SelectQuery::setLeftjoins for an explaination of the format of the input that this function expects.

Parameters
$right_joinsarray The right join specification
SelectQuery::setSelectFields (   $selectfields)

Sets the fields in the table that should be selected.

There are two ways to specify field names. You can either specify them directly, which would be just an array of strings, or in the case that you have joined other tables to the query (by using SelectQuery::setLeftjoins or SelectQuery::setRightjoins as described below) then instead of a string you would pass an array containing arrays of two strings: the first string being the alias that you have assigned the joined table, and the field within that joined table that you are targeting.

Parameters
$selectfieldsarray The fields in the table to select
SelectQuery::setTablenames (   $tablenames)

Set the table (or tables) that you wish to query.

This can either be a single table – you can just pass a string – or if multiple tables are to be selected from then you would pass an array of strings.

Parameters
$tablenamesstring|array The tables to select from

The documentation for this class was generated from the following file: