jbzoo / sqlbuilder
Simple SQL query constructor
Installs: 2 588
Dependents: 1
Suggesters: 0
Security: 0
Stars: 2
Watchers: 3
Forks: 1
Open Issues: 0
Requires
- php: >=5.3.10
Requires (Dev)
- jbzoo/phpunit: dev-master
This package is auto-updated.
Last update: 2021-04-25 12:03:16 UTC
README
PHP library description
Init builder
use JBZoo\SqlBuilder\SqlBuilder; // composer autoload.php require_once './vendor/autoload.php'; // register database database type, connection and table prefix (if exists) $connection = mysqli_connect('127.0.0.1', 'root', '', 'sqlbuilder', '3306'); SqlBuilder::set('mysqli', $connection, 'prefix_');
SELECT queries
use JBZoo\SqlBuilder\Query\Select; echo (new Select('table', 'tTable')); // or __toString() method
SELECT * FROM `table` AS `tTable`
Simple SELECT query
$select = (new Select('#__items', 'tItem')) ->select('tItem.item_id', 'id') ->leftJoin(['#__categories', 'tCategory'], 'tCategory.id = tItem.category_id') ->where(['tItem.name', ' = ?s'], "O'Realy") ->where('tItem.name IN ?a', ['42', '43', '44']) ->group('tItem.author') ->order('tItem', 'desc') ->limit(5, 10);
SELECT `tItem`.`item_id` FROM `prefix_items` AS `tItem` LEFT JOIN `prefix_categories` AS `tCategory` ON (tCategory.id = tItem.category_id) WHERE `tItem`.`name` = 'O\'Realy' AND tItem.name IN ('42', '43', '44') GROUP BY `tItem`.`author` ORDER BY `tItem` DESC LIMIT 10, 5
Flexible and safe WHERE conditions
$select = (new Select(['items', 'tAlias'])) ->where("prop = 'O\\'Realy'") // old school ->where('prop = ?s', "O'Realy") // escape string ->where(['prop', '= ?s'], "O'Realy", 'or') // quote field ->whereOR(['tAlias.prop', '=', '?s'], "O'Realy"); // more syntax suger :)
SELECT * FROM `items`AS `tAlias` WHERE prop = 'O\'Realy' AND prop = 'O\'Realy' OR `prop` = 'O\'Realy' OR `tAlias`.`prop` = 'O\'Realy'
Escaping other variable types
$select = (new Select(['items', 'tAlias'])) ->where('prop <> ?e', 'tAlias.col') // entities ->where('prop < ?i', ' -10,56 ') // integer ->where('prop > ?f', ' -10,56 ') // float ->where('prop = ?b', 1) // bool TRUE ->where('prop <> ?b', 0) // and FALSE ->where('prop ?n', 0) // IS [NOT] NULL ->where('prop IN ?a', [null, '1', "'qwerty'"]); // array
SELECT * FROM `items` AS `tAlias` WHERE prop <> `tAlias`.`col` AND prop < -10 AND prop > -10.56 AND prop = TRUE AND prop <> FALSE AND prop IS NULL AND prop IN (NULL, '1', '\'qwerty\'')
WHERE groups
$select = (new Select(['items', 'tAlias'])) ->whereGroup([ ['tAlias.prop > ?i', 42], [['tAlias.prop', '< ?i'], 4242], ]) ->whereGroup([ ['prop NOT IN ?a', [0, 1, null, 'Some string']], ['prop ?n', 0], ], 'OR');
SELECT * FROM `items` AS `tAlias` WHERE ( tAlias.prop > 42 AND `tAlias`.`prop` < 4242 ) OR ( prop NOT IN ('0', '1', NULL, 'Some string') AND prop IS NULL )
Other queries
- Delete
- Insert
- Replace
- Union
- Update
License
MIT