Download the PHP package dilovanmatini/query-builder without Composer

Query Builder

Enables PHP developers to build SQL queries similar to native language syntax.

Requirements

• PHP >= 8.1
• PDO PHP Extension

Installation

Usage

Using PDO Instance:

In Laravel:

In Laravel, the connection detects automatically. You don't need to set it.

Using Database Credentials:

Configuration

If you set a valid connection you don't need to set host, port, database, username, password, and charset options.

Documentation

• Select
  • Select
  • From
  • Joins
  • Where
  • Group By
  • Order By
  • Having
  • Limit
  • Offset
  • Fetch, Fetch ALL, and Statement
  • Helpers
  • Raw
  • Param
  • Now
• Insert
  • Columns
  • Values
• Update
  • Set
  • Where
• Delete
  • Where

Select

The select() method is used to add SELECT clause to the query to specify which columns you would like to retrieve from the database. The select() method accepts list of arguments as columns, so you can pass the columns as a comma-separated list or as an array.

To retrieve all columns from a table, you may use the select() method without passing any arguments.

Note: The second code is SQL code that will be generated by the first code.

To retrieve a single column, pass the name of the column as the first argument to the select() method.

To retrieve multiple columns, pass the names of the columns as an array to the select() method.

Or you can pass the columns as a comma-separated list to the select() method.

Multiple usage of select() in one query:

select() has some helpers to make it easier to write queries:

Alias

The alias() method is used to add alias for the columns. The alias() method accepts two arguments. The first argument is alias name and the second argument is the list of columns.

Count

The count() method is used to add COUNT() function to the query. The count() method accepts two arguments. The first argument is the column name and the second argument is optional and is used to specify the alias for the column.

You can also use as() method instead of passing the alias as the second argument.

The same way for the sum min max avg methods.

From

The from() method is used to add FROM clause to the query to specify the table from which you would like to retrieve data. The from() method accepts a string variable or a model class name as its first argument. The second argument is optional and is used to specify the alias for the table.

Or using Model class name For developers who use MVC framework like Laravel

You can also specify the alias for the table as the second argument to the from() method.

Or using the as() method

Joins

The joins methods are used to join tables in a query.

The leftJoin() rightJoin() crossJoin() innerJoin fullJoin() methods accept the table name as the first argument and the alias for the table as the second argument. You can use as() method instead of passing the alias as the second argument.

If you don't provide alias for the tables, the table name will be used as the alias when you have more than one table in the query.

You can also use the on() method to specify the join condition.

Using more than one joins in a query.

Where

The where() method is used to add a WHERE clause to the query. The where() method accepts three arguments. The first argument is required and others are optional.

If you pass only one argument to the where() method, it will be considered as the full condition consists of column name, operator, and value.

If you pass two arguments to the where() method, the first argument will be considered as the column name and the second argument will be considered as the value. The = operator will be used as the default operator.

If you pass three arguments to the where() method, the first argument will be considered as the column name, the second argument will be considered as the operator, and the third argument will be considered as the value.

Example using only one argument:

Example using two arguments:

Example using three arguments:

Note: if you want to pass the RAW value as the second and third arguments, you should use the QB::raw() method.

You can also use the and() and or() methods to add more conditions to the WHERE clause.

Using the and() method:

Using the or() method:

Using the and() and or() methods together:

You can use group conditions using the where() and() or() on() having methods. Especially when you use the and() and or() methods together.

Where helpers

The where() method also accepts the group of method helpers to provide more flexibility to the query.

List of where helpers:

• QB::equal( $value )
• QB::notEqual( $value )
• QB::lessThan( $value )
• QB::lessThanOrEqual( $value )
• QB::greaterThan( $value )
• QB::greaterThanOrEqual( $value )
• QB::like( $value )
• QB::notLike( $value )
• QB::between( $value1, $value2 )
• QB::notBetween( $value1, $value2 )
• QB::in( $values )
• QB::notIn( $values )
• QB::isNull()
• QB::isNotNull()
• QB::isEmpty()
• QB::isNotEmpty()

All the above helpers can be used as the second or third argument of the where() and() or() on() having methods.

