chillerlan/php-database

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

Documentation

Requirements

• PHP 7.4+
• one of the supported databases, set up to work with PHP:
  • MySQL (5.5+) / MariaDB
  • PostgreSQL (9.5+)
  • SQLite3
  • Firebird (2.5+)
  • Microsoft SQL Server (transact-sql)

Installation

requires composer

composer.json

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

{
	"require": {
		"php": "^7.4 || ^8.0",
		"chillerlan/php-database": "dev-main"
	}
}

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

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

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()])
	->query();

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

$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

$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

$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'])
	->query('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

Single row update

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

Update multiple rows

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

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

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