chillerlan/php-database

A PHP 7.2+ SQL client and querybuilder for the most common databases.

Documentation

Requirements

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

Supported databases

• MySQL (5.5+) / MariaDB
• PostgreSQL (9.5+)
• SQLite3
• Firebird (2.5+)
• Microsoft SQL Server (transact-sql)
• any other database supported via PDO, ODBC or native PHP extension

Installation

requires composer

composer.json

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

{
	"require": {
		"php": ">=7.2.0",
		"chillerlan/database": "dev-master"
	}
}

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

Profit!

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.

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

which is equivalent to

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

now instance a driver with these options

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

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

via the querybuilder

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

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

recommended way via Database, which provides all methods of DriverInterface and QueryBuilder

$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

Methods of DriverInterface

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.

Methods of Database

in addition to DriverInterface and QueryBuilderInterface methods

The Statement interface

Create

CreateDatabase

$conn->create
	->database('test')
	->ifNotExists()
	->charset('utf8mb4_bin')
	->execute();

CREATE DATABASE IF NOT EXISTS `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

CreateTable

$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')
	->execute();

The generated Query will look something like this

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

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

$conn->insert
	->into('products')
	->values(['name' => 'product1', 'type' => 'a', 'price' => 3.99, 'weight' => 0.1, 'added' => time()])
	->execute();

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

An array with multiple rows will automatically perform a multi insert

$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')
	->values($values)
	->execute();

As an alternative, you can provide the values via a callback

$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' => '?'])
	->execute(null, $values, function($row){
		return [
			$row[0],
			$row[1],
			floatval($row[2]),
			floatval($row[3]),
			time(),
		];
	});

Select

$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

Delete

The Result and ResultRow objects

Result implements \SeekableIterator, \ArrayAccess and \Countable, ResultRow extends it.

Result

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

methods from Enumerable

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?

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]]