FyreDB is a free, database library for PHP.

• Installation
• Methods
• Connections
  • MySQL
• Queries
• Results

Using Composer

composer require fyre/db

In PHP:

use Fyre\DB\ConnectionManager;

Clear

Clear and close connections.

ConnectionManager::clear();

Get Config

Set a connection config.

• $key is a string representing the connection key.

$config = ConnectionManager::getConfig($key);

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

$config = ConnectionManager::getConfig();

Get Key

Get the key for a connection instance.

• $connection is a Connection.

$key = ConnectionManager::getKey($connection);

Load

Load a connection.

• $options is an array containing configuration options.

$connection = ConnectionManager::load($options);

Set Config

Set the connection config.

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

ConnectionManager::setConfig($key, $options);

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

ConnectionManager::setConfig($config);

Unload

Unload a connection.

• $key is a string representing the connection key, and will default to "default".

ConnectionManager::unload($key);

Use

Load a shared connection instance.

• $key is a string representing the connection key, and will default to "default".

$connection = ConnectionManager::use($key);

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 results method that returns a new ResultSet and a generator method that returns a new QueryGenerator (if required).

Affected Rows

Get the number of affected rows.

$affectedRows = $connection->affectedRows();

Begin

Begin a transaction.

$connection->begin();

Builder

Create a QueryBuilder.

$builder = $connection->builder();

Commit

Commit a transaction.

$connection->commit();

Connect

Connect to the database.

$connection->connect();

This method is called automatically when the Connection is created.

Disconnect

Disconnect from the database.

$connection->disconnect();

Execute

Execute a SQL query with bound parameters.

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

$result = $connection->execute($sql, $params);

The SQL query can use either ? as a placeholder (for numerically indexed paramaters), 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.

$charset = $connection->getCharset();

Get Collation

Get the connection collation.

$collation = $connection->getCollation();

Get Error

Get the last connection error.

$error = $connection->getError();

Insert ID

Get the last inserted ID.

$id = $connection->insertId();

Query

Execute a SQL query.

• $sql is a string representing the SQL query.

$result = $connection->query($sql);

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.

$quoted = $connection->quote($value);

Rollback

Rollback a transaction.

$connection->rollback();

Transactional

Execute a callback inside a database transaction.

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

$result = $connection->transactional($callback);

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

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.

ConnectionManager::setConfig($key, $options);

$connection = ConnectionManager::use($key);

Delete

Set query as DELETE.

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

$builder->delete($aliases);

Distinct

Set the DISTINCT clause.

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

$builder->distinct($distinct);

Epilog

Set the epilog.

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

$builder->epilog($epilog);

Except

Add an EXCEPT query.

• $query is a Closure, QueryBuilder, QueryLiteral or string representing the query.

$builder->except($query);

Execute

Execute the query.

$result = $builder->execute();

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

Group By

Set the GROUP BY fields.

• $fields is an array or string representing the fields to group by.

$builder->groupBy($fields);

Having

Set the HAVING conditions.

• $conditions is an array or string representing the having conditions.

$builder->having($conditions);

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 QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Insert

Set query as an INSERT.

• $data is an array of values to insert.

$builder->insert($data);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Insert Batch

Set query as a batch INSERT.

• $data is a 2-dimensional array of values to insert.

$builder->insertBatch($data);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Insert From

Set query as an INSERT from another query.

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

$builder->insertFrom($query, $columns);

Intersect

Add an INTERSECT query.

• $query is a Closure, QueryBuilder, QueryLiteral or string representing the query.

$builder->intersect($query);

Join

Set the JOIN tables.

• $joins is a 2-dimensional array of joins.

$builder->join($joins);

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.

$builder->limit($limit, $offset);

Literal

Create a QueryLiteral.

• $string is a string representing the literal string.

$literal = $builder->literal($string);

Offset

Set the OFFSET clause.

• $offset is a number indicating the query offset.

$builder->offset($offset);

Order By

Set the ORDER BY fields.

• $fields is an array or string representing the fields to order by.

$builder->orderBy($fields);

Replace

Set query as a REPLACE.

• $data is an array of values to replace.

$builder->replace($data);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Replace Batch

Set query as a batch REPLACE.

• $data is a 2-dimensional array of values to replace.

$builder->replaceBatch($data);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Select

Set the SELECT fields.

• $fields is an array or string representing the fields to select.

$builder->select($fields);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Sql

Generate the SQL query.

$query = $builder->sql();

Table

Set the table(s).

• $tables is an array or string representing the tables.

$builder->table($tables);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Update

Set query as an UPDATE.

• $data is an array of values to update.

$builder->update($data);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Update Batch

Set query as a batch UPDATE.

• $data is a 2-dimensional array of values to update.
• $updateKeys is a string or array containing the keys to use for updating.

$builder->updateBatch($data, $updateKeys);

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

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

Union

Add a UNION DISTINCT query.

• $query is a Closure, QueryBuilder, QueryLiteral or string representing the query.

$builder->union($query);

Union All

Add a UNION ALL query.

• $query is a Closure, QueryBuilder, QueryLiteral or string representing the query.

$builder->unionAll($query);

Where

Set the WHERE conditions.

• $conditions is an array or string representing the where conditions.

$builder->where($conditions);

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 QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

With

Set the WITH clause.

• $with is an array of common table expressions.

$builder->with($with);

Array keys will be used as table aliases.

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

With Recursive

Set the WITH RECURSIVE clause.

• $with is an array of common table expressions.

$builder->withRecursive($with);

Array keys will be used as table aliases.

If a QueryBuilder 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 a new QueryBuilder will be passed as the first argument.

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.

foreach ($result AS $row) { }

All

Get the results as an array.

$array = $result->all();

Column Count

Get the column count.

$columnCount = $result->columnCount();

Columns

Get the result columns.

$columns = $result->columns();

Count

Get the result count.

$count = $result->count();

Fetch

Get a result by index.

• $index is a number indicating the row index.

$row = $result->fetch($index);

First

Get the first result.

$first = $result->first();

Free

Free the result from memory.

$result->free();

Get Type

Get the Type parser for a column.

• $name is a string representing the column name.

$parser = $result->getType($name);

Last

Get the last result.

$last = $result->last();