Overview

Namespaces

  • None
  • PHP

Classes

  • AbstractTest
  • AMysql
  • AMysql_Abstract
  • AMysql_Expr
  • AMysql_Iterator
  • AMysql_Profiler
  • AMysql_Select
  • AMysql_Statement
  • AMysql_TestCase
  • ExceptionTest
  • ExprTest
  • IteratorTest
  • SelectTest
  • StatementTest

Exceptions

  • AMysql_Exception
  • Overview
  • Namespace
  • Class
  • Tree

Class AMysql_Select

The class is for helping with the assembly of a mysqli SELECT string. Rather than having to manually the string - especially of not only the values of certain parameters would be dynamic, but the tables, columns, joins, wheres and everything else is - you can get a new instance of this class by calling $amyql->select(), and you can freely add fragments to the SELECT query with the different methods of this class. When you call ->execute(), the final, but unbound SQL string is automatically prepared for binding with the passed (or pre-set) binds and execution.

Since the bindings are applied after the SELECT sql string is assembled, you are encouraged to use :named placeholders for WHEREs and anything similar, binding the values with ->bindValue() each time, or collecting all the binds to use in the end and passing it to ->execute().

Please check @link tests/AMysql/SelectTest.php for examples.

AMysql_Statement implements IteratorAggregate, Countable
Extended by AMysql_Select
License: License; http://www.opensource.org/licenses/mit-license.php
Author: Szerémi Attila
Located at AMysql/Select.php
Methods summary
public AMysql_Select
# option( string $selectOption )

Adds a select option.

Adds a select option.

e.g. SQL_CALC_FOUND_ROWS DISTINCT

Parameters

$selectOption
string
$selectOption The select option.

Returns

AMysql_Select
(chainable)
public string
# formatSelectColumn( string $columnName, string $alias = null )

Formats a column name and an optional alias to form columnName AS alias. The alias is automatically not taken into account if it's numeric. No need to worry about escaping the select all star character '*'.

Formats a column name and an optional alias to form columnName AS alias. The alias is automatically not taken into account if it's numeric. No need to worry about escaping the select all star character '*'.

Parameters

$columnName
string
$columnName The column name.
$alias
string
$alias (Optional) the alias to select AS

Returns

string
public AMysql_Select
# column( string|AMysql_Expr|array $columns, array $options = array () )

Adds one or more COLUMN to the list of columns to select.

Adds one or more COLUMN to the list of columns to select.

Parameters

$columns
string|AMysql_Expr|array
$columns The column name. Can be an array of column names in which case the key can mark the optional alias of the column.
$options
array
$options (Options) an array of config options columnPrefix - prefix each column with this table prefix

Returns

AMysql_Select
(chainable)
public AMysql_Select
# columnLiteral( string $columnLiteral )

Add this literal string between select options and columns.

Add this literal string between select options and columns.

Parameters

$columnLiteral
string
$columnLiteral Literal string

Returns

AMysql_Select
(chainable)
public string
# formatFrom( string $tableName, string $alias = null )

Formats a table name and an optional alias to form tableName AS alias. The alias is automatically not taken into account if it's numeric.

Formats a table name and an optional alias to form tableName AS alias. The alias is automatically not taken into account if it's numeric.

Parameters

$tableName
string
$tableName The table name.
$alias
string
$alias (Optional) the alias to select AS

Returns

string
public AMysql_Select
# from( string|AMysql_Expr|array $tables, array $columns = array () )
  1. Adds one or more table name to the list of tables to select FROM.
  1. Adds one or more table name to the list of tables to select FROM.
  2. Alternatively, if you only select from 1 table here, you can supply an array

of columns to select, having them automatically prefixed to the needed prefix of the table selected from. Similar to Zend Framework 1.

e.g. ->from(array('p' => 'products'), array('product_id', 'product_name'));// Build this query: // results in: SELECT p."product_id", p."product_name" FROM "products" AS p

You can use literals as table names with AMysql_Expr.

Parameters

$tables
string|AMysql_Expr|array
$tables The table name. Can be an array or table names in which case the key can mark the optional alias of the table.
$columns
array
$columns (Optional) The columns from this table to select. Do not use if you are selecting from more than 1 tables!

Returns

AMysql_Select
(chainable)
public AMysql_Select
# join( string $type, string $table, string $on, array $columns = array (), boolean $prepend = false )

Adds a JOIN

Adds a JOIN

Parameters

$type
string
$type Type of join. 'left' would be LEFT JOIN, 'inner' would be INNER JOIN. Leaving this falsy will result in a normal JOIN.
$table
string
$table The table name to join. Can be a 1 element array of ['alias' => 'tableName']
$on
string
$on The ON clause unbound.
$columns
array
$columns (Optional) The columns from this table to select. TODO!
$prepend
boolean
$prepend (Optional) whether to prepend this JOIN to the other joins. Default: false (append).

Returns

AMysql_Select
(chainable)
public AMysql_Select
# where( string $where )

Adds a WHERE fragment. All fragments are joined by an AND at the end. WARNING: When binding a WHERE part that is an AMysql_Expr, you shouldn't pass it to this method. You should instead pass a new bind string (e.g. :wherePart) and then make a bind to the expression using "wherePart" as the key. If you ignore this and the expression contains any bind-related substrings, unexpected results will happen when placeholders are bound. Note that this object cannot automatically place it in for you as a bind due to not knowing whether named or unnamed binds are being used.

Adds a WHERE fragment. All fragments are joined by an AND at the end. WARNING: When binding a WHERE part that is an AMysql_Expr, you shouldn't pass it to this method. You should instead pass a new bind string (e.g. :wherePart) and then make a bind to the expression using "wherePart" as the key. If you ignore this and the expression contains any bind-related substrings, unexpected results will happen when placeholders are bound. Note that this object cannot automatically place it in for you as a bind due to not knowing whether named or unnamed binds are being used.

