PHP SQL BUILDER

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();