sqlbuilder/phpsqlbuilder

php query builder to execute sql queries for different database systems.

1.0.0 2023-03-15 22:43 UTC

This package is auto-updated.

Last update: 2024-05-30 01:06:12 UTC


README

PHP query builder to create and execute SQL queries for different database systems.

The query builder support mysql postgresql sql server sqlite and provide plenty of methods to cover the most of SQL statements and apply restrictions and parameters to the data to verify and filter the user inputs in order to prevent SQl injection.

Install via composer

composer require sqlbuilder/phpsqlbuilder

Example

require ('vendor/autoload.php');

// Connect to database
$sql = new PhpSqlBuilder\SQL([
	'driver' => 'mysql', // support (mysql, pgsql, sqlsrv, sqlite)
	'host' => '127.0.0.1',
	'database' => 'test',
	'user' => 'root',
	'password' => '',
	'port' => 3306,
	'charset' => 'utf8',
]);

// Build your SQL Query
$result = $sql->table('users')
              ->select(['id', 'name'])
              ->where('id')->value('>', 16)
              ->orderBy('id')
              ->limit(5)
              ->get();

Available Methods

Method Describe Parameters Output
table() Define the table name. 1. (string) table name ___
get() Return an object with the result of sql query, You have to call this method at the end of the query builder. no ___
all() Select all columns. no SELECT *
select() Select specific columns. 1. (sequential array) columns SELECT columns
selectTop() Select limited rows. (support sql server only) 1. (integer) rows number

2. (sequential array) columns
SELECT TOP 5 columns
distinct() Select distinct rows. 1. (sequential array) columns SELECT DISTINCT columns
alias() Select with alias name. 1. (associative array) column and alias name SELECT column AS alias
max()

min()

sum()

avg()

count()
Select with aggregate functions. The five methods accepts 2 parameters, first one is the column name and the second is *optional the alias name. 1. (string) column name

2. (string) alias name *optional
SELECT MAX(column)
where() Add WHERE clause to specify a condition. 1. (string) column name WHERE column
whereNot() Add NOT keyword to WHERE clause. 1. (string) column name WHERE NOT column
value() Used to specify the operator and the value after WHERE statement. 1. (string) the operator

2. (scalar) the value
= value
isNull() Used with WHERE clause to select null values. no IS NULL
isNotNull() Used with WHERE clause to select non-null values. no IS NOT NULL
like() Used in a WHERE clause to search for a specified pattern. 1. (string) pattern LIKE %pattern%
in() Used in a WHERE clause to specify multiple values. 1. (sequential array) values IN (1,3,6)
between() Used in a WHERE clause selects values within a given range. 1. (scalar) value1

2. (scalar) value2
BETWEEN value1 AND value2
and()

or()

not()
These operetors can be combined with the query to add multiple conditions. (string) column name AND column / OR column
innerJoin()

leftJoin()

rightJoin()

fullJoin()
Join multiple tables. 1. (string) table name

2. (string) column1

3. (string) operator

4. (string) column2
INNER JOIN table ON column1 = column2
limit() Retrieve limited rows. 1. (interger) rows number LIMIT 5
union()

unionAll()
Used to combine the result of two tables. 1. (sequential array) columns

2. (string) table2
UNION SELECT columns FROM table2
groupBy() Used to arrange identical data into groups. 1. (sequential array) columns GROUP BY columns
having() HAVING clause used with GROUP BY to specify a condition. 1. (string) column name HAVING column
orderBy() Used to sort rows according to specific columns. 1. (sequential array) columns

2. (string) sort ASC/DESC
ORDER BY columns DESC
delete() Generate sql delete statement. no DELETE FROM table
truncate() Generate sql truncate statement. no TRUNCATE table
insert() Generate sql insert statement. 1. (associative array) column and value INSERT INTO table (columns) VALUES (values)
update() Generate sql update statement. 1. (associative array) column and value UPDATE table SET column = value
save() You should call this method at the end of insert, update and delete methods to execute the query. no ___

table

Define the database table name.

$result = $sql->table('tablename')
              ->all()
              ->get();

get

Return an object with the result of sql query, You have to call this method at the end of the query builder.

$result = $sql->table('tablename')
              ->all()
              ->get();

all

Select all columns.

$result = $sql->table('tablename')
              ->all()
              ->get();

select

Select specific columns.

$result = $sql->table('tablename')
              ->select(['col1', 'col2', 'col3'])
              ->get();

selectTop

Select limited rows. (support sql server only)

$result = $sql->table('tablename')
              ->selectTop(4, ['col1', 'col2'])
              ->get();

distinct

Select distinct rows.