Some examples:

Note: like() and notLike() don't make the value as placeholder, so you can pass the value directly. But if you want to make the value as placeholder, you can use the QB::param() method for the value.

Group by

The groupBy() method is used to add a GROUP BY clause to the query. The groupBy() method accepts list of columns as arguments.

Order by

The orderBy() method is used to add a ORDER BY clause to the query. The orderBy() method accepts multiple arguments.

If you don't pass ASC or DESC, the ASC will be used as the default order.

Having

The having() method is used to add a HAVING clause to the query. The having() method is similar to the where() method.

Limit

The limit() method is used to add a LIMIT clause to the query. The limit() method accepts one argument.

You can also pass the offset as the second argument.

Offset

The offset() method is used to add a OFFSET clause to the query. The offset() method accepts one argument.

The offset() method must be used with the limit() method.

Fetch, FetchAll, and Statement

The fetch() method is used to fetch data from the database. The fetch() method accepts one argument as the fetch mode.

The default fetch mode is PDO::FETCH_OBJ. You can pass any fetch mode from the PDO class.

To fetch data as an object as stdClass instance:

You can use fetch when you want to fetch only one row.

To fetch data as an associative array:

The fetchAll() method is used to fetch all data from the database. The fetchAll() method accepts one argument as the fetch mode.

You can use fetchAll when you want to fetch all rows.

The statement() method is used to get the PDOStatement object.

You can use statement when you want to use the PDOStatement methods.

Raw

The raw() method is used to return the raw query string. The raw() method accepts one argument to indicate whether you want the query as a string or an stdClass object including parameters used as placeholders.

Helpers

Raw

The raw() method is used to add a raw string to the query. The raw() method accepts one argument.

Param

The param() method is used to add the value as placeholder to the query. The param() method accepts two arguments. The first one is required as the value and the second one is optional as the name of the placeholder.

You don't need to use the param() method for the where() clause. The where() automatically adds the value as placeholder.

Now

The now() method is used to add the current date and time to the query.

Some examples from simple to complex

Insert

The QB::insert() method is used to create an INSERT query. The insert() method accepts one argument as the table name.

You can also use insertInto() as well. It is an alias of insert() method.

You can use Model classes instead of table names.

Columns

The columns() method is used to add COLUMNS to the INSERT query. The columns() method is optional and accepts the below arguments:

• A string as RAW SQL.
• An array as column names.

If you pass a string to the columns(), the values() method must be string too.

If you pass an array to the values() method, you don't need to use the columns() method.

Values

The values() method is used to add VALUES to the INSERT query. The values() method accepts the below arguments:

• A string as RAW SQL.
• An array as column names and values.

When you pass an array to the values() method, the keys of the array are the column names and the values of the array are the values of the columns.

The values of the array will be placeholders automatically.

To execute the query, you can use the run() or execute() methods.

Update

The QB::update() method is used to create an UPDATE query. The update() method accepts two arguments as the table name and the alias of the table.

You can also use as() method to set the alias of the table.

You can use Model classes instead of table names.

Set

The set() method is used to add SET to the UPDATE query. The set() method accepts the below arguments:

• A string as RAW SQL.
• An array as column names and values.

When you pass an array to the set() method, the keys of the array are the column names and the values of the array are the values of the columns.

The values of the array will be placeholders automatically.

To use where() method with update() method, please see the Where section.

Note: you cannot use update() method without using where() method.

To execute the query, you can use the run() or execute() methods.

Delete

The QB::delete() method is used to create a DELETE query. The delete() method accepts two arguments as the table name and the alias of the table.

You can also use as() method to set the alias of the table.

You can also use deleteFrom() as well. It is an alias of delete() method.

You can use Model classes instead of table names.

To use where() method with delete() method, please see the Where section.

Note: you cannot use delete() method without using where() method.

To execute the query, you can use the run() or execute() methods.

You can get SQL query as the string by ending the query with raw() method in SELECT, INSERT, UPDATE and DELETE queries.

License

This project is open-sourced software licensed under the MIT License - see the LICENSE file for details.