Download the PHP package fyre/db without Composer

FyreDB

FyreDB is a free, open-source database library for PHP.

Table Of Contents

• Installation
• Methods
• Connections
  • MySQL
  • Postgres
  • Sqlite
• Queries
  • Delete
  • Insert
  • Insert From
  • Replace
  • Select
  • Update
  • Update Batch
• Results

Installation

Using Composer

In PHP:

Methods

Clear

Clear and close connections.

Get Config

Set a Connection config.

• $key is a string representing the Connection key.

Alternatively, if the $key argument is omitted an array containing all configurations will be returned.

Get Key

Get the key for a Connection instance.

• $connection is a Connection.

Has Config

Check if a Connection config exists.

• $key is a string representing the Connection key, and will default to ConnectionManager::DEFAULT.

Is Loaded

Check if a Connection instance is loaded.

• $key is a string representing the Connection key, and will default to ConnectionManager::DEFAULT.

Load

Load a Connection.

• $options is an array containing configuration options.

Set Config

Set the Connection config.

• $key is a string representing the Connection key.
• $options is an array containing configuration options.

Alternatively, a single array can be provided containing key/value of configuration options.

Unload

Unload a Connection.

• $key is a string representing the Connection key, and will default to ConnectionManager::DEFAULT.

Use

Load a shared Connection instance.

• $key is a string representing the Connection key, and will default to ConnectionManager::DEFAULT.

Connections

You can load a specific connection handler by specifying the className option of the $options variable above.

Custom connection handlers can be created by extending \Fyre\DB\Connection, ensuring all below methods are implemented.

Custom handlers should also implement a generator method that returns a new QueryGenerator (if required) and a resultSetClass static method that returns the class name to use for results.

Affected Rows

Get the number of affected rows.

After Commit

Queue a callback to execute after the transaction is committed.

• $callback is a Closure.
• $priority is a number representing the callback priority, and will default to 1.
• $key is a string representing a unique identifier for the callback, and will default to null.

The callback will be executed immediately if there is no active transaction.

Begin

Begin a transaction.

Commit

Commit a transaction.

Connect

Connect to the database.

This method is called automatically when the Connection is created.

Delete

Create a DeleteQuery.

• $alias is a string or array containing the table aliases to delete, and will default to null.

Disconnect

Disconnect from the database.

Execute

Execute a SQL query with bound parameters.

• $sql is a string representing the SQL query.
• $params is an array containing the bound parameters.

The SQL query can use either ? as a placeholder (for numerically indexed parameters), or the array key prefixed with :.

This method will return a ResultSet for SELECT queries. Other query types will return a boolean value.

Get Charset

Get the connection character set.

Get Error

Get the last connection error.

Get Savepoint Level

Get the transaction save point level.

In Transaction

Determine if a transaction is in progress.

Insert

Create an InsertQuery.

Insert From

Create an InsertFromQuery.

• $from is a Closure, SelectQuery, QueryLiteral or string representing the query.
• $columns is an array of column names.

Insert ID

Get the last inserted ID.

When performing bulk inserts, this method will return the first ID for Sqlite.

Literal

Create a QueryLiteral.

• $string is a string representing the literal string.

Query

Execute a SQL query.

• $sql is a string representing the SQL query.

This method will return a ResultSet for SELECT queries. Other query types will return a boolean value.

Quote

Quote a string for use in SQL queries.

• $value is a string representing the value to quote.

Replace

Create a ReplaceQuery.

This method is only supported for queries using a MysqlConnection or SqliteConnection.

Rollback

Rollback a transaction.

Select

Create a SelectQuery.

• $fields is an array or string representing the fields to select, and will default to "*".

Non-numeric array keys will be used as field aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Set Charset

Set the connection character set.

• $charset is a string representing the connection character set.

Transactional

Execute a callback inside a database transaction.

• $callback is a Closure that will be executed inside the transaction.

If the callback returns false or throws an Exception the transaction will be rolled back, otherwise it will be committed.

Update

Create an UpdateQuery.

• $table is an array or string representing the table(s).

Non-numeric array keys will be used as table aliases.

Update Batch

Create an UpdateBatchQuery.

• $table is an array or string representing the table(s).

Non-numeric array keys will be used as table aliases.

Version

Get the server version.

MySQL

The MySQL connection can be loaded using custom configuration.

