2017 © Pedro Peláez
 

library database

An extensible database wrapper and query builder for PHP 7.2+

image

chillerlan/database

An extensible database wrapper and query builder for PHP 7.2+

  • Tuesday, April 10, 2018
  • by codemasher
  • Repository
  • 1 Watchers
  • 0 Stars
  • 685 Installations
  • PHP
  • 2 Dependents
  • 0 Suggesters
  • 0 Forks
  • 0 Open issues
  • 8 Versions
  • 1 % Grown

The README.md

chillerlan/php-database

A PHP 7.2+ SQL client and querybuilder for the most common databases., (*1)

version ![license][license-badge] ![Travis][travis-badge] ![Coverage][coverage-badge] ![Scrunitizer][scrutinizer-badge] ![Packagist downloads][downloads-badge] ![PayPal donate][donate-badge], (*2)

Documentation

Requirements

  • PHP 7.2+
  • one of the supported databases, set up to work with PHP

Supported databases

Installation

requires composer, (*3)

composer.json

(note: replace dev-master with a version boundary), (*4)

{
    "require": {
        "php": ">=7.2.0",
        "chillerlan/database": "^2.1"
    }
}

Manual installation

Download the desired version of the package from master or release and extract the contents to your project folder. After that: - run composer install to install the required dependencies and generate /vendor/autoload.php. - if you use a custom autoloader, point the namespace chillerlan\Database to the folder src of the package, (*5)

Profit!, (*6)

Usage

Getting started

Both, the DriverInterface and QueryBuilder can be instanced on their own. However, since the QueryBuilder requires an instance of DriverInterface it's recommended to just use Database which instances both and provides all of their methods. A DriverInterface requires a DatabaseOptions object and accepts a Psr\SimpleCache\CacheInterface and a Psr\Log\LoggerInterface as optional parameters, the QueryBuilder accepts a LoggerInterface as additional parameter., (*7)

$options = new DatabaseOptions;
$options->database = 'whatever';
$options->username = 'user';
$options->password = 'supersecretpassword';

which is equivalent to, (*8)

$options = new DatabaseOptions([
    'database' => 'whatever',
    'username' => 'user',
    'password' => 'supersecretpassword',
]);

now instance a driver with these options, (*9)

$mysql = new MySQLiDrv($options, $cache, $log);
$mysql->connect();

// a raw query using the driver directly
$result = $mysql->raw('SELECT * FROM sometable');

via the querybuilder, (*10)

$querybuilder = new QueryBuilder($mysql, $log)

$result = $querybuilder->select->from('sometable')->query();

recommended way via Database, which provides all methods of DriverInterface and QueryBuilder, (*11)

$options->driver = MySQLiDrv::class;

$db = new Database($options);
$db->connect();

$result = $db->raw('SELECT * FROM sometable');
// is equivalent to
$result = $db->select->from('sometable')->query();

Properties of DatabaseOptions

property type default allowed description
$driver string null DriverInterface database driver to use (FQCN)
$querybuilder string null QueryBuilderInterface query builder to use (FQCN) [optional]
$host string 'localhost'
$port int null
$socket string null
$database string null
$username string null
$password string null
$use_ssl bool false indicates whether the connection should use SSL or not
$ssl_key string null
$ssl_cert string null
$ssl_ca string null
$ssl_capath string null
$ssl_cipher string null
$mysqli_timeout int 3
$mysql_charset string 'utf8mb4' How to support full Unicode in MySQL
$pgsql_charset string 'UTF8'
$odbc_driver string null
$convert_encoding_src string null supported encodings mb_convert_encoding(), used in Result
$convert_encoding_dest string 'UTF-8' supported encodings mb_convert_encoding(), used in Result
$mssql_timeout int 3
$mssql_charset string 'UTF-8'
$mssql_encrypt bool false

Methods of DriverInterface

