This project is Not Actively Maintained but most of the features are fully working and there are no major security issues, I'm just not giving it much time.

Pixie Query Builder

A lightweight, expressive, framework agnostic query builder for PHP it can also be referred as a Database Abstraction Layer. Pixie supports MySQL, SQLite and PostgreSQL and it takes care of query sanitization, table prefixing and many other things with a unified API.

It has some advanced features like:

Query Events
Nested Criteria
Sub Queries
Nested Queries
Multiple Database Connections.

The syntax is quite similar to Laravel's query builder.

Example

// Make sure you have Composer's autoload file included
require 'vendor/autoload.php';

// Create a connection, once only.
$config = [
            'driver'    => 'mysql', // Db driver
            'host'      => 'localhost',
            'database'  => 'your-database',
            'username'  => 'root',
            'password'  => 'your-password',
            'charset'   => 'utf8', // Optional
            'collation' => 'utf8_unicode_ci', // Optional
            'prefix'    => 'cb_', // Table prefix, optional
            'options'   => [ // PDO constructor options, optional
                PDO::ATTR_TIMEOUT => 5,
                PDO::ATTR_EMULATE_PREPARES => false,
            ],
        ];

new \Pixie\Connection('mysql', $config, 'QB');

Simple Query:

The query below returns the row where id = 3, null if no rows.

$row = QB::table('my_table')->find(3);

Full Queries:

$query = QB::table('my_table')->where('name', '=', 'Sana');

// Get result
$query->get();

Query Events:

After the code below, every time a select query occurs on users table, it will add this where criteria, so banned users don't get access.

QB::registerEvent('before-select', 'users', function($qb)
{
    $qb->where('status', '!=', 'banned');
});

There are many advanced options which are documented below. Sold? Let's install.

Installation

Pixie uses Composer to make things easy.

Learn to use composer and add this to require section (in your composer.json):

"usmanhalalit/pixie": "2.*@dev"

And run:

composer update

Library on Packagist.

Full Usage API

Connection
Query
Select
Where
Group By and Order By
Having
Limit and Offset
Join
- Multiple Join Criteria
Raw Query
- Raw Expressions
Insert
- Batch Insert
- Insert with ON DUPLICATE KEY statement
Update
Delete
Transactions
Get Built Query
Sub Queries and Nested Queries
Get PDO Instance
Fetch results as objects of specified class
Query Events

Connection

Pixie supports three database drivers, MySQL, SQLite and PostgreSQL. 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.;

// Make sure you have Composer's autoload file included
require 'vendor/autoload.php';

$config = array(
            'driver'    => 'mysql', // Db driver
            'host'      => 'localhost',
            'database'  => 'your-database',
            'username'  => 'root',
            'password'  => 'your-password',
            'charset'   => 'utf8', // Optional
            'collation' => 'utf8_unicode_ci', // Optional
            'prefix'    => 'cb_', // Table prefix, optional
        );

new \Pixie\Connection('mysql', $config, 'QB');

// Run query
$query = QB::table('my_table')->where('name', '=', 'Sana');

Alias

When you create a connection:

new \Pixie\Connection('mysql', $config, 'MyAlias');

MyAlias is the name for the class alias you want to use (like MyAlias::table(...)), you can use whatever name (with Namespace also, MyNamespace\\MyClass) you like or you may skip it if you don't need an alias. Alias gives you the ability to easily access the QueryBuilder class across your application.

When not using an alias you can instantiate the QueryBuilder handler separately, helpful for Dependency Injection and Testing.

$connection = new \Pixie\Connection('mysql', $config);
$qb = new \Pixie\QueryBuilder\QueryBuilderHandler($connection);

$query = $qb->table('my_table')->where('name', '=', 'Sana');

var_dump($query->get());

$connection here is optional, if not given it will always associate itself to the first connection, but it can be useful when you have multiple database connections.

SQLite and PostgreSQL Config Sample

new \Pixie\Connection('sqlite', array(
                'driver'   => 'sqlite',
			    'database' => 'your-file.sqlite',
			    'prefix'   => 'cb_',
		    ), 'QB');

new \Pixie\Connection('pgsql', array(
                    'driver'   => 'pgsql',
                    'host'     => 'localhost',
                    'database' => 'your-database',
                    'username' => 'postgres',
                    'password' => 'your-password',
                    'charset'  => 'utf8',
                    'prefix'   => 'cb_',
                    'schema'   => 'public',
                ), 'QB');

Query

You must use table() method before every query, except raw query(). To select from multiple tables just pass an array.

QB::table(array('mytable1', 'mytable2'));

Get Easily

The query below returns the (first) row where id = 3, null if no rows.

$row = QB::table('my_table')->find(3);

Access your row like, echo $row->name. If your field name is not id then pass the field name as second parameter QB::table('my_table')->find(3, 'person_id');.

The query below returns the all rows where name = 'Sana', null if no rows.

$result = QB::table('my_table')->findAll('name', 'Sana');

Select

$query = QB::table('my_table')->select('*');

Multiple Selects

->select(array('mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3'));

Using select method multiple times select('a')->select('b') will also select a and b. Can be useful if you want to do conditional selects (within a PHP if).

Select Distinct

->selectDistinct(array('mytable.myfield1', 'mytable.myfield2'));

Get All

Return an array.

$query = QB::table('my_table')->where('name', '=', 'Sana');
$result = $query->get();

You can loop through it like:

foreach ($result as $row) {
    echo $row->name;
}

Get First Row

$query = QB::table('my_table')->where('name', '=', 'Sana');
$row = $query->first();

Returns the first row, or null if there is no record. Using this method you can also make sure if a record exists. Access these like echo $row->name.

Get Rows Count

$query = QB::table('my_table')->where('name', '=', 'Sana');
$query->count();

Where

Basic syntax is (fieldname, operator, value), if you give two parameters then = operator is assumed. So where('name', 'usman') and where('name', '=', 'usman') is the same.

QB::table('my_table')
    ->where('name', '=', 'usman')
    ->whereNot('age', '>', 25)
    ->orWhere('type', '=', 'admin')
    ->orWhereNot('description', 'LIKE', '%query%')
    ;

Where In

QB::table('my_table')
    ->whereIn('name', array('usman', 'sana'))
    ->orWhereIn('name', array('heera', 'dalim'))
    ;

QB::table('my_table')
    ->whereNotIn('name', array('heera', 'dalim'))
    ->orWhereNotIn('name', array('usman', 'sana'))
    ;

Where Between

QB::table('my_table')
    ->whereBetween('id', 10, 100)
    ->orWhereBetween('status', 5, 8);

Where Null

QB::table('my_table')
    ->whereNull('modified')
    ->orWhereNull('field2')
    ->whereNotNull('field3')
    ->orWhereNotNull('field4');

Grouped Where

Sometimes queries get complex, where you need grouped criteria, for example WHERE age = 10 and (name like '%usman%' or description LIKE '%usman%').

Pixie allows you to do so, you can nest as many closures as you need, like below.

QB::table('my_table')
            ->where('my_table.age', 10)
            ->where(function($q)
                {
                    $q->where('name', 'LIKE', '%usman%');
                    // You can provide a closure on these wheres too, to

Pixie

Install / Use

README