• $key is a string representing the connection key.
• $options is an array containing configuration options.
  • className must be set to \Fyre\DB\Handlers\Mysql\MysqlConnection.
  • host is a string representing the MySQL host, and will default to "127.0.0.1".
  • username is a string representing the MySQL username.
  • password is a string representing the MySQL password.
  • database is a string representing the MySQL database.
  • port is a number indicating the MySQL port, and will default to 3306.
  • collation is a string representing the collation, and will default to "utf8mb4_unicode_ci".
  • charset is a string representing the character set, and will default to "utf8mb4".
  • compress is a boolean indicating whether to enable compression, and will default to false.
  • persist is a boolean indicating whether to use a persistent connection, and will default to false.
  • timeout is a number indicating the connection timeout.
  • ssl is an array containing SSL options.
    • key is a string representing the path to the key file.
    • cert is a string representing the path to the certificate file.
    • ca is a string representing the path to the certificate authority file.
    • capath is a string representing the path to a directory containing CA certificates.
    • cipher is a string representing a list of allowable ciphers to use for encryption.
  • flags is an array containing PDO connection options.

Get Collation

Get the connection collation.

Postgres

The Postgres connection can be loaded using custom configuration.

• $key is a string representing the connection key.
• $options is an array containing configuration options.
  • className must be set to \Fyre\DB\Handlers\Postgres\PostgresConnection.
  • host is a string representing the Postgres host, and will default to "127.0.0.1".
  • username is a string representing the Postgres username.
  • password is a string representing the Postgres password.
  • database is a string representing the Postgres database.
  • port is a number indicating the Postgres port, and will default to 5432.
  • charset is a string representing the character set, and will default to "utf8".
  • schema is a string representing the character set, and will default to "public".
  • persist is a boolean indicating whether to use a persistent connection, and will default to false.
  • timeout is a number indicating the connection timeout.
  • flags is an array containing PDO connection options.

Set Schema

Set the connection schema.

• $schema is a string representing the connection schema.

Sqlite

The Sqlite connection can be loaded using custom configuration.

• $key is a string representing the connection key.
• $options is an array containing configuration options.
  • className must be set to \Fyre\DB\Handlers\Sqlite\SqliteConnection.
  • database is a string representing the Sqlite database file, and will default to ":memory:".
  • mask is a number indicating the database file permissions, and will default to 0644.
  • cache is a string representing the cache flag.
  • mode is a string representing the mode flag.
  • persist is a boolean indicating whether to use a persistent connection, and will default to false.
  • flags is an array containing PDO connection options.

Queries

The \Fyre\DB\Query class provides base methods related to building queries, and is extended by the query type classes below.

Execute

Execute the query.

This method will return a ResultSet for SELECT queries. Other query types will return a boolean value.

Connection

Get the Connection.

Get Table

Get the table(s).

Sql

Generate the SQL query.

Table

Set the table(s).

• $table is an array or string representing the table(s).
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Non-numeric array keys will be used as table aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection.

Delete

The \Fyre\DB\Queries\DeleteQuery class extends the Query class, while providing additional methods for executing DELETE queries.

Multiple tables is only supported for queries using a MysqlConnection.

Alias

Set the delete alias(es).

• $alias is a string or array containing the table aliases to delete, and will default to null.
• $overwrite is a boolean indicating whether to overwrite existing aliases, and will default to false.

This method is only supported for queries using a MysqlConnection.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

From

Set the FROM table(s).

• $table is an array or string representing the table(s).
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Non-numeric array keys will be used as table aliases.

Get Alias

Get the delete alias(es).

Get Epilog

Get the epilog.

Get From

Get the FROM table(s).

Get Join

Get the JOIN tables.

Get Limit

Get the LIMIT clause.

Get Order By

Get the ORDER BY fields.

Get Using

Get the USING tables.

Get Where

Get the WHERE conditions.

Join

Set the JOIN tables.

• $joins is a 2-dimensional array of joins.
• $overwrite is a boolean indicating whether to overwrite existing joins, and will default to false.

Each join array can contain a table, alias, type and an array of conditions. If the type is not specified it will default to INNER.

This method is only supported for queries using a MysqlConnection.

Limit

Set the LIMIT clause.

• $limit is a number indicating the query limit.

Order By

Set the ORDER BY fields.

• $fields is an array or string representing the fields to order by.
• $overwrite is a boolean indicating whether to overwrite existing fields, and will default to false.

Using

Set the USING tables.

• $table is an array or string representing the using table(s).
• $overwrite is a boolean indicating whether to overwrite existing using tables, and will default to false.