method return
__construct(DatabaseOptions $options, CacheInterface $cache = null) -
connect() DriverInterface
disconnect() bool
getDBResource() resource|object
getClientInfo() string
getServerInfo() string
escape($data) string (subject to change)
raw(string $sql, string $index = null, bool $assoc = true) Result|bool
rawCached(string $sql, string $index = null, bool $assoc = true, int $ttl = null) Result|bool
prepared(string $sql, array $values = [], string $index = null, bool $assoc = true) Result|bool
preparedCached(string $sql, array $values = [], string $index = null, bool $assoc = true, int $ttl = null) Result|bool
multi(string $sql, array $values) bool (subject to change)
multiCallback(string $sql, array $data, $callback) bool (subject to change)

Methods of QueryBuilder

All methods of QueryBuilder are also accessible as properties via magic methods. The returned object is a Statement of \chillerlan\Database\Query\* interfaces., (*12)

method return
__construct(DriverInterface $db, LoggerInterface $logger = null) -
select() Select
insert() Insert
update() Update
delete() Delete
create() Create
alter() Alter
drop() Drop

Methods of Database

in addition to DriverInterface and QueryBuilderInterface methods, (*13)

method return
__construct(DatabaseOptions $options, CacheInterface $cache = null, LoggerInterface $logger = null) -
getDriver() DriverInterface
getQueryBuilderFQCN() QueryBuilderInterface|null

The Statement interface

