Download the PHP package phossa2/query without Composer

phossa2/query

phossa2/query is a SQL query builder library with concise syntax for PHP. It supports Mysql dialect and more coming.

It requires PHP 5.4, supports PHP 7.0+ and HHVM. It is compliant with PSR-1, PSR-2, PSR-3, PSR-4, and the proposed PSR-5.

Features

• Support UPDATE, DELETE.

• Complex sql building with raw(), before() etc.

• Statement with positioned or named parameters.

• Beautiful output with different settings.

• Ongoing support for different dialects like Mysql and more.

Installation

Install via the composer utility.

or add the following lines to your composer.json

Usage

• Getting started

  Start with a query builder first, then query.

• SELECT

  • Columns/fields

  Columns can be specified in the select($col, ...), col($col, $alias) or col(array $cols).

  Raw string can be provided using colRaw($string, array $parameters)

  Common functions like cnt($col, $alias), min($col, $alias), max($col, $alias), avg($col, $alias), sum($col, $alias) can also be used directly.

  Generic column template by using colTpl($template, $cols, $alias),

  Subquery can also be use in col(),

  • Distinct

  DISTINCT can be specified with distinct(...),

  • From

  from($table, $alias) or table($table, $alias) can be used with $builder object or query object such as $builder->select().

  Using table() to replace any existing tables,

  Using from() to append to any existing tables,

  Subqueries can be used in from() or table(),

  • Group

  Group result with group($col, ...),

  Multiple group() and groupRaw($str, array $params),

  Template can also be used with groupTpl($template, $cols),

  • Join

  Join using join($table, $col),

  Specify alias for the joined table,

  Join table with different column name,

  Join with operator specified,

  Multiple joins,

  Subqueries in join,

  Other joins leftJoin(), rightJoin(), outerJoin(), leftOuterJoin(), rightOuterJoin(), crossJoin() are supported. If want to use your own join, joinRaw() is handy.

  • Limit

  LIMIT and OFFSET are supported,

  Or use page($pageNum, $pageLength) where $pageNum starts from 1,

  • Order

  Order by ASC or DESC

  Or raw mode

  • Where

  Simple where clauses,

  Multiple wheres,

  Complex where,

  Raw mode,

  with NOT,

  Where IN and BETWEEN

  IS NULL,

  EXISTS,

  • Having

  Similar to WHERE clause,

  • Union

  union() or unionAll() can be used with builder or query object,

• INSERT

  Single insert statement,

  Multiple data rows,

  Insert with DEFAULT values

  Insert NULL instead of default values,

  Insert with SELECT subquery,

• UPDATE

  Common update statement,

  increment($col, $step) and decrement($col, $step),

  With Mysql extensions,

• REPLACE

  Mysql version of replace,

• DELETE

  Single table deletion,

  Multiple tables deletion

Advanced topics

• expr()

  Expression can be used to construct complex WHERE

  Join with complex ON,

• raw()

  Raw string to bypass the quoting and escaping,

  Raw string with positioned parameters,

• Template with colTpl(), groupTpl() etc.

  Using template will make quotation of db names possible,

• before(), after(), hint() and option()

  Sometimes, non-standard SQL wanted and no methods found. before() and after() will come to rescue.

  hint() add hints right after the statement word, and option() will append to the end of sql,

• Parameters

  phossa2/query can return statement for driver to prepare and use the getBindings() to get the values to bind.

  Or named parameters,

  Parameters can be applied to raw or template methods,

• Settings

  Settings can be applied to $builder during instantiation or using setSettings(),

  Or applied when output with getSql() or getStatement(),

  Indented sql,

  List of settings,

  • autoQuote: boolean. Quote db identifier or not.

  • positionedParam: boolean. Output with positioned parameter or not.

  • namedParam: boolean. Output with named parameter or not.

  • seperator: string, default to ' '. Seperator between clauses.

  • indent: string, default to ''. Indent prefix for clauses.

  • escapeFunction: callabel, default to null. Function used to quote and escape values.

  • useNullAsDefault: boolean.

Change log

Please see CHANGELOG from more information.

Testing

Contributing

Please see CONTRIBUTE for more information.

Dependencies

• PHP >= 5.4.0

• phossa2/shared >= 2.0.21

License

MIT License