PHP QueryBuilder QueryBuilder Examples

Example #1

File: Delete.php Project: Kekos/query-builder

 private function sanitizeField($field)
 {
     if ($field instanceof Raw) {
         return (string) $field;
     }
     return QueryBuilder::sanitizeField($field, $this->adapter->getSanitizer());
 }

Example #2

File: ModelQueryBuilder.php Project: Kekos/booya-database

 public function getCount()
 {
     $this->builder->columns(QueryBuilder::raw('COUNT(*) AS cnt'));
     $this->prepareAndExecute();
     $result = $this->stmt->fetchObject();
     return is_object($result) ? $result->cnt : 0;
 }

Example #3

File: CriteriaBuilder.php Project: Kekos/query-builder

 protected function sanitizeField($field)
 {
     if ($field instanceof Raw) {
         return (string) $field;
     } else {
         if ($field instanceof \Closure) {
             return $field;
         }
     }
     return QueryBuilder::sanitizeField($field, $this->adapter->getSanitizer());
 }

Example #4

File: ModelSortable.php Project: Kekos/booya-database

 public static function getMaxSortorder($ref_model)
 {
     $sort_join_on = static::$sort_join_on;
     $mqb = self::select(function ($qb) use($sort_join_on, $ref_model) {
         $qb->columns(QueryBuilder::raw("MAX(sortorder) AS max_sortorder"));
         if ($sort_join_on !== null) {
             $qb->where($sort_join_on, '=', $ref_model->{$sort_join_on});
         }
     });
     return $mqb->fetchSingleField('max_sortorder');
 }

Example #5

File: delete.php Project: Kekos/query-builder

<pre>
<?php 
require '../vendor/autoload.php';
use QueryBuilder\QueryBuilder as QB;
QB::setAdapter(new QueryBuilder\MySqlAdapter());
$result = QB::delete('user')->where('id', '=', 2)->toSql();
var_dump($result['sql'], $result['params']);
?>
</pre>

Example #6

File: update.php Project: Kekos/query-builder

<pre>
<?php 
require '../vendor/autoload.php';
use QueryBuilder\QueryBuilder as QB;
QB::setAdapter(new QueryBuilder\MySqlAdapter());
$result = QB::update('user')->set(['username' => 'new_username', 'firstname' => 'New firstname'])->where('id', '=', 2)->toSql();
var_dump($result['sql'], $result['params']);
$result = QB::update('user')->set(['firstname' => QB::raw('REPLACE(firstname, ?, ?)', array('Doe', 'Eod'))])->where('firstname', 'LIKE', '%Doe%')->toSql();
var_dump($result['sql'], $result['params']);
?>
</pre>

Example #7

File: select.php Project: Kekos/query-builder

<pre>
<?php 
require '../vendor/autoload.php';
use QueryBuilder\QueryBuilder as QB;
QB::setAdapter(new QueryBuilder\MySqlAdapter());
$result = QB::select(['user', 'u'])->columns(['u.id', 'uname' => 'username'])->join(['user_permission', 'p'], QB::raw('p.user_id = u.id'))->limit(5, 0)->groupby(['u.id'])->orderby(['username ASC', 'firstname ASC'])->where('firstname', '=', 'Christoffer')->whereNot('u.id', 'IN', array(2))->toSql();
var_dump($result['sql'], $result['params']);
$result = QB::select('user')->orderby('id')->where(function ($qb) {
    $qb->where('name', 'LIKE', '%chris%')->whereOr('username', 'LIKE', '%chris%');
})->where('active', '=', 1)->toSql();
var_dump($result['sql'], $result['params']);
$result = QB::select('user')->where(function ($qb) {
    $qb->where('id', 'BETWEEN', array(2, 5))->whereOr('username', 'LIKE', '%chris%');
})->toSql();
var_dump($result['sql'], $result['params']);
$result = QB::select('user')->columns('id')->columns(['fname' => 'firstname'])->toSql();
var_dump($result['sql'], $result['params']);
?>
</pre>

Example #8

File: index.php Project: Kekos/booya-database

<pre>
<?php 
require '../vendor/autoload.php';
require_once 'User.php';
require_once 'UserPermission.php';
require_once 'config.php';
use QueryBuilder\QueryBuilder as QB;
// Configure database
\Booya\Database\PDO::configure($db_config);
// Configure QueryBuilder
QB::setAdapter(new QueryBuilder\MySqlAdapter());
$user = User::get(1);
if (!$user->exists()) {
    $user->username = '******';
    $user->setPassword('pwd');
    $user->name = 'Christoffer';
    $u_perm = new UserPermission();
    $u_perm->code = 'users';
    $user->permissions->set('users', $u_perm);
    $user->save();
    var_dump($user);
}
foreach ($user->permissions->getAll() as $u_perm) {
    echo $u_perm->code . PHP_EOL;
}
$user = User::get(2);
$user->delete();
?>
</pre>

Example #9

File: insert.php Project: Kekos/query-builder

<pre>
<?php 
require '../vendor/autoload.php';
use QueryBuilder\QueryBuilder as QB;
QB::setAdapter(new QueryBuilder\MySqlAdapter());
$result = QB::insert('user')->values(['username' => 'Kekos', 'firstname' => 'Christoffer'])->toSql();
var_dump($result['sql'], $result['params']);
?>
</pre>