sqlbuilder / phpsqlbuilder
php query builder to execute sql queries for different database systems.
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();