Download the PHP package pecee/pixie without Composer

On this page you can find all versions of the php package pecee/pixie. 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?

Example code of pecee/pixie

Informations about the package pixie

pecee/pixie: Advanced lightweight querybuilder

A lightweight, expressive, framework agnostic query builder for PHP it can also be referred as a Database Abstraction Layer. Pixie supports MySQL, MS-SQL, SQLite and PostgreSQL will handle all your query sanitization, table alias, unions among many other things, with a unified API.

The syntax is similar to Laravel's query builder "Eloquent", but with less overhead.

This library is stable, maintained and are used by sites around the world (check the credits).

Requirements:

Features:

Including all the original features like:

Most importantly this project is used on many live-sites and maintained.

Examples

Simple query:

Get user with the id of 3. Returns null when no match.

Full queries:

Get all users with blue or red hair.

Table of Contents

Installation

Pixie uses Composer to make things easy.

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

Feedback and development

If you are missing a feature, experience problems or have ideas or feedback that you want us to hear, please feel free to create an issue.

Issues guidelines

Create a new issue here

Contribution and development guidelines

Connecting to the database

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

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

NOTE: Calling the getQueryBuilder method will automatically make a connection to the database, if none has already established. If you want to access the Pdo instance directly from the Connection class, make sure to call $connection->connect(); to establish a connection to the database.

SQLite and PostgreSQL config example

The example below is for use with sqlite-databases.

The example below is for pgsql databases.

Select

It is recommend to use table() method before every query, except raw query(). To select from multiple tables just pass an array.

However this is not required.

Table alias

You can easily set the table alias by using

You can change the alias anytime by using:

Output:

NOTE: You can always remove a table from a query by calling the table method with no arguments like this $queryBuilder->table().

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 $queryBuilder->table('my_table')->find(3, 'person_id');.

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

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

Select from query

You can easily select items from another query by using

Will produce the following output:

Select single field

Select multiple fields

Get all

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

Aggregate methods

Getting the row count

This will return the count for the entire number of rows in the result.

The default behavior will count * (all) fields. You can specify a custom field by changing the field parameter.

Getting the sum

This will return the sum for a field in the entire number of rows in the result.

Getting the average

This will return the average for a field in the entire number of rows in the result.

Getting the minimum

This will return the minimum for a field in the entire number of rows in the result.

Getting the maximum

This will return the average for a field in the entire number of rows in the result.

Select with sub-queries

The example above will output a SQL-query like this:

You can also easily create a subjquery within the where statement:

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

Where between

Where null

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

Join USING syntax

The JOIN USING syntax allows you to easily map two identical identifiers to one, which can be helpful on large queries.

Example:

Can be simplified to:

Multiple join criteria

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

Unions

You can easily create unions by calling the union method on the QueryBuilderHandler.

Example:

The example above will create a sql-statement similar to this:

Raw query

You can always perform raw queries, if needed.

You can also pass custom 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.

Transactions

Pixie has the ability to run database "transactions", in which all database changes are not saved until committed. That way, if something goes wrong or differently then you intend, the database changes are not saved and no changes are made.

Here's a basic transaction:

If this were to cause any errors (such as a duplicate name or some other such error), neither data set would show up in the database. If not, the changes would be successfully saved.

If you wish to manually commit or rollback your changes, you can use the commit() and rollback() methods accordingly:

Transactions will automatically be used when inserting multiple records. For example:

Get raw query

Sometimes you may need to get the query string, it's possible.

getQuery() will return a QueryBuilderHandler object.

You can use this to get the SQL, bindings or raw SQL.

Calling getSql() will return the SQL-query without any processing.

You can easily get any bindings on the query by calling the getBindings()method.

Example:

You can also get the raw SQL-query directly by calling the getRawSql() method.

Example:

Calling the getRawSql() method will return a query including bindings like this.

Get QueryObject from last executed query

You can also retrieve the query-object from the last executed query.

Example:

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 $queryBuilder->subQuery() method.

This will produce a query like this:

NOTE:

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

Getting the PDO instance

If you need the \PDO instance, you can easily get it by calling:

If you want to get the Connection object you can do so like this:

Fetch results as objects of specified class

Simply call asObject query's method.

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

Advanced

Enable query-overwriting

If enabled calling from, select etc. will overwrite any existing values from previous calls in query.

You can enable or disable query-overwriting by calling the setOverwriteEnabled method on the QueryBuilderHandler object.

The feature is disabled as default.

Example:

If you want this feature to be enabled on all QueryBuilderHandler object as default, you can add the following setting to the connection configuration:

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

Event constant Event value/name Description
EventHandler::EVENT_BEFORE_ALL before-* Event-type that fires before each query.
EventHandler::EVENT_AFTER_ALL after-* Event-type that fires after each query.
EventHandler::EVENT_BEFORE_QUERY before-query Event-type that fires before a raw query is executed.
EventHandler::EVENT_AFTER_QUERY after-query Event-type that fires after a raw query is executed
EventHandler::EVENT_BEFORE_SELECT before-select Event-type that fires before select query.
EventHandler::EVENT_AFTER_SELECT after-select Event-type that fires after insert query.
EventHandler::EVENT_BEFORE_INSERT before-insert Event-type that fires before insert query
EventHandler::EVENT_AFTER_INSERT after-insert Event-type that fires after insert query.
EventHandler::EVENT_BEFORE_UPDATE before-update Event-type that fires before update query.
EventHandler::EVENT_AFTER_UPDATE after-update Event-type that fires after update query.
EventHandler::EVENT_BEFORE_DELETE before-delete Event-type that fires before delete query.
EventHandler::EVENT_AFTER_DELETE after-delete Event-type that fires after delete query.

Registering event

You can easily register a new event either by using the registerEvent method on either the QueryBuilderHandler, Connection or EventHandler class.

The event needs a custom callback function with a EventArguments object as parameters.

Examples:

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', action in a closure, 'table name').

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 event

Use cases

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

Notes

Exceptions

This is a list over exceptions thrown by pecee-pixie.

All exceptions inherit from the base Exception class.

Exception name
ColumnNotFoundException
ConnectionException
DuplicateColumnException
DuplicateEntryException
ForeignKeyException
NotNullException
TableNotFoundException
Exception

Getting sql-query from exceptions

If an error occurs and you want to debug your query - you can easily do so as all exceptions thrown by Pixie will contain the last executed query.

You can retrieve the QueryObject by calling

Credits

This project is based on the original Pixie project by the incredible talented usmanhalalit.

Thanks to all the people that have contributed and the users enjoying our library.

Here's some of our references:

Licence

Licensed under the MIT licence.

The MIT License (MIT)

Copyright (c) 2016 Simon Sessingø / pecee-pixie

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

All versions of pixie with dependencies

PHP Build Version
Package Version
Requires php Version >=7.1
ext-pdo Version *
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 pecee/pixie contains the following files

Loading the files please wait ....