This method is only supported for queries using a PostgresConnection.

Where

Set the WHERE conditions.

• $conditions is an array or string representing the where conditions.
• $overwrite is a boolean indicating whether to overwrite existing conditions, and will default to false.

Array conditions can contain:

• Literal values with numeric keys.
• Key/value pairs where the key is the field (and comparison operator) and the value(s) will be escaped.
• Array values containing a group of conditions. These will be joined using the AND operator unless the array key is "OR" or "NOT".

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Insert

The \Fyre\DB\Queries\InsertQuery class extends the Query class, while providing additional methods for executing INSERT queries.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

Get Epilog

Get the epilog.

Get Into

Get the INTO table.

Get Values

Get the REPLACE data.

Into

Set the INTO table.

• $table is a string representing the table.
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Values

• $values is a 2-dimensional array of values to insert.
• $overwrite is a boolean indicating whether to overwrite existing data, and will default to false.

Array keys will be used for the column names, and the values will be escaped automatically.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Insert From

The \Fyre\DB\Queries\InsertFromQuery class extends the Query class, while providing additional methods for executing INSERT queries from SELECT queries.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

Get Epilog

Get the epilog.

Get Into

Get the INTO table.

Into

Set the INTO table.

• $table is a string representing the table.
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Replace

The \Fyre\DB\Queries\ReplaceQuery class extends the Query class, while providing additional methods for executing REPLACE queries.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

Get Epilog

Get the epilog.

Get Into

Get the INTO table.

Get Values

Get the REPLACE data.

Into

Set the INTO table.

• $table is a string representing the table.
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Values

• $values is a 2-dimensional array of values to insert.
• $overwrite is a boolean indicating whether to overwrite existing data, and will default to false.

Array keys will be used for the column names, and the values will be escaped automatically.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Select

The \Fyre\DB\Queries\SelectQuery class extends the Query class, while providing additional methods for executing SELECT queries.

Distinct

Set the DISTINCT clause.

• $distinct is a boolean indicating whether to set the query as DISTINCT, and will default to true.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

Except

Add an EXCEPT query.

• $union is a Closure, SelectQuery, QueryLiteral or string representing the query.
• $overwrite is a boolean indicating whether to overwrite existing unions, and will default to false.

From

Set the FROM table(s).

• $table is an array or string representing the table(s).
• $overwrite is a boolean indicating whether to overwrite existing tables, and will default to false.

Non-numeric array keys will be used as table aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection.

Get Distinct

Get the DISTINCT clause.

Get Epilog

Get the epilog.

Get Group By

Get the GROUP BY fields.

Get From

Get the FROM table(s).

Get Having

Get the HAVING conditions.

Get Join

Get the JOIN tables.

Get Limit

Get the LIMIT clause.

Get Offset

Get the OFFSET clause.

Get Order By

Get the ORDER BY fields.

Get Select

Get the SELECT fields.

Get Union

Get the UNION queries.

Get Where

Get the WHERE conditions.

Get With

Get the WITH queries.

Group By

Set the GROUP BY fields.

• $fields is an array or string representing the fields to group by.
• $overwrite is a boolean indicating whether to overwrite existing fields, and will default to false.

Having

Set the HAVING conditions.

• $conditions is an array or string representing the having conditions.
• $overwrite is a boolean indicating whether to overwrite existing conditions, and will default to false.

Array conditions can contain:

• Literal values with numeric keys.
• Key/value pairs where the key is the field (and comparison operator) and the value(s) will be escaped automatically.
• Array values containing a group of conditions. These will be joined using the AND operator unless the array key is "OR" or "NOT".

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Intersect

Add an INTERSECT query.

• $union is a Closure, SelectQuery, QueryLiteral or string representing the query.
• $overwrite is a boolean indicating whether to overwrite existing unions, and will default to false.

Join

Set the JOIN tables.

• $joins is a 2-dimensional array of joins.
• $overwrite is a boolean indicating whether to overwrite existing joins, and will default to false.

Each join array can contain a table, alias, type and an array of conditions. If the type is not specified it will default to INNER.

Limit

Set the LIMIT and OFFSET clauses.

• $limit is a number indicating the query limit.
• $offset is a number indicating the query offset.

Offset

Set the OFFSET clause.

• $offset is a number indicating the query offset.

Order By

Set the ORDER BY fields.

• $fields is an array or string representing the fields to order by.
• $overwrite is a boolean indicating whether to overwrite existing fields, and will default to false.

