Download the PHP package artox-lab/fairy-database without Composer

On this page you can find all versions of the php package artox-lab/fairy-database. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.

FAQ

After the download, you have to make one include require_once('vendor/autoload.php');. After that you have to import the classes with use statements.

Example:
If you use only one package a project is not needed. But if you use more then one package, without a project it is not possible to import the classes with use statements.

In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
Some PHP packages are not free to download and because of that hosted in private repositories. In this case some credentials are needed to access such packages. Please use the auth.json textarea to insert credentials, if a package is coming from a private repository. You can look here for more information.

  • Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
  • To use Composer is sometimes complicated. Especially for beginners.
  • Composer needs much resources. Sometimes they are not available on a simple webspace.
  • If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
  • Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Please rate this library. Is it a good library?

Informations about the package fairy-database

Pixie Query Builder Build Status

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. At least PHP 5.3 is required.

It has some advanced features like:

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

Example

Simple Query:

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

Full Queries:

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.

There are many advanced options which are documented below. Sold? Lets install.

Installation

Pixie uses Composer to make things easy.

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

"artox-lab/fairy-database": "1.0.6"

And run:

composer update

Library on Packagist.

Full Usage API

Table of Contents


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

Alias

When you create a connection:

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 instanciate the QueryBuilder handler separately, helpful for Dependency Injection and Testing.

$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

Query

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

Get Easily

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

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.

Select

Multiple Selects

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

Get All

Return an array.

You can loop through it like:

Get First Row

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

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.

Where In

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.

Group By and Order By

Multiple Group By

Using groupBy() or orderBy() methods multiple times groupBy('a')->groupBy('b') will also group by first a and than b. Can be useful if you want to do conditional grouping (within a PHP if). Same applies to orderBy().

Having

Limit and Offset

Join

Available methods,

If you need FULL OUTER join or any other join, just pass it as 5th parameter of join method.

Multiple Join Criteria

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

Raw Query

You can always use raw queries if you need,

You can also pass your bindings

Raw Expressions

When you wrap an expression with raw() method, Pixie doesn't try to sanitize these.


NOTE: Queries that run through query() method are not sanitized until you pass all values through bindings. Queries that run through raw() method are not sanitized either, you have to do it yourself. And of course these don't add table prefix too, but you can use the addTablePrefix() method.

Insert

insert() method returns the insert id.

Batch Insert

In case of batch insert, it will return an array of insert ids.

Insert with ON DUPLICATE KEY statement

Update

Will update the name field to Sana and description field to Blah where id = 5.

Delete

Will delete all the rows where id is greater than 5.

Get Built Query

Sometimes you may need to get the query string, its possible.

getQuery() will return a query object, from this you can get sql, bindings or raw sql.

Sub Queries and Nested Queries

Rarely but you may need to do sub queries or nested queries. Pixie is powerful enough to do this for you. You can create different query objects and use the QB::subQuery() method.

This will produce a query like this:

SELECT * FROM (SELECT `cb_my_table`.*, (SELECT `details` FROM `cb_person_details` WHERE `person_id` = 3) as table_alias1 FROM `cb_my_table`) as table_alias2

NOTE: Pixie doesn't use bindings for sub queries and nested queries. It quotes values with PDO's quote() method.

Get PDO Instance

If you need to get the PDO instance you can do so.

Fetch results as objects of specified class

Simply call asObject query's method.

Furthermore, you may fine-tune fetching mode by calling setFetchMode method.

Query Events

Pixie comes with powerful query events to supercharge your application. These events are like database triggers, you can perform some actions when an event occurs, for example you can hook after-delete event of a table and delete related data from another table.

Available Events

Registering Events

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

The syntax is registerEvent('event type', 'table name', action in a closure).

If you want the event to be performed when any table is being queried, provide ':any' as table name.

Other examples:

After inserting data into my_table, details will be inserted into another table

Whenever data is inserted into person_details table, set the timestamp field created_at, so we don't have to specify it everywhere:

After deleting from my_table delete the relations:

Pixie passes the current instance of query builder as first parameter of your closure so you can build queries with this object, you can do anything like usual query builder (QB).

If something other than null is returned from the before-* query handler, the value will be result of execution and DB will not be actually queried (and thus, corresponding after-* handler will not be called ether).

Only on after-* events you get three parameters: first is the query builder, third is the execution time as float and the second varies:

Removing Events

Some Use Cases

Here are some cases where Query Events can be extremely helpful:

Notes


If you find any typo then please edit and send pull request.

© 2015 Licensed under MIT license.


All versions of fairy-database with dependencies

PHP Build Version
Package Version
Requires php Version >=5.3.0
Composer command for our command line client (download client) This client runs in each environment. You don't need a specific PHP version etc. The first 20 API calls are free. Standard composer command

The package artox-lab/fairy-database contains the following files

Loading the files please wait ....