PHP code example of phossa2 / query

1. Go to this page and download the library: Download phossa2/query library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.

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

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

phossa2 / query example snippets


  use Phossa2\Query\Builder;

  // a builder default to table 'Users' and Mysql as default dialect
  $builder = new Builder();
  $users = $builder->table('Users');

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

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

  // reset builder to table 'Sales' as 's'
  $sales = $users->table('Sales', 's');

  // SELECT * FROM `Sales` AS `s` WHERE `user_id` = 12
  $qry = $sales->select()->where('user_id', 12);

  // SELECT * FROM `Sales` AS `s` WHERE `user_id` = ?
  $sql = $qry->getStatement(); // with positioned parameters

  // [12]
  var_dump($qry->getBindings());


    // SELECT * FROM `Users`
    $qry = $users->select();

    // SELECT `user_id`, `user_name` FROM `Users`
    $qry = $users->select('user_id', 'user_name');

    // SELECT `user_id`, `user_name` AS `n` FROM `Users`
    $qry = $users->select()->col('user_id')->col('user_name', 'n');

    // same as above
    $qry = $users->select()->col(['user_id', 'user_name' => 'n']);


    // SELECT COUNT(user_id) AS cnt FROM `Users`
    $qry = $users->select()->colRaw('COUNT(user_id) AS cnt');

    // SELECT CONCAT(user_name, 'x') AS con FROM `Users`
    $qry = $users->select()->colRaw('CONCAT(user_name, ?) AS con', ['x']);


    // SELECT MAX(`user_id`) AS `maxId` FROM `Users`
    $qry = $users->select()->max('user_id', 'maxId');


    // SELECT SUM(DISTINCT `score`) AS `s` FROM `Users`
    $qry = $users->select()->colTpl('SUM(DISTINCT %s)', 'score', 's');

    // SELECT CONCAT(`fname`, ' ', `lname`) AS `fullName` FROM `Users`
    $qry = $users->select()->colTpl("CONCAT(%s, ' ', %s)", ['fname', 'lname'], 'fullName');


    // SELECT (SELECT MAX(`user_id`) FROM `oldUsers`) AS `maxId` FROM `Users`
    $qry = $users->select()->col(
        $users->select()->max('user_id')->table('oldUsers'),
        'maxId'
    );


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

    // SELECT DISTINCT `user_alias` AS `a` FROM `Users`
    $qry = $users->select()->distinct()->col('user_alias', 'a');


    // $sales is a clone of builder $users with table replaced
    $sales = $users->table('Sales');

    // or replace table in the select query object
    $select = $users->select()->table('Sales', 's');

    // SELECT * FROM `Users` AS `u`, `Accounts` AS `a`
    $qry = $users->select()->table(['Users' => 'u', 'Accounts' => 'a']);


    // SELECT * FROM `Users`, `Sales` AS `s`
    $select = $users->select()->from('Sales', 's');

    // builder has two tables now
    $usersAndSales = $users->from('Sales', 's');


    // SELECT * FROM (SELECT `user_id` FROM `oldUsers`) AS `u`
    $qry = $users->select()->table(
        $users->select('user_id')->table('oldUsers'),
        'u'
    );


    // SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id`
    $qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->group('grp_id');


    // SELECT `grp_id`, `age`, COUNT(*) AS `cnt` FROM `Users` GROUP BY `grp_id`, age ASC
    $qry = $users->select('grp_id', 'age')->cnt('*', 'cnt')
        ->group('grp_id')->groupRaw('age ASC');


    // GROUP BY `year` WITH ROLLUP
    $users->select()->groupTpl('%s WITH ROLLUP', 'year')


    // SELECT * FROM `Users` INNER JOIN `Accounts`
    $qry = $users->select()->join('Accounts');

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


    // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id`
    $qry = $users->select()->join(['Accounts', 'a'], 'id');


    // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`user_id`
    $qry = $users->select()->join(['Accounts'], 'a'], ['id', 'user_id']);

    // same as above
    $qry = $users->select()->join(['Accounts'], 'a'], ['Users.id', 'a.user_id']);


    // SELECT * FROM `Users` INNER JOIN `Accounts` AS `a` ON `Users`.`id` <> `a`.`user_id`
    $qry = $users->select()->join(['Accounts', 'a'], ['id', '<>', 'user_id']);


    // SELECT * FROM `Users`
    // INNER JOIN `Sales` AS `s` ON `Users`.`uid` = `s`.`uid`
    // INNER JOIN `Orders` AS `o` ON `Users`.`uid` = `o`.`oid`
    $qry = $users->select()
                ->join(['Sales', 's'], ['uid', '=', 'uid'])
                ->join(['Orders', 'o'], ['uid', 'o.oid']);


    // SELECT * FROM `Users` INNER JOIN (SELECT `uid` FROM `oldUsers`) AS `x` ON `Users`.`uid` = `x`.`uid`
    $qry = $users->select()->join(
        [$builder->select('uid')->from('oldUsers'), 'x'],
        'uid'
    );


    // SELECT * FROM `Users` OUTER JOIN `Accounts` AS `a` ON `Users`.`id` = `a`.`id`
    $qry = $users->select()->outerJoin(['Accounts', 'a'], 'id');

    // SELECT * FROM `Users` NATURAL JOIN Accounts AS a ON Users.id = a.id
    $qry = $users->select()->joinRaw('NATURAL JOIN', 'Accounts AS a ON Users.id = a.id');


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

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


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


    // SELECT * FROM `Users` ORDER BY `age` ASC, `score` DESC
    $qry = $users->select()->order('age')->orderDesc('score');


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


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

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

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


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

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


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


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


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


    // SELECT * FROM `Users` WHERE `age` IN (10,12,15,18,20)
    $qry = $users->select()->where('age', 'IN', [10,12,15,18,20]);

    // SELECT * FROM `Users` WHERE `age` NOT BETWEEN 10 AND 20
    $qry = $users->select()->where('age', 'NOT BETWEEN', [10,20]);


    // SELECT * FROM `Users` WHERE `age` IS NULL
    $qry = $users->select()->where('age', 'IS', NULL);


    // SELECT * FROM `Sales` WHERE EXISTS (SELECT `user_id` FROM `Users`)
    $sql = $sales->select()->where('', 'EXISTS', $users->select('user_id'))->getSql();


    // SELECT * FROM `Users` HAVING `age` = 10 OR `level` > 20
    $qry = $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()->table('oldUsers1')
            ->unionAll()
                ->select('user_id')->table('oldUsers2')
            ->getSql()

    // (SELECT * FROM `Users`) UNION (SELECT * FROM `oldUesrs`) ORDER BY `user_id` ASC LIMIT 10
    $sql = $builder->union(
        $builder->select()->table('Users'),
        $builder->select()->table('oldUsers')
    )->order('user_id')->limit(10)->getSql();


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

  // same as above
  $sql = $users->insert()->set('uid', 2)->set('uname', 'phossa')->getSql();

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


  // INSERT INTO `Users` (`uid`, `uname`) VALUES (2, 'phossa'), (3, 'test')
  $qry = $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')
  $qry = $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 = $qry->getSql(['useNullAsDefault' => true]);


  // INSERT INTO `Users` (`uid`, `uname`) SELECT `user_id`, `user_name` FROM `oldUsers`
  $qry = $users->insert()->set(['uid', 'uname'])
      ->select('user_id', 'user_name')->table('oldUsers');


  // UPDATE `Users` SET age = age + 1
  $qry = $users->update()->set('age = age + 1');

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

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


  // UPDATE `Users` SET `age` = `age` + 2 WHERE `user_id` = 2
  $qry = $users->update()->increment('age', 2)->where('user_id', 2);


  // UPDATE IGNORE `Users` SET `user_id` = `user_id` + 10, `user_status` = user_status | 2 ORDER BY `user_id` ASC LIMIT 10
  $qry = $users->update()->hint('IGNORE')
      ->setTpl('user_id', '%s + ?', 'user_id', [10])
      ->setRaw('user_status', 'user_status | 2')
      ->order('user_id')->limit(10);


  // REPLACE INTO `Users` (`user_id`, `user_name`) VALUES (3, 'phossa')
  $qry = $users->replace(['user_id' => 3, 'user_name' => 'phossa']);


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


  // DELETE `u`, `a` FROM `Users` AS `u` INNER JOIN `Accounts` AS `a`
  // ON `u`.`user_id` = `a`.`user_id` WHERE `a`.`total_amount` < 10
  $qry = $builder->delete('u', 'a')->table('Users', 'u')
      ->join(['Accounts', 'a'], 'user_id')->where('a.total_amount', '<', 10);


  // SELECT
  //     *
  // FROM
  //     "Users"
  // WHERE
  //    ("age" < 18 OR "gender" = 'female')
  //    OR ("age" > 60 OR ("age" > 55 AND "gender" = 'female'))
  $qry = $builder->select()->table('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')
      )
  );


  // 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')
  )->getSql();


  // SELECT score + 10 FROM `Students` WHERE `time` < NOW()
  $qry = $builder->select()->colRaw('score + 10')
      ->from("Students")->where('time', '<', $builder->raw('NOW()'));

  // SELECT `grp_id`, COUNT(*) AS `cnt` FROM `Users` GROUP BY grp_id ASC
  $qry = $users->select()->col('grp_id')->cnt('*', 'cnt')->groupRaw('grp_id ASC');


  // SELECT * FROM `Students` WHERE `age` IN RANGE(1, 1.2)
  $qry = $builder->select()->from("Students")->where("age", "IN",
      $builder->raw('RANGE(?, ?)', [1, 1.2]));

  // same as above
  $qry = $builder->select()->from("Students")
    ->whereRaw("`age` IN RANGE(?, ?)", [1, 1.2]);


  // SELECT MAX(`score`) AS max FROM `Users`
  $sql = $users->select()->colTpl('MAX(%s)', 'score', 'max')->getSql();


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


  // INSERT IGNORE INTO "users" ("id", "name") VALUES (3, 'phossa') ON DUPLICATE KEY UPDATE id=id+10
  $qry = $users->insert()->hint('IGNORE')
      ->set('id', 3)->set('name', 'phossa')
      ->option('ON DUPLICATE KEY UPDATE id=id+?', [10]);


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

  // SELECT * FROM `Users` WHERE `user_id` = ?
  $sql = $qry->getStatement();

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


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

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


  // SELECT * FROM `Users` GROUP BY `year` + 10
  $sql = $users->select()->groupRaw('`year` + ?', [10])->getSql();

  // same as above
  $sql = $users->select()->groupTpl('%s + ?', 'year', [10])->getSql();


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

  // adjust settings
  $users->setSettings(['autoQuote' => true]);


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


  // SELECT
  //     *
  // FROM
  //     `Users`
  $sql = $users->select()->getSql(['seperator' => "\n", 'indent' => "    "]);