Select

Set the SELECT fields.

• $fields is an array or string representing the fields to select, and will default to "*".
• $overwrite is a boolean indicating whether to overwrite existing fields, and will default to false.

Non-numeric array keys will be used as field aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Union

Add a UNION DISTINCT query.

• $union is a Closure, SelectQuery, QueryLiteral or string representing the query.
• $overwrite is a boolean indicating whether to overwrite existing unions, and will default to false.

Union All

Add a UNION ALL query.

• $union is a Closure, SelectQuery, QueryLiteral or string representing the query.
• $overwrite is a boolean indicating whether to overwrite existing unions, and will default to false.

Where

Set the WHERE conditions.

• $conditions is an array or string representing the where conditions.
• $overwrite is a boolean indicating whether to overwrite existing conditions, and will default to false.

Array conditions can contain:

• Literal values with numeric keys.
• Key/value pairs where the key is the field (and comparison operator) and the value(s) will be escaped.
• Array values containing a group of conditions. These will be joined using the AND operator unless the array key is "OR" or "NOT".

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

With

Set the WITH clause.

• $with is an array of common table expressions.
• $overwrite is a boolean indicating whether to overwrite existing expressions, and will default to false.

Array keys will be used as table aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection.

With Recursive

Set the WITH RECURSIVE clause.

• $with is an array of common table expressions.
• $overwrite is a boolean indicating whether to overwrite existing expressions, and will default to false.

Array keys will be used as table aliases.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection.

Update

The \Fyre\DB\Queries\UpdateQuery class extends the Query class, while providing additional methods for executing UPDATE queries.

Multiple tables is only supported for queries using a MysqlConnection.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

From

Set the FROM tables.

• $table is an array or string representing the from table(s).
• $overwrite is a boolean indicating whether to overwrite existing from tables, and will default to false.

This method is only supported for queries using a PostgresConnection or SqliteConnection.

Get Data

Get the UPDATE data.

Get Epilog

Get the epilog.

Get From

Get the FROM table(s).

Get Join

Get the JOIN tables.

Get Where

Get the WHERE conditions.

Join

Set the JOIN tables.

• $joins is a 2-dimensional array of joins.
• $overwrite is a boolean indicating whether to overwrite existing joins, and will default to false.

Each join array can contain a table, alias, type and an array of conditions. If the type is not specified it will default to INNER.

This method is only supported for queries using a MysqlConnection.

Set

• $data is an array of values to update.
• $overwrite is a boolean indicating whether to overwrite existing data, and will default to false.

Array keys will be used for the column names, and the values will be escaped automatically.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Where

Set the WHERE conditions.

• $conditions is an array or string representing the where conditions.
• $overwrite is a boolean indicating whether to overwrite existing conditions, and will default to false.

Array conditions can contain:

• Literal values with numeric keys.
• Key/value pairs where the key is the field (and comparison operator) and the value(s) will be escaped.
• Array values containing a group of conditions. These will be joined using the AND operator unless the array key is "OR" or "NOT".

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Update Batch

The \Fyre\DB\Queries\UpdateBatchQuery class extends the Query class, while providing additional methods for executing batch UPDATE queries.

Epilog

Set the epilog.

• $epilog is a string representing the epilog for the query.

Get Data

Get the UPDATE data.

Get Epilog

Get the epilog.

Set

• $data is a 2-dimensional array of values to update.
• $keys is a string or array containing the keys to use for updating.
• $overwrite is a boolean indicating whether to overwrite existing data, and will default to false.

Array keys will be used for the column names, and the values will be escaped automatically.

If a SelectQuery or QueryLiteral is supplied as an array value they will be converted to a string and not escaped.

A Closure can also be supplied as an array value, where the first argument will be the Connection and the second argument will be the ValueBinder.

Results

SELECT queries will return a new ResultSet containing the results of the query.

The ResultSet is an implementation of an Iterator and can be used in a foreach loop.

All

Get the results as an array.

Clear Buffer

Clear the results from the buffer.

• $index is a number representing the index of the result to clear.

Alternatively, if the $index argument is omitted, the entire buffer will be cleared.

Column Count

Get the column count.

Columns

Get the result columns.

Count

Get the result count.

Fetch

Get a result by index.

• $index is a number indicating the row index.

First

Get the first result.

Free

Free the result from memory.

Get Type

Get the Type parser for a column.

• $name is a string representing the column name.

Last

Get the last result.