masterweber / sqlbuilder
Simple SQL query constructor.
v1.3.1
2021-01-09 07:22 UTC
Requires
- php: >=7.4
- ext-json: *
- ext-mbstring: *
Requires (Dev)
- phpunit/phpunit: ^9
README
If you're looking for something that is not an ORM but can generate SQL for you, you just found the right one.
SQLBuilder is not an ORM system, but a toolset that helps you generate SQL queries in PHP.
Features
- Simple API, easy to remember.
- Fast.
- Zero dependency.
Synopsis
Here is a short example of using
use SQLBuilder\SQLBuilder; use SQLBuilder\Expression\Column; use SQLBuilder\Clause\OrderBy; $builder = new SQLBuilder(); $sql = $builder->select(['t1.column' => 'col1','t2.column' => 'col2']) ->from(['table1' => 't1']) ->join(['table2' => 't2']) ->right() ->on() ->equal('col1', Column::from('t2.col3')) ->where() ->isNotNull('col1') ->orderBy('col2', OrderBy::DESC) ->limit(10) ->offset(10);
SELECT `t1`.`column` AS `col1`, `t2`.`column` AS `col2` FROM `table1` AS `t1` RIGHT JOIN `table2` AS `t2` ON (`col1` = `t2`.`col3`) WHERE `col1` IS NOT NULL ORDER BY `col2` DESC LIMIT 10 OFFSET 10
CRUD Query Examples
Insert
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->insertInto('table_name')->values([5, 2]);
INSERT INTO `table_name` VALUES (5, 2)
Insert Data Only in Specified Columns
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->insertInto('table_name') ->columns(['first', 'second']) ->values([5, 2]);
INSERT INTO `table_name` (`first`, `second`) VALUES (5, 2)
Insert default data
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->insertInto('table_name')->default();
INSERT INTO `table_name` DEFAULT VALUES
Select
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->select() ->from('table_name') ->where() ->equal('column', 5) ->limit(17) ->offset(10);
SELECT * FROM `table_name` WHERE `column` = 5 LIMIT 10 OFFSET 10
With JOIN
use SQLBuilder\SQLBuilder; use SQLBuilder\Expression\Column; use SQLBuilder\Clause\OrderBy; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->select(['t1.column' => 'col1','t2.column' => 'col2']) ->distinct() ->from(['table1' => 't1']) ->join(['table2' => 't2']) ->right() ->on() ->equal('col1', Column::from('t2.col3')) ->where() ->isNotNull('col1') ->orderBy('col2', OrderBy::DESC) ->limit(12745);
SELECT DISTINCT `t1`.`column` AS `col1`, `t2`.`column` AS `col2` FROM `table1` AS `t1` RIGHT JOIN `table2` AS `t2` ON (`col1` = `t2`.`col3`) WHERE `col1` IS NOT NULL ORDER BY `col2` DESC LIMIT 12745
Update
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->update('table_name') ->set(['col1' => 1, 'date' => '2000-01-01']) ->where(['id' => 5]) ->limit(1);
UPDATE `table_name` SET `col1` = 1, `date` = '2000-01-01' WHERE `id` = 5 LIMIT 1
Delete
use SQLBuilder\SQLBuilder; $sqlBuilder = new SqlBuilder(); $sql = $sqlBuilder->delete() ->from('table_name') ->where() ->equal('col1', 2) ->and() ->equal('col2', 23) ->limit(3);
DELETE FROM `table_name` WHERE `col1` = 2 AND `col2` = 23 LIMIT 3
Installation
Install through Composer
composer require masterweber/sqlbuilder
Author
masterWeber master.weber@outlook.com