Database
The Database component is a framework agnostic PHP database abstraction layer, providing an expressive query builder. It currently supports MySQL, Postgres, SQL Server, and SQLite.
Features:
- Simple CRUD functions
- Support for Insert Ignore / Replace
- Support for Insert On Duplicate Key Update
- Support for direct
INSERT INTO ... SELECT * FROMqueries - Buffered inserts from Traversable/Iterator interfaces
- Joins
- Sub Queries
- Nested Queries
- Bulk Inserts
- MySQL
SELECT * INTO OUTFILE '...' - MySQL
LOAD DATA INFILE '...' - Lazy Connections
- PSR Compatible Logging
- Database Connection Resolver
The component is based on Laravel's Illuminate\Database and has very familiar syntax. The core Query Builder is mostly compatible. The main alterations are to the composition of the objects, and most significantly the creation and resolution of connections within the ConnectionFactory and ConnectionResolver classes.
Installation
composer require mrjgreen/database
Basic Example
First, create a new "ConnectionFactory" instance.
$factory = new \Database\Connectors\ConnectionFactory();
$connection = $factory->make(array(
'driver' => 'mysql',
'host' => 'localhost',
'username' => 'root',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
// Don't connect until we execute our first query
'lazy' => true,
// Set PDO attributes after connection
'options' => array(
PDO::MYSQL_ATTR_LOCAL_INFILE => true,
PDO::ATTR_EMULATE_PREPARES => true,
)
));
$connection->query("SELECT id, username FROM customers");Documentation
Table of Contents
- Connection
- Connection Resolver
- Raw Queries
- Query Builder
- Selects
- Insert
- Update
- Delete
- Raw Expressions
- Get SQL
- Raw PDO Instance
Connection
The Database component supports MySQL, SQLite, SqlServer and PostgreSQL drivers. You can specify the driver during connection and the associated configuration when creating a new connection. You can also create multiple connections, but you can use alias for only one connection at a time.;
$factory = new \Database\Connectors\ConnectionFactory();MySQL
$connection = $factory->make(array(
'driver' => 'mysql',
'host' => 'localhost',
'username' => 'root',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
));
$connection->fetchAll("SELECT id, username FROM customers");
$connection->table('customers')
->find(12);
$connection->table('customers')
->join('products', 'customer.id', '=', 'customer_id')
->where('favourites', '=', 1)
->get();SQLite
$connection = $factory->make(array(
'driver' => 'sqlite',
'database' => '/path/to/sqlite.db',
));Default Connection Options
By default the following PDO attributes will be set on connection. You can override these or add to them in the
options array parameter in the connection config.
PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES => false,Connection Resolver
Many complex applications may need more than one database connection. You can create a set of named connections inside the connection resolver, and reference them by name within in your application.
$resolver = new Database\ConnectionResolver(array(
'local' => array(
'driver' => 'mysql',
'host' => 'localhost',
'username' => 'root',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
),
'archive' => array(
'driver' => 'mysql',
'host' => '1.2.3.456',
'username' => 'root',
'password' => 'password',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
),
));
$dbLocal = $resolver->connection('local');
// Use it
$dbLocal->table('users')->get();
$dbArchive = $resolver->connection('archive');
// Etc...If you request a connection that you have used previously in your application, the connection resolver will return the same connection, rather than create a new one.
You can set a default connection after creating the resolver, so you don't have to specify the connection name throughout your application.
$resolver->setDefaultConnection('local');
// Returns the `local` connection
$resolver->connection();Raw Queries
Perform a query, with bindings and return the PDOStatement object
$statement = $connection->query('SELECT * FROM users WHERE name = ?', array('John Smith'));
// PDOStatement
$statement->rowCount();
$statement->fetchAll();Query Shortcuts
$firstRow = $connection->fetch('SELECT * FROM users WHERE name = ?', array('John Smith'));
$allRows = $connection->fetchAll('SELECT * FROM users WHERE name = ?', array('John Smith'));
$firstColumnFirstRow = $connection->fetchOne('SELECT COUNT(*) FROM users WHERE name = ?', array('John Smith'));Query Builder
Selects
Get PDOStatement
If you intend to iterate through the rows, it may be more efficient to get the PDOStatement
$rows = $connection->table('users')->query();Get All
$rows = $connection->table('users')->get();Get First Row
$row = $connection->table('users')->first();Find By ID
$row = $connection->table('users')->find(6);The query above assumes your table's primary key is 'id' and you want to retreive all columns. You can specify the columns you want to fetch, and your primary key:
$connection->table('users')->find(3, array('user_id', 'name', 'email'), 'user_id');Select Columns
$rows = $connection->table('users')->select('name')->addSelect('age', 'dob')->get();Limit and Offset
$connection->table('users')->offset(100)->limit(10);Where
$connection->table('user')
->where('username', '=', 'jsmith')
->whereNotIn('age', array(10,20,30))
->orWhere('type', '=', 'admin')
->orWhereNot('name', 'LIKE', '%Smith%')
->get();Grouped Where
$connection->table('users')
->where('age', '>', 10)
->orWhere(function($subWhere)
{
$subWhere
->where('animal', '=', 'dog')
->where('age', '>', 1)
});
SELECT * FROM `users` WHERE `age` > 10 or (`age` > 1 and `animal` = 'dog')`.Group By, Order By and Having
$users = $connection->table('users')
->orderBy('name', 'desc')
->groupBy('count')
->having('count', '>', 100)
->get();Joins
$connection->table('users')
->join('products', 'user_id', '=', 'users.id')
->get();
/*
->leftJoin()
->rightJoin()
*/Multiple Join Criteria
If you need more than one criterion to join a table then you can pass a closure as second parameter.
->join('products', function($table)
{
$table->on('users.id', '=', 'products.user_id');
$table->on('products.price', '>', 'users.max_price');
})Sub Selects
$query = $connection->table('users')
->selectSub(function($subQuery){
$subQuery
->from('customer')
->select('name')
->where('id', '=', $subQuery->raw('users.id'));
}, 'tmp');This will produce a query like this:
SELECT (SELECT `name` FROM `customer` WHERE `id` = users.id) as `tmp` FROM `users`
Aggregates
Count
$count = $connection->table('users')->count();Min
$count = $connection->table('users')->min('age');Max
$count = $connection->table('users')->max('age');Average
$count = $connection->table('users')->avg('age');Sum
$count = $connection->table('users')->sum('age');MySQL Outfile
$connection
->table('users')
->select('*')
->where('bar', '=', 'baz')
->intoOutfile('filename', function(\Database\Query\OutfileClause $out){
$out
->enclosedBy(".")
->escapedBy("\\")
->linesTerminatedBy("\n\r")
->fieldsTerminatedBy(',');
})->query();Insert
$data = array(
'username' = 'jsmith',
'name' = 'John Smith'
);
$connection->table('users')->insert($data);
// Returns PDOStatement
`->insertGetId($data)` method returns the insert id instead of a PDOStatementInsert Ignore
Ignore errors from any rows inserted with a duplicate unique key
$data = array(
'username' = 'jsmith',
'name' = 'John Smith'
);
$connection->table('users')->insertIgnore($data);Replace
Replace existing rows with a matching unique key
$data = array(
'username' = 'jsmith',
'name' = 'John Smith'
);
$connection->table('users')->replace($data);Batch Insert
The query builder will intelligently handle multiple insert rows:
$data = array(
array(
'username' = 'jsmith',
'name' = 'John Smith'
),
array(
'username' = 'jbloggs',
'name' = 'Joe Bloggs'
),
);
$connection->table('users')->insert($data);You can also pass bulk inserts to replace() and insertIgnore()
On Duplicate Key Update
$data = array(
'username' = 'jsmith',
'name' = 'John Smith'
);
$now = $connection->raw('NOW()');
$connection->table('users')->insertUpdate(
array('username' => 'jsmith', 'active' => $now), // Insert this data
array('active' => $now) // Or partially update the row if it exists
);
//insertOnDuplicateKeyUpdate() is an alias of insertUpdateInsert Select
$connection->table('users')->insertSelect(function($select){ $select->from('admin') ->select('name', 'email') ->where('status', '=', 1);
}, array('name','email'));
insertIgnoreSelect and replaceSelect methods are supported for the MySQL grammar driver.
Buffered Iterator Insert
If you have a large data set you can insert in batches of a chosen size (insert ignore/replace/on duplicate key update supported).
This is especially useful if you want to select large data-sets from one server and insert into another.
$pdoStatement = $mainServer->table('users')->query(); // Returns a PDOStatement (which implements the `Traversable` interface)
// Will be inserted in batches of 1000 as it reads from the rowset iterator.
$backupServer->table('users')->buffer(1000)->insertIgnore($pdoStatement);Update
$data = array(
'username' = 'jsmith123',
'name' = 'John Smith'
);
$connection->table('users')->where('id', 123)->update($data);Delete
$connection->table('users')->where('last_active', '>', 12)->delete();Will delete all the rows where id is greater than 5.
Raw Expressions
Wrap raw queries with $connection->raw() to bypass query parameter binding. NB use with caution - no sanitisation will take place.
$connection->table('users')
->select($connection->raw('DATE(activity_time) as activity_date'))
->where('user', '=', 123)
->get();Get SQL Query and Bindings
$query = $connection->table('users')->find(1)->toSql();
$query->toSql();
// SELECT * FROM users where `id` = ?
$query->getBindings();
// array(1)Raw PDO Instance
$connection->getPdo();
