Download the PHP package phossa/phossa-query without Composer

phossa-query

phossa-query is a SQL query builder library with concise syntax for PHP. It supports Mysql, SQLite, Postgres, Sql server, Oracle etc.

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

Features

• Support UPDATE, CREATE TABLE.

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

• Statement with positioned or named parameters.

• Beautiful output with different settings.

• Ongoing support for different dialects like Mysql, Sqlite 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 the query.

• SELECT

  • Columns

  Columns can be specified in the select() or in col() (or with its alias field()).

  Column with optional alias name,

  Multiple columns,

  Raw mode,

  Common functions like count(), min(), max(), avg(), sum() and sumDistinct() can be used in the columns.

  Generic functions by using func($template, $colName, $colAlias),

  • Distinct

  DISTINCT can be specified with distinct()

  • From

  FROM can used with builder object or select object.

  Use select(false) to ignore default table from the builder,

  Builder tables are carried over,

  Multiple tables (with aliases) supported,

  Subqueries can be used in from(),

  • Group by

  Single GROUP BY,

  Multiple groupBy() and raw mode can be used,

  • Join

  Join with another table with same column name

  Specify alias for the join table,

  Join table with different column name,

  Join with operator specified,

  Multiple joins,

  Subqueries in join,

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

  • Limit

  LIMIT and OFFSET are supported,

  Or use page($pageNum, $pageLength),

  • Order by

  Order by ASC or DESC

  Or raw mode

  • Where

  Simple wheres,

  Multiple wheres,

  Complex where,

  Raw mode,

  with NOT,

  Where IN and BETWEEN

  Where IS NULL and IS NOT NULL

  Exists,

  • Having

  Similar to WHERE clause,

  • Union

• 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,

  With Mysql extensions,

• REPLACE

  Mysql version of replace,

  Sqlite version of replace,

• DELETE

  Single table deletion,

  Multiple tables deletion

• CREATE TABLE

  Create table is used by most of the ORM libraries.

  With the following output,

Advanced topics

• expr()

  Expression can be used to construct complex WHERE

  Join with complex ON,

• raw()

  Raw string bypass the quoting and escaping,

  Raw string with parameters,

• before() and after()

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

• Parameters

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

  Or named parameters,

• Settings

  Settings can be applied to $builder at instantiation,

  Or applied when output with getStatement(),

  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.

Dialects

• Mysql

Dependencies

• PHP >= 5.4.0

• phossa/phossa-shared 1.*

License

MIT License