Download the PHP package mrjgreen/database without Composer

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 * FROM queries
• 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

Basic Example

First, create a new "ConnectionFactory" instance.

Documentation

Table of Contents

• Connection
  • MySQL
  • SQLite
  • Default Connection Options
• Connection Resolver
• Raw Queries
  • Query Shortcuts
• Query Builder
• Selects
  • Get All
  • Get First Row
  • Find By ID
  • Select Columns
  • Limit and Offset
  • Where
  • Grouped Where
  • Group By, Order By and Having
  • Joins
  • Sub Selects
  • MySQL Outfile
• Insert
  • Insert Ignore
  • Replace
  • Batch Insert
  • On Duplicate Key Update
  • Insert Select
  • Buffered Iterator 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.;

MySQL

SQLite

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.

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.

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.

Raw Queries

Perform a query, with bindings and return the PDOStatement object

Query Shortcuts

Query Builder

Selects

Get PDOStatement

If you intend to iterate through the rows, it may be more efficient to get the PDOStatement

Get All

Get First Row

Find By ID

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:

Select Columns

Limit and Offset

Where

Grouped Where

Group By, Order By and Having

Joins

Multiple Join Criteria

If you need more than one criterion to join a table then you can pass a closure as second parameter.

Sub Selects

This will produce a query like this:

SELECT (SELECT `name` FROM `customer` WHERE `id` = users.id) as `tmp` FROM `users`

Aggregates

Count

Min

Max

Average

Sum

MySQL Outfile

Insert

Insert Ignore

Ignore errors from any rows inserted with a duplicate unique key

Replace

Replace existing rows with a matching unique key

Batch Insert

The query builder will intelligently handle multiple insert rows:

You can also pass bulk inserts to replace() and insertIgnore()

On Duplicate Key Update

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

Update

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.

Get SQL Query and Bindings

Raw PDO Instance