PHP code example of phossa / phossa-query

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

phossa / phossa-query example snippets

  use Phossa\Query\Builder;
  use Phossa\Query\Dialect\Mysql;

  // a builder with mysql dialect,default 'users' table
  $users = new Builder(new Mysql(), 'users');

  // SELECT * FROM `users` LIMIT 10
  $sql = $users->select()->limit(10)->getStatement();

  // INSERT INTO `users` (`usr_name`) VALUES ('phossa')
  $sql = $users->insert(['usr_name' => 'phossa'])->getStatement();

  // A new builder (cloned) with default table 'sales'
  $sales = $users->table(['sales' => 's']);
  $query = $sales->select()->where('user_id', 12);

  // SELECT * FROM `sales` AS `s` WHERE `user_id` = ?
  $sql = $query->getStatement(['positionedParam' => true]);

  // value bindings: [12]
  $val = $query->getBindings();
  

    // SELECT `user_name` AS `n` FROM `users`
    $query = $users->select('user_name', 'n');
    

    // SELECT `id`, `user_name` AS `n` FROM `users`
    $query = $users->select()->col(['id', 'user_name' => 'n']);

    // same as above
    $query = $users->select()->col('id')->field('user_name', 'n');
    

    // SELECT COUNT(user_id) AS `cnt` FROM `users`
    $query = $users->select()->colRaw(['COUNT(user_id)' => 'cnt']);
    

    // SELECT COUNT(`user_id`) AS `cnt`, MAX(`user_id`) AS `max_id` FROM `users`
    $query = $users->select()->count('user_id', 'cnt')->max('user_id', 'max_id');
    

    // SELECT CONCAT(`user_name`, "XXX") AS `new_name` FROM `users`
    $query = $users->select()->func('CONCAT(%s, "XXX")', 'user_name', 'new_name');
    

    // SELECT DISTINCT `user_alias` FROM `users`
    $query = $users->select('user_alias')->distinct();
    

    // SELECT * FROM `sales` AS `s`
    $query = $users->select(false)->from('sales', 's');
  

    // SELECT * FROM `users`, `sales`
    $query = $users->select()->from('sales');
    

    // SELECT * FROM `users` AS `u`, `accounts` AS `a`
    $query = $users->select()->from(['users' => 'u', 'accounts' => 'a']);
    

    // builder without default table[s]
    $builder = $users->table(false);

    // SELECT * FROM (SELECT `user_id` FROM `oldusers`) AS `u`
    $query = $builder->select()->from(
        $builder->select('user_id')->from('oldusers'), 'u'
    );
    

    // SELECT `group_id`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`
    $query = $users->select()->col('group_id')->count('*', 'cnt')->groupBy('group_id');
    

    // SELECT `group_id`, `age`, COUNT(*) AS `cnt` FROM `users` GROUP BY `group_id`, age ASC
    $query = $users->select()->col('group_id')->col('age')->count('*', 'cnt')
        ->groupBy('group_id')->groupByRaw('age ASC');
    

    // SELECT * FROM `users` INNER JOIN `accounts` ON `users`.`id` = `accounts`.`id`
    $query = $users->select()->join('accounts', 'id');
    

    // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
    $query = $users->select()->join('accounts a', 'id');
    

    // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`user_id`
    $query = $users->select()->join('accounts a', 'id', 'user_id');
    

    // SELECT * FROM `users` INNER JOIN `accounts` AS `a` ON `users`.`id` <> `a`.`user_id`
    $query = $users->select()->join('accounts a', 'id', '<>', 'user_id');
    

    // SELECT * FROM `users`
    // INNER JOIN `sales` AS `s` ON `users`.`uid` = `s`.`uid`
    // INNER JOIN `order` AS `o` ON `users`.`uid` = `o`.`o_uid`
    $query = $users->select()
                ->join('sales s', 'uid', '=', 'uid')
                ->join('order o', 'uid', 'o_uid')
                ->getStatement();
    

    // SELECT * FROM `users` INNER JOIN (SELECT `uid` FROM `oldusers`) AS `x`
    // ON `users`.`uid` = `x`.`uid`
    $query = $users->select()->join(
        $builder->select('uid')->from('oldusers')->alias('x'),
        'uid'
    );
    

    // SELECT * FROM `users` OUTER JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
    $query = $users->select()->outerJoin('accounts a', 'id');

    // SELECT * FROM `users` NATURAL JOIN `accounts` AS `a` ON `users`.`id` = `a`.`id`
    $query = $users->select()->realJoin('NATURAL', 'accounts a', 'id');
    

    // SELECT * FROM `users` LIMIT 30 OFFSET 10
    $query = $users->select()->limit(30, 10);

    // SELECT * FROM `users` LIMIT 20 OFFSET 15
    $query = $users->select()->limit(20)->offset(15);
    

    // SELECT * FROM `users` LIMIT 30 OFFSET 60
    $query = $users->select()->page(3, 30);
    

    // SELECT * FROM `users` ORDER BY `age` ASC, `score` DESC
    $query = $users->select()->orderByAsc('age')->orderByDesc('score');
    

    // SELECT * FROM `users` ORDER BY age ASC, score DESC
    $query = $users->select()->orderByRaw('age ASC, score DESC');
    

    // SELECT * FROM `users` WHERE age > 18
    $query = $users->select()->where('age > 18');

    // SELECT * FROM `users` WHERE `age` = 18
    $query = $users->select()->where('age', 18);

    // SELECT * FROM `users` WHERE `age` < 18
    $query = $users->select()->where('age', '<', 18);
    

    // SELECT * FROM `users` WHERE `age` > 18 AND `gender` = 'male'
    $query = $users->select()->where(['age' => ['>', 18], 'gender' => 'male']);

    // same as above
    $query = $users->select()->where('age', '>', 18)->where('gender','male');
    

    // SELECT * FROM `users` WHERE (`id` = 1 OR (`id` < 20 OR `id` > 100))
    // OR `name` = 'Tester'
    $query = $users->select()->where(
                $builder->expr()->where('id', 1)->orWhere(
                    $builder->expr()->where('id', '<', 20)->orWhere('id', '>', 100)
                )
             )->orWhere('name', 'Tester');
    

    // SELECT * FROM `users` WHERE age = 18 OR score > 90
    $query = $users->select()->whereRaw('age = 18')->orWhereRaw('score > 90');
    

    // SELECT * FROM `users` WHERE NOT `age` = 18 OR NOT `score` > 90
    $query = $users->select()->whereNot('age', 18)->orWhereNot('score', '>', 90);
    

    // SELECT * FROM `users` WHERE `age` IN (10,12,15,18,20) OR `score` NOT BETWEEN 90 AND 100
    $query = $users->select()->whereIn('age', [10,12,15,18,20])
            ->orWhereNotBetween('score', 90, 100);
    

    // SELECT * FROM `users` WHERE `age` IS NULL OR `score` IS NOT NULL
    $query = $users->select()->whereNull('age')->orWhereNotNull('score');
    

    $qry1  = $users->select('user_id')->where('age', '>', 60);
    $sales = $users->table('sales');

    // SELECT * FROM `sales` WHERE EXISTS (SELECT `user_id` FROM `users`
    // WHERE `age` > 60)
    $sql = $sales->select()->whereExists($qry1)->getStatement();
    

    // SELECT * FROM `users` HAVING `age` = 10 OR `level` > 20
    $query = $users->select()->having('age', 10)->orHaving('level', '>', 20);
    

    // SELECT * FROM `users` UNION SELECT * FROM `oldusers1`
    // UNION ALL SELECT `user_id` FROM `oldusers2`
    $sql = $users->select()
            ->union()
                ->select()->from('oldusers1')
            ->unionAll()
                ->select('user_id')->from('oldusers2')
                ->getStatement()
    

  // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa')
  $sql = $users->insert()->set('uid', 2)->set('uname', 'phossa')
      ->getStatement();

  // same as above, with array notation
  $sql = $users->insert()->set(['uid' => 2, 'uname' => 'phossa'])
      ->getStatement();
  

  // INSERT INTO `users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test')
  $query = $users->insert()
            ->set(['uid' => 2, 'uname' => 'phossa'])
            ->set(['uid' => 3, 'uname' => 'test']);
  

  // INSERT INTO `users` (`uid`, `uname`, `phone`)
  // VALUES (2, 'phossa', DEFAULT), (3, 'test', '1234')
  $query = $users->insert([
      ['uid' => 2, 'uname' => 'phossa'],
      ['uid' => 3, 'uname' => 'test', 'phone' => '1234']
  ]);
  

  // INSERT INTO `users` (`uid`, `uname`, `phone`)
  // VALUES (2, 'phossa', NULL), (3, 'test', '1234')
  $sql = $query->getStatement(['useNullAsDefault' => true]);
  

  // INSERT INTO `users` (`uid`, `uname`)
  // SELECT `user_id`, `user_name` FROM `oldusers`
  $query = $users->insert()->set(['uid', 'uname'])
               ->select(['user_id', 'user_name'])
               ->from('oldusers');
  

  // UPDATE `users` SET `user_name` = 'phossa' WHERE `user_id` = 3
  $query = $users->update(['user_name' => 'phossa'])->where('user_id', 3);

  // UPDATE `users` SET `user_name` = 'phossa', `user_addr` = 'xxx'
  // WHERE `user_id` = 3
  $query = $users->update()->set('user_name','phossa')
      ->set('user_addr', 'xxx')->where('user_id', 3);
  

  // UPDATE IGNORE `users` SET `user_id` = user_id + 10 ORDER BY `user_id` ASC LIMIT 10
  $query = $users->update()->addHint('IGNORE')->set('user_id', $builder->raw('user_id + 10'))
      ->orderByASC('user_id')->limit(10);
  

  // REPLACE LOW_PRIORITY INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
  $query = $users->replace(['user_id' => 3, 'user_name' => 'phossa'])
      ->addHint('low_priority');
  

  // INSERT INTO `users` (`user_id`, `user_name`) VALUES (3, 'phossa')
  // ON CONFLICT REPLACE
  $query = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);
  

  // DELETE FROM `users` WHERE `user_id` > 10 ORDER BY `user_id` ASC LIMIT 10
  $query = $users->delete()->where('user_id', '>', 10)
      ->orderByAsc('user_id')->limit(10);
  

  // DELETE `users`.* FROM `users` AS `u` INNER JOIN `accounts` AS `a`
  // ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 0
  $query = $users->delete('users')->from('users', 'u')
      ->join('accounts a', 'user_id')->where('a.total_amount', '<', 0);
  

  $builder = new Builder();

  $builder->create()->table('new_table')
      ->temp()
      ->ifNotExists()
      ->addCol('id', 'INT')
          ->notNull()
          ->autoIncrement()
      ->addCol('name', 'VARCHAR(20)')
          ->notNull()
          ->unique()
          ->defaultValue('NONAME')
      ->addCol('alias', 'VARCHAR(10)')
          ->colConstraint('CHECK ()')
      ->primaryKey(['id'])
      ->uniqueKey(['name(4) ASC', 'alias'], 'ON CONFLICT REPLACE')
      ->uniqueKey(['id', 'alias'], 'ON CONFLICT ROLLBACK')
      ->constraint('FOREIGN KEY (...)')
      ->tblOption('DELAY_KEY_WRITE=1')
      ->tblOption('MAX_ROWS=100')
      ->getStatement([
          'seperator' => "\n",
          'indent'    => "  ",
      ]);
  

  // SELECT
  //     *
  // FROM
  //     "Users"
  // WHERE
  //    ("age" < 18 OR "gender" = 'female')
  //    OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female'))
  $query = $builder->select()->from('Users')->where(
      $builder->expr()->where('age', '<', 18)->orWhere('gender', 'female')
  )->orWhere(
      $builder->expr()->where('age', '>' , 60)->orWhere(
          $builder->expr()->where('age', '>', 55)->where('gender', 'female')
      )
  );
  

  $builder = $users->table(false);

  // SELECT * FROM `users` INNER JOIN `sales`
  // (ON `users`.`uid` = `sales`.`s_uid` OR `users`.`uid` = `sales`.`puid`)
  $sql = $users->select()->join('sales',
      $builder->expr()->on('users.uid', 'sales.s_uid')->orOn('users.uid', 'sales.puid')
  )->getStatement();
  

  // SELECT id FROM "students" WHERE "time" = NOW()
  $query = $builder->select()->field($builder->raw("id"))
      ->from("students")->where("time", $builder->raw('NOW()'));
  

  // SELECT * FROM "students" WHERE "age" IN RANGE(1, 1.2)
  $query = $builder->select()->from("students")->where("age", "IN",
      $builder->raw('RANGE(?, ?)', 1, 1.2));
  

  // INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa')
  // ON DUPLICATE KEY UPDATE id=id+10
  $query = $users->insert()->set('id', 3)->set('name', 'phossa')
      ->before('INTO', 'IGNORE')
      ->after('VALUES', 'ON DUPLICATE KEY UPDATE id=id+?', 10);
  

  $query = $users->select()->where("user_id", 10);

  // SELECT * FROM "users" WHERE "user_id" = ?
  $sql = $query->getPositionedStatement();

  // values to bind: [10]
  $val = $query->getBindings();
  

  $query = $users->select()->where("user_name", ':name');

  // SELECT * FROM "users" WHERE "user_name" = :name
  $sql = $query->getNamedStatement();
  

  $users = new Builder(new Mysql(), 'users', ['autoQuote' => false]);
  

  $sql = $users->select()->getStatement(['autoQuote' => false]);