Parameters

$where
string
$where Unbound WHERE fragment

Returns

AMysql_Select
(chainable)
public AMysql_Select
# whereBind( string $where, mixed $key, mixed $val )

Syntactic sugar for $this->where($where)->bindValue($key, $val);

Syntactic sugar for $this->where($where)->bindValue($key, $val);

Usage e.g. $select->whereBind('id = :id', 'id', 3)

Parameters

$where
string
$where Unbound WHERE fragment
$key
mixed
$key @see AMysql_Statement::bindValue()
$val
mixed
$val @see AMysql_Statement::bindValue()

Returns

AMysql_Select
(chainable)
public AMysql_Select
# groupBy( name $col, boolean $desc = false, boolean $prepend = false )

Adds an GROUP BY parameter

Adds an GROUP BY parameter

Parameters

$col
name
$col Column name
$desc
boolean
$desc (Optional) Whether to sort DESC. Default: false
$prepend
boolean
$prepend (Optional) Whether to prepend this parameter. Default: FALSE

Returns

AMysql_Select
(chainable)
public AMysql_Select
# groupByLiteral( name $what, boolean $prepend = false )

Adds an GROUP BY parameter with no escaping.

Adds an GROUP BY parameter with no escaping.

Parameters

$what
name
$col What to group by. Can list multiple literals separated by commas.
$prepend
boolean
$prepend (Optional) Whether to prepend this parameter. Default: FALSE

Returns

AMysql_Select
(chainable)
public AMysql_Select
# having( string $having )

Adds a HAVING fragment. All fragments are joined by an AND at the end.

Adds a HAVING fragment. All fragments are joined by an AND at the end.

Parameters

$having
string
$having Unbound HAVING fragment

Returns

AMysql_Select
(chainable)
public AMysql_Select
# orderBy( name $col, boolean $desc = false, boolean $prepend = false )

Adds an ORDER BY parameter

Adds an ORDER BY parameter

Parameters

$col
name
$col Column name
$desc
boolean
$desc (Optional) Whether to sort DESC. Default: false
$prepend
boolean
$prepend (Optional) Whether to prepend this parameter. Default: FALSE

Returns

AMysql_Select
(chainable)
public AMysql_Select
# orderByLiteral( name $what, boolean $prepend = false )

Adds an ORDER BY parameter with no escaping.

Adds an ORDER BY parameter with no escaping.

Parameters

$what
name
$col What to order by. Can list multiple literals separated by commas.
$prepend
boolean
$prepend (Optional) Whether to prepend this parameter. Default: FALSE

Returns

AMysql_Select
(chainable)
public AMysql_Select
# limit( integer $limit )

Adds a LIMIT You can only limit the number of rows returned here (e.g. LIMIT 10). To set the offset (e.g. LIMIT 20, 10), you must do: $select->limit(10)->offset(20);

Adds a LIMIT You can only limit the number of rows returned here (e.g. LIMIT 10). To set the offset (e.g. LIMIT 20, 10), you must do: $select->limit(10)->offset(20);

Parameters

$limit
integer
$limit The LIMIT

Returns

AMysql_Select
(chainable)
public AMysql_Select
# offset( integer $offset )

Adds an OFFSET

Adds an OFFSET

Parameters

$offset
integer
$offset The OFFSET

Returns

AMysql_Select
(chainable)
public string
# getSql( string $prepared = null )

Gets the full, bound SQL string ready for use with MySQL.

Gets the full, bound SQL string ready for use with MySQL.

Parameters

$prepared
string
$prepared (Optional) Only for parent compatibility.

Returns

string

Overrides

AMysql_Statement::getSql()
public string
# getUnboundSql( )

Gets the SQL string without the binds applied yet.

Gets the SQL string without the binds applied yet.

Returns

string
Methods inherited from AMysql_Statement
__construct(), __destruct(), __set(), _query(), _replaceCallback(), addBinds(), affectedRows(), appendPrepare(), bindParam(), bindValue(), buildColumnsValues(), buildSet(), count(), delete(), errorHandlerCallback(), escapeIdentifier(), execute(), fetch(), fetchAll(), fetchAllAssoc(), fetchAllColumn(), fetchAllColumns(), fetchArray(), fetchAssoc(), fetchNum(), fetchObject(), fetchPairs(), fetchRow(), freeResults(), getIterator(), getLink(), handleError(), handleException(), insert(), insertId(), insertReplace(), isMysqli(), numRows(), pairUp(), prepare(), query(), quoteInto(), replace(), reportErrorException(), result(), resultDefault(), resultInt(), resultNull(), setBinds(), setFetchMode(), throwException(), update()
Constants inherited from AMysql_Statement
CODE_QUERY_NOT_SUCCESSFUL
Properties summary
protected array $selectOptions array ()
#
protected mixed $columnLiteral
#
protected array $columns array ()
#
protected array $froms array ()
#
protected array $joins array ()
#
protected array $wheres array ()
#
protected array $groupBys array ()
#
protected array $havings array ()
#
protected array $orderBys array ()
#
protected mixed $limit null
#
protected mixed $offset null
#
Properties inherited from AMysql_Statement
$_executed, $_fetchMode, $_fetchModeExtraArgs, $_replacements, $affectedRows, $amysql, $beforeSql, $binds, $errno, $error, $insertId, $isMysqli, $lastException, $link, $prepared, $query, $queryTime, $result, $results, $throwExceptions
API documentation generated by ApiGen 2.8.0