Bloatless Query Builder

A query builder for PDO MySQL and SQLite.

Installation

Using phar

Download the latest phar from the releases page and include/use the lib in your project like any other PHP class:

Using composer

You can install the library using composer:

Manually

Clone or download the files from GitHub into your local project. You can than include/use the library within your project:

Usage

• Query Builder
  • Connections
  • Factory
  • SELECT
  • A simple select
  • Table and column alias
  • Get specific columns
  • First row only
  • Single column as array
  • Counting rows
  • Joins
  • Group by
  • Order by
  • Having
  • Limit and Offset
  • Distinct
  • UPDATE
  • DELETE
  • WHERE
  • Simple where
  • Or where
  • Where in
  • Where not in
  • Or where in
  • Or where not in
  • Where between
  • Or where between
  • Where null
  • Where not null
  • Or where null
  • Or where not null
  • Where raw
  • Or where raw
  • INSERT
  • Single row
  • Multiple rows
  • Last insert id
  • RAW Queries
  • Raw select queries
  • Other raw queries
  • Reset
  • Security

Query Builder

This documentation explains the complete usage API of the Bloatless Query Builder.

Connections

You can define multiple database connections in your projects config.php file.

Factory

The QueryBuilder factory needs to be initialized using a config array providing the connection credentials:

Once initialized the factory can be used to provide query-builder objects for various database operations:

With no arguments provided the default database connection is used. If you want to use a different connection you can pass the connection name as an argument.

SELECT

A simple select

Table and column alias

Aliases can be used on table names as well as on column names.

Get specific columns

First row only

Single column as array

Will fetch an array containing all first names of the customers table.

You can specify a second column which will be used for the keys of the array:

Will fetch an array of all first names using the customer_id as array key.

Counting rows

Joins

You can join tables using the join, leftJoin or rightJoin methods. You can of course join multiple tables.

Group by

Order by

Having

Limit and Offset

Distinct

UPDATE

DELETE

WHERE

You can use various where clauses on all select, update and delete queries:

Simple where

Or where

Where in

Where not in

Or where in

Or where not in

Where between

Or where between

Where null

Where not null

Or where null

Or where not null

Where raw

Or where raw

INSERT

Single row

When inserting a single row, the auto-increment value of the newly added row will be returned.

Multiple rows

You can insert multiple rows at once using the rows method:

Last insert id

In case you need to fetch the id if the last insert manually you can use the getLastInsertId method:

RAW Queries

There will always be some kind of queries you can not build using the methods of a query builder. In those cases you can utilize the RawQueryBuilder which allows you to execute raw queries to the database.

Raw select queries

Other raw queries

Reset

All query builders have a reset method. This method can be used to clear all previously set values without the need of creating a new QueryBuilder object.

Security

All query builders internally use PDO parameter binding to reduce the risk of injection attacks as much as possible. Additionally, table names as well as field names are quoted - so you don't have to worry about that. This works on simple table names or when using aliases. Nevertheless, you should always try to avoid using user input within sql statements!

License

MIT