$result = $sql->table('tablename')
              ->distinct(['col1', 'col2', 'col3'])
              ->get();

alias

Select with alias name.

$result = $sql->table('tablename')
              ->alias([
              	'column1' => 'alias name',
              	'column2' => 'alias name'
              ])
              ->get();

aggregate functions

Select with aggregate functions (MAX, MIN, COUNT, SUM, AVG).

$result = $sql->table('tablename')
              ->count('id')
              ->max('price', 'unitprice')
              ->min('price')
              ->avg('price')
              ->sum('id')
              ->get();

where

Add where clause to specify a condition.

$result = $sql->table('tablename')
              ->all()
              ->where('col')->value('=', 5)
              ->get();

whereNot

Add NOT keyword to where clause.

$result = $sql->table('tablename')
              ->all()
              ->whereNot('col')->value('<', 5)
              ->get();

isNull isNotNull

// IS NULL
$result = $sql->table('tablename')
              ->all()
              ->where('col')->isNull()
              ->get();

// IS NOT NULL
$result = $sql->table('tablename')
              ->all()
              ->where('col')->isNotNull()
              ->get();

like in between

// LIKE
$result = $sql->table('tablename')
              ->all()
              ->where('col')->like('%pattern%')
              ->get();

// IN
$result = $sql->table('tablename')
              ->all()
              ->where('col')->in([45, 76, 88])
              ->get();

// BETWEEN
$result = $sql->table('tablename')
              ->all()
              ->where('col')->between(2, 10)
              ->get();

and or not

These operetors can be combined with the query to add multiple conditions.

// AND
$result = $sql->table('tablename')
              ->all()
              ->where('col')->value('=', 'value')
              ->and('col2')->value('=', 'value')
              ->get();

// OR
$result = $sql->table('tablename')
              ->all()
              ->where('col')->value('=', 'value')
              ->or('col2')->value('=', 'value')
              ->get();

// NOT (does not accept parameters)
$result = $sql->table('tablename')
              ->all()
              ->where('col')->not()->value('=', 'value')
              ->get();

joins

// INNER JOIN
$result = $sql->table('table1')
              ->innerJoin('table2', 'table1.column', '=', 'table2.column')
              ->get();

// LEFT JOIN
$result = $sql->table('table1')
              ->leftJoin('table2', 'table1.column', '=', 'table2.column')
              ->get();

// RIGHT JOIN
$result = $sql->table('table1')
              ->rightJoin('table2', 'table1.column', '=', 'table2.column')
              ->get();

// FULL OUTER JOIN
$result = $sql->table('table1')
              ->fullJoin('table2', 'table1.column', '=', 'table2.column')
              ->get(); 

// CROSS JOIN (accept 1 string parameter)
$result = $sql->table('table1')
              ->crossJoin('table2')
              ->get();

limit

Retrieve limited rows.

$result = $sql->table('tablename')
              ->all()
              ->limit(5)
              ->get();

union unionAll

Used to combine the result of two tables.

// UNION
$result = $sql->table('tablename')
              ->select(['column1', 'column2'])
              ->union(['column1', 'column2'], 'table2')
              ->get();

// UNION ALL
$result = $sql->table('tablename')
              ->select(['column1', 'column2'])
              ->unionAll(['column1', 'column2'], 'table2')
              ->get();

groupBy

Used to arrange identical data into groups.

$result = $sql->table('tablename')
              ->select(['col1', 'col2'])
              ->groupBy(['col1'])
              ->get();

having

Used with GROUP BY to specify a condition.

$result = $sql->table('tablename')
              ->select(['col1', 'col2'])
              ->groupBy(['col1'])
              ->having('col1')->value('=', 'value')
              ->get();

orderBy

Used to sort rows according to specific columns.

$result = $sql->table('tablename')
              ->all()
              ->orderBy(['col'], 'ASC') // default DESC
              ->get();

delete

You must use save() method at the end of insert, update and delete methods to execute the sql query.

// EX 1
$sql->table('tablename')
    ->delete()
    ->where('col')->value('=', 1)
    ->save(); // execute

// EX 2
$sql->table('tablename')
    ->delete()
    ->where('col')->in(['val1', 'val2', 'val3'])
    ->save();

truncate

// EX 1
$sql->table('tablename')
    ->truncate()
    ->save();

insert

$sql->table('tablename')
	->insert([
        'id' => 84,
        'name' => 'ahmed',
        'email' => 'ahmed@gmail.com'
	])->save(); 

update

$sql->table('tablename')
    ->update([
        'name' => 'omar',
        'email' => 'omar@gmail.com'
    ])
    ->where('id')->value('=', 88)
    ->save();