inspired by https://github.com/lichtner/fluentpdo
"Just for Fun", "NIH", "I can do better" ... choose any.
require_once "b2/autoload.php";
$mysql = new mysqli(/* ... */);
$quote = \b2\Quote::createFromMysqli($mysql);
$b2 = new \b2\B2;
$selectObject = $b2->select('user', 'id > ?', [10])
->leftJoin('payment', 'payment.id = user.id')
->fields(['user.id', 'sum' => $b2->sql('SUM(payment.value)')])
->orderBy('sum', 'DESC')
;
$selectSql = $selectObject->toString($quote);
echo $selectSql;Output (added formatting for easy reading)
SELECT `user`.`id`, SUM(payment.value) AS `sum`
FROM `user`
LEFT JOIN `payment` ON payment.id = user.id
WHERE id > '10' ORDER BY `sum` DESCNo filters
$b2->select('user')->allFields();SELECT * FROM `user`Select by filter
$b2->select('user', 'id', 10)->allFields();
// or
$b2->select('user', 'id = ?', [10])->allFields();
// or
$b2->select('user', ['id' => 10])->allFields();
// or use explicit where(). See below
$b2->select('user')->where('id', 10)->allFields();All of them SQL
SELECT * FROM `user` WHERE `id` = '10'$b2->select('user')->fields(['id', 'alias' => 'name']);
// or
$b2->select('user')->field('id')->field('name', 'alias');SELECT `id`, `name` AS `alias` FROM `user`$b2->select('user')->field('id')->groupBy('level');SELECT `id` FROM `user` GROUP BY `level`Group by multiple columns
$b2->select('user')->field('id')
->groupBy('level')
->groupBy('bYear')
;SELECT `id` FROM `user` GROUP BY `level`, `bYear`$b2->select('user')->field('id')->orderBy('level', 'DESC');SELECT `id` FROM `user` ORDER BY `level` DESCOrdering by multiple columns
$b2->select('user')->field('id')
->orderBy('level', 'DESC')
->orderBy('bYear')
;SELECT `id` FROM `user` ORDER BY `level` DESC, `bYear`Not implemented yet :(
$b2->select('user')->field('id')->limit(10)->offset(20);SELECT `id` FROM `user` LIMIT 10 OFFSET 20$b2->update('user')->set('money', 10)->where('id', 1);
// or
$b2->update('user')->set(['money' => 10])->where(['id' => 1]);
// or
$b2->update('user')->set('`money` = ?', [10])->where('`id` = ?', [1]);
// or
$b2->update('user')
->set('`money` = :money', [':money' => 10])
->where('`id` = :id', [':id' => 1])
;
// or
$b2->update('user')->set("`money` = '10'")->where("`id` = '1'");UPDATE `user` SET `money` = '10' WHERE `id` = '1'See SELECT section
$b2->insert('user', [['id' => 1, 'name' => 'John']]);
// or
$b2->insert('user')
->row(['id' => 1, 'name' => 'John'])
;
// or
$b2->insert('user')
->rows([['id' => 1, 'name' => 'John']])
;INSERT INTO `user`(`id`, `name`) VALUES ('1', 'John')Multi-rows
$b2->insert('user', [
['id' => 1, 'name' => 'John'],
['id' => 2, 'name' => 'Ivan']
]);
// or
$b2->insert('user')
->row(['id' => 1, 'name' => 'John'])
->row(['id' => 2, 'name' => 'Ivan'])
;
// or
$b2->insert('user')
->rows([
['id' => 1, 'name' => 'John']
, ['id' => 2, 'name' => 'Ivan']
])
;INSERT INTO `user`(`id`, `name`) VALUES ('1', 'John'), ('2', 'Ivan')Update all inserting fields
$b2->insert('user', [['id' => 1, 'name' => 'John']])
->onDuplicateKeyUpdate()
;INSERT INTO `user`(`id`, `name`) VALUES ('1', 'John')
ON DUPLICATE KEY UPDATE `id` = VALUES(`id`), `name` = VALUES(`name`)Update specified fields
$b2->insert('user', [['id' => 1, 'name' => 'John', 'age' => 20]])
->onDuplicateKeyUpdate(['name', 'age'])
;
// or
$b2->insert('user', [['id' => 1, 'name' => 'John', 'age' => 20]])
->onDuplicateKeyUpdate('name')
->onDuplicateKeyUpdate('age')
;INSERT INTO `user`(`age`, `id`, `name`) VALUES ('20', '1', 'John')
ON DUPLICATE KEY UPDATE `age` = VALUES(`age`), `name` = VALUES(`name`)Update with expression
$b2->insert('counter')->row(['name' => 'visits', 'count' => 1])
->onDuplicateKeyUpdate([
'count' => $b2->sql('`count` + VALUES(`count`)')
])
;INSERT INTO `counter`(`count`, `name`) VALUES ('1', 'visits')
ON DUPLICATE KEY UPDATE `count` = `count` + VALUES(`count`)$b2->delete('user', 'id', 10);
//or
$b2->delete('user')->where('id', 10);DELETE FROM `user` WHERE `id` = '10'See SELECT section
$select->where('key', 'value');
// or
$select->where(['key' => 'value']);
// or
$select->where('`key` = ?', ['value']);WHERE `key` = 'value'$select
->where('key1', 'value1')
->where('key2', 'value2')
;
// or
$select
->where(['key1' => 'value1', 'key2' => 'value2'])
;WHERE `key1` = 'value1' AND `key2` = 'value2'$select->where($b2->sql('key1 = ? OR key2 = ?', [10, 20]));WHERE key1 = '10' OR key2 = '20'$b2->where('id = ? OR id = ?', [1, 2]);$b2->where('id = :id1 OR id = :id2', [':id1' => 1, ':id2' => 2]);$b2->where('id = :id1 OR id = ?', [':id1' => 1, 2]);$b2->where('id IN(::ids)', ['::ids' => [1, 2, 3]]);id IN('1', '2', '3')