method return description
sql() string returns the SQL for the current statement
bindValues() array returns the values for each '?' parameter in the SQL
query(string $index = null Result Executes the current statement. $index is being used in "SELECT" statements to determine a column to index the Result by. $values.
multi(array $values = null) bool Executes the current statement as multi query. $values needs to be a multi dimensional array with each row.
callback(array $values = null, $callback = null) bool Executes the current statement. $index is being used in "SELECT" statements to determine a column to index the Result by. $values and $callback can be used to provide multiple values on multi row "INSERT" or "UPDATE" queries.

Create

method return
database(string $dbname = null) CreateDatabase
table(string $tablename = null) CreateTable

CreateDatabase

method description
ifNotExists()
name(string $dbname = null)
charset(string $collation)
$conn->create
    ->database('test')
    ->ifNotExists()
    ->charset('utf8mb4_bin')
    ->query();

```mysql CREATE DATABASE IF NOT EXISTS test CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, (*14)


#### `CreateTable` method | description ------ | ----------- `ifNotExists()` | `name(string $tablename = null)` | `charset(string $collation)` | `primaryKey(string $field)` | `field(string $name, string $type, $length = null, string $attribute = null, string $collation = null, bool $isNull = null, string $defaultType = null, $defaultValue = null, string $extra = null)` | `int(string $name, int $length = null, $defaultValue = null , bool $isNull = null, string $attribute = null)` | convenience shortcut for `field()`, also `tinyint(...)` `varchar(string $name, int $length, $defaultValue = null , bool $isNull = null)` | `decimal(string $name, string $length, $defaultValue = null , bool $isNull = null)` | `text(string $name, $defaultValue = null , bool $isNull = true)` | also `tinytext()` `enum(string $name, array $values, $defaultValue = null , bool $isNull = null)` | currently the only way to create an "ENUM" field ```php $conn->create ->table('products') ->ifNotExists() ->int('id', 10, null, false, 'UNSIGNED AUTO_INCREMENT') ->tinytext('name', null, false) ->varchar('type', 20) ->decimal('price', '9,2', 0) ->decimal('weight', '8,3') ->int('added', 10, 0, null, 'UNSIGNED') ->primaryKey('id') ->query();

The generated Query will look something like this, (*15)

-- mysql

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
    `name` TINYTEXT NOT NULL,
    `type` VARCHAR(20),
    `price` DECIMAL(9,2) NOT NULL DEFAULT 0,
    `weight` DECIMAL(8,3),
    `added` INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`)
)

Note that additional constraints and attributes will be appended regardless of the Query dialect, (*16)

-- postgres: attributes UNSIGNED and AUTO_INCREMENT are invalid

CREATE TABLE IF NOT EXISTS "products" (
    "id" INT NOT NULL UNSIGNED AUTO_INCREMENT,
    "name" VARCHAR(255) NOT NULL,
    "type" VARCHAR(20),
    "price" DECIMAL(9,2) NOT NULL DEFAULT '0',
    "weight" DECIMAL(8,3),
    "added" INT NOT NULL UNSIGNED DEFAULT '0',
    PRIMARY KEY ("id")
)

Insert

method description
into(string $table) The table where to insert data
values(array $values) An array of values where each row represents a row to insert [['column' => 'value', ...], ...]
$conn->insert
    ->into('products')
    ->values(['name' => 'product1', 'type' => 'a', 'price' => 3.99, 'weight' => 0.1, 'added' => time()])
    ->query();

```mysql INSERT INTO products (name, type, price, weight, added) VALUES (?,?,?,?,?), (*17)



```php $values = [ ['name' => 'product2', 'type' => 'b', 'price' => 4.20, 'weight' => 2.35, 'added' => time()], ['name' => 'product3', 'type' => 'b', 'price' => 6.50, 'weight' => 1.725, 'added' => time()], ]; $conn->insert ->into('products') ->multi($values);

As an alternative, you can provide the values via a callback, (*18)

$values = [
    ['product4', 'c', 3.99, 0.1,],
    ['product5', 'a', 4.20, 2.35,],
    ['product6', 'b', 6.50, 1.725,],
];

$conn->insert
    ->into('products')
    ->values([['name' => '?', 'type' => '?', 'price' => '?', 'weight' => '?', 'added' => '?']])
    ->callback($values, function($row){
        return [
            $row[0],
            $row[1],
            floatval($row[2]),
            floatval($row[3]),
            time(),
        ];
    });

Select

method description
distinct() sets the "DISTINCT" statement (if the Query dialect supports it)
cols(array $expressions) An array of column expressions. If omitted, a SELECT * ... will be performed. Example: ['col', 'alias' => 'col', 'alias' => ['col', 'sql_function']]
from(array $expressions) An array of table expressions. Example: ['table', 'alias' => 'table']
groupBy(array $expressions) An array of expressions to group by.
where($val1, $val2, $operator = '=', $bind = true, $join = 'AND') Adds a "WHERE" clause, comparing $val1 and $val2 by $operator. $bind specifies whether the value should be bound to a '?' parameter (default) or not (no effect if $val2 is a Select interface). If more than one "WHERE" statement exists, they will be joined by $join.
openBracket($join = null) puts an opening bracket ( at the current position in the "WHERE" statement
closeBracket() puts a closing bracket ) at the current position in the "WHERE" statement
orderBy(array $expressions) An array of expressions to order by. ['col1', 'col2' => 'asc', 'col3' => 'desc']
offset(int $offset) Sets the offset to start from
limit(int $limit) Sets a row limit (page size)
count() Executes the statement to perform a SELECT COUNT(*) ... and returns the row count as int
cached() Performs a chached query
$result = $conn->select
    ->cols([
        'uid'         => ['t1.id', 'md5'],
        'productname' => 't1.name',
        'price'       => 't1.price',
        'type'        => ['t1.type', 'upper'],
    ])
    ->from(['t1' => 'products'])
    ->where('t1.type', 'a')
    ->orderBy(['t1.price' => 'asc'])
    ->execute('uid')
    ->__toArray();
SELECT MD5(`t1`.`id`) AS `uid`,
    `t1`.`name` AS `productname`,
    `t1`.`price` AS `price`,
    UPPER(`t1`.`type`) AS `type`
FROM `products` AS `t1`
WHERE `t1`.`type` = ?
ORDER BY `t1`.`price` ASC
array(2) {
  'c4ca4238a0b923820dcc509a6f75849b' =>
  array(4) {
    'uid' =>
    string(32) "c4ca4238a0b923820dcc509a6f75849b"
    'productname' =>
    string(8) "product1"
    'price' =>
    string(4) "3.99"
    'type' =>
    string(1) "A"
  }
  'e4da3b7fbbce2345d7772b0674a318d5' =>
  array(4) {
    'uid' =>
    string(32) "e4da3b7fbbce2345d7772b0674a318d5"
    'productname' =>
    string(8) "product5"
    'price' =>
    string(4) "8.19"
    'type' =>
    string(1) "A"
  }
}

Update

method description
table(string $tablename) The table to update
set(array $set, bool $bind = true) $set is a key/value array to update the table with. $bind determines whether the values should be inserted into the Query (unsafe! use only for aliases) or be replaced by parameters (the default).
where($val1, $val2, $operator = '=', $bind = true, $join = 'AND') see Select::where()
openBracket($join = null) see Select::openBracket()
closeBracket() see Select::closeBracket()

Single row update, (*19)

$db->update
    ->table('table_to_update')
    ->set(['col_to_update' => 'val1'])
    ->where('row_id', 1)
    ->query();

Update multiple rows, (*20)

$values = [
    // [col_to_update, row_id]
    ['val1', 1],
    ['val2', 2],
    ['val3', 3],
];

$db->update
    ->table('table_to_update')
    ->set(['col_to_update' => '?'], false) // disable value binding here
    ->where('row_id', '?', '=', false) // also disable binding here
    ->multi($values);

The generated SQL for both examples would look like the following, the difference is that one performs a single query, while the other loops through the given value array in the open prepared statement., (*21)

UPDATE `table_to_update` SET `col_to_update` = ? WHERE `row_id` = ?

Delete

method description
from(string $table) The table to delete from
where($val1, $val2, $operator = '=', $bind = true, $join = 'AND') see Select::where()
openBracket($join = null) see Select::openBracket()
closeBracket() see Select::closeBracket()

The Result and ResultRow objects

Result implements \SeekableIterator, \ArrayAccess and \Countable, ResultRow extends it., (*22)

Result

property description
length

methods in addition to \SeekableIterator, \ArrayAccess and \Countable

method description
__construct($data = null, $sourceEncoding = null, $destEncoding = 'UTF-8') If $data is of type \Traversable, \stdClass or array, the Result will be filled with its values. If $sourceEncoding is present, the values will be converted to $destEncoding via mb_convert_encoding().
__merge(Result $result) merges one Result object into another (using array_merge())
__chunk(int $size) splits the Result into chunks of $size and returns it as array (using array_chunk())

methods from Enumerable

method description
__toArray() returns an array representation of the Result
__map($callback) collects the result of $callback for each value of Result and returns it as array
__each($callback) similar to __map(), except it doesn't collect results and returns the Result instance
__reverse() reverses the order of the Result (using array_reverse())

ResultRow

ResultRow allows to call the result fields as magic methods or properties. If called as method, you may supply a callable as argument which then takes the field value as argument. Fancy, huh?, (*23)

Result and ResultRow examples

__map() and __each()

$values = $result->__map(function($row){

    // ...

    return [
        $row->id,
        $row->name('trim'),
        // ...
    ];
});

__merge(), __toArray(), __chunk() and __reverse()

$result1 = new Result([['id' => 1]]);
$result2 = new Result([['id' => 2]]);

$result1->__merge($result2);

var_dump($result1->__toArray()); 
// -> [['id' => 1], ['id' => 2]]

var_dump($result1->__reverse()->__chunk(1)[0]);
// -> [['id' => 2]]

The Versions

10/04 2018

dev-master

9999999-dev https://github.com/chillerlan/php-database

An extensible database wrapper and query builder for PHP 7.2+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

10/04 2018

2.0.2

2.0.2.0 https://github.com/chillerlan/php-database

An extensible database wrapper and query builder for PHP 7.2+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

29/01 2018

2.0.1

2.0.1.0 https://github.com/chillerlan/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

24/01 2018

2.0.0

2.0.0.0 https://github.com/chillerlan/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

16/12 2017

1.0.2

1.0.2.0 https://github.com/codemasher/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

16/12 2017

dev-php7.0

dev-php7.0 https://github.com/codemasher/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

26/11 2017

1.0.1

1.0.1.0 https://github.com/codemasher/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird

09/07 2017

1.0.0

1.0.0.0 https://github.com/codemasher/php-database

An extensible database wrapper and query builder for PHP 7+

  Sources   Download

MIT

The Requires

 

The Development Requires

database postgres query mssql mysql driver mariadb sqlite querybuilder builder firebird