Download the PHP package francerz/sql-builder without Composer
On this page you can find all versions of the php package francerz/sql-builder. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Informations about the package sql-builder
SQL Builder
A PHP SQL query builder that prioritizes readability and optimal performance with object based construction.
Table of contents
- SQL Builder
- Table of contents
- Installation ↑
- Connect to database ↑
- Basic common usage syntax ↑
- Select query ↑
- Insert query ↑
- Update query ↑
- Delete query ↑
- Build SELECT with WHERE or HAVING clause ↑
- Parentheses syntax
- List of operators ↑
- Building SELECT with JOIN ↑
- SUPPORTED JOIN TYPES
- Examples
- SELECT nesting ↑
- Nesting a Collection of Result Objects
- Nesting the First or Last Result Object
- Transactions ↑
- Executing Stored Procedures ↑
Installation ↑
This package can be installed with composer using following command.
Connect to database ↑
Using an URI string
Using environment variable
Basic common usage syntax ↑
Select query ↑
Insert query ↑
Update query ↑
Delete query ↑
Build SELECT with WHERE or HAVING clause ↑
Bellow are examples of using WHERE
clause which aplies to SELECT
, UPDATE
and DELETE
queries.
Selecting all fields from table
groups
when the value of columngroup_id
is equal to10
.
Selecting all fields from table
groups
when value of columngroup_id
is equals to10
,20
or30
.
Selecting all fields from table
groups
when value of columnteacher
isNULL
.
Selecting all fields from table
groups
when value of columngroup_id
is less or equals to10
and value from columnsubject
contains the word"database"
.
Parentheses syntax
To incorporate highly specific and intricate conditions, it becomes essential to override the default operator precedence, a task traditionally achieved through the use of parentheses in SQL syntax. Within the SQL Builder, this functionality is adeptly handled through the utilization of an anonymous function parameter.
Parentheses anonymous function works in the following syntax:
Selecting all fields from table
groups
when the value ofgroup_id
is equals to10
or is within the range from20
to30
.
List of operators ↑
The library provides a comprehensive array of operators that are largely
consistent across various SQL database engines. To enhance readability, it also
prefixes the and
and or
logical operators for clarity.
SQL Operator | Regular (AND) | AND | OR |
---|---|---|---|
= |
equals($op1, $op2) |
andEquals($op1, $op2) |
orEquals($op1, $op2) |
<> or != |
notEquals($op1, $op2) |
andNotEquals($op1, $op2) |
orNotEquals($op1, $op2) |
< |
lessThan($op1, $op2) |
andLessThan($op1, $op2) |
orLessthan($op1, $op2) |
<= |
lessEquals($op1, $op2) |
andLessEquals($op1, $op2) |
orLessEquals($op1, $op2) |
> |
greaterThan($op1, $op2) |
andGreaterThan($op1, $op2) |
orGreaterThan($op1, $op2) |
>= |
greaterEquals($op1, $op2) |
andGreaterEquals($op1, $op2) |
orGreaterEquals($op1, $op2) |
LIKE |
like($op1, $pattern) |
andLike($op1, $pattern) |
orLike($op1, $pattern) |
NOT LIKE |
notLike($op1, $pattern) |
andNotLike($op1, $pattern) |
orNotLike($op1, $pattern) |
REGEXP |
regexp($op1, $pattern) |
andRegexp($op1, $pattern) |
orRegexp($op1, $pattern) |
NOT REGEXP |
notRegexp($op1, $pattern) |
andNotRegexp($op1, $pattern) |
orNotRegexp($op1, $pattern) |
IS NULL |
null($op) |
andNull($op) |
orNull($op) |
IS NOT NULL |
notNull($op) |
andNotNull($op) |
orNotNull($op) |
BETWEEN |
between($op, $min, $max) |
andBetween($op, $min, $max) |
orBetween($op, $min, $max) |
NOT BETWEEN |
notBetween($op, $min, $max) |
andNotBetween($op, $min, $max) |
orNotBetween($op, $min, $max) |
IN |
in($op, $array) |
andIn($op, $array) |
orIn($op, $array) |
NOT IN |
notIn($op, $array) |
andNotIn($op, $array) |
orNotIn($op, $array) |
About
ConditionList
classThe examples of condition list, functions and operators applies in the same way to
WHERE
,HAVING
andON
clauses.
Building SELECT with JOIN ↑
One of the most common operations in relational databases is merging and
combining data from multiple tables. The join operations allow to combine the
data from multiple tables by using the INNER JOIN
, LEFT JOIN
, RIGHT JOIN
and CROSS JOIN
syntax.
SUPPORTED JOIN TYPES
Query Builder supports many types of JOIN
syntaxes:
SQL Join Syntax Compatibility:
Join Syntax is available to
SELECT
,UPDATE
andDELETE
sql syntax, however, not all database engines might support it.
Examples
Using table aliases to reduce naming length.
Multiple database (same host) select with join.
Selecting fields from joined tables.
Renaming fields from joined tables.
Selecting columns into an external function (cleaner code).
Join tables and subqueries.
SELECT nesting ↑
In some cases, simple database table joining isn't sufficient for meeting all data requirements. It's common to need to execute additional filtered queries for each row in the result of a primary query.
However, this approach often leads to overly complex code and performance issues due to increased loops and database access roundtrips. To address these challenges, a more efficient and lightweight syntax is available for querying nested data.
Nesting a Collection of Result Objects
The nestMany
method is used to nest a collection of result objects within each
row of the primary query's result. In the provided example, this is used to
associate multiple students with their respective groups. This approach is
suitable when you expect multiple related records for each main record.
Result:
Nesting the First or Last Result Object
On the other hand, the linkFirst
method is employed to link only the first
result object from a secondary query with each row of the primary query's
result. In the given code snippet, this is used to link the first teacher to
each group. This method is beneficial when you want to link a single related
record to each main record, prioritizing the first match.
Additionally, there is the linkLast
method, which is similar to linkFirst
but instead links the last result object from a secondary query to each row of
the primary query's result. This can be useful in scenarios where you want to
prioritize the most recent or latest related record for each main record.
Result:
By choosing the appropriate nesting mode (nestMany
, linkFirst
, or
linkLast
), you can tailor your queries to efficiently handle nested data based
on your specific data structure and requirements.
Legacy old nest syntax
There is a legacy nest syntax, that stills working underhood.
Transactions ↑
One of the most important features in databases is to keep data consistency across multiple records that might be stored in multiple tables.
Executing Stored Procedures ↑
All versions of sql-builder with dependencies
francerz/enum Version ^0.1.1
psr/http-message Version ^1.0