php-query-builder

Powerful and lightweight PHP SQL Query Builder with fluid interface SQL syntax using bindings and complicated query generation

Features

• multiple databases (multiple PDO instances)
• INSERT, INSERT IGNORE, INSERT DELAYED, UPDATE, SELECT, DELETE queries
• support LEFT JOIN, INNER JOIN, RIGHT JOIN, GROUP BY, LIMIT, HAVING and etc.
• =, !=, >, <, >=, <=, IN, NOT IN, IS NULL, NOT NULL operators
• support transactions and sub-queries
• support result caching (\Psr\SimpleCache\CacheInterface)
• multiple fetching data modes (fetch arrays, fetch objects, etc.)
• auto escape column names
• auto format queries
• auto bind variables
• hydration to objects (auto cast types from annotations and property types). See demo/hydration.php

Quick install

---

The recommended way to install the Query Builder is through Composer. Run the following command to install it:

composer require hemiframe/php-query-builder

Set default PDO instance:

<?php

//Create PDO instance
$pdo = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test', [
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
]);
$pdo->exec("set names utf8");

// Set as default for all query instances
\HemiFrame\Lib\SQLBuilder\Query::$global['pdo'] = $pdo;

$query = new \HemiFrame\Lib\SQLBuilder\Query(); //Your query
$query->execute();

Documentation

---

Select query

<?php

$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->leftJoin("details", "d", "d.userId=u.id");
$query->andWhere("u.status", 0);
$query->andWhere("u.id", [1, 2, 3]);
$query->andWhere("u.age", null);
$query->andWhere("u.gender", null, '!=');
$query->orderBy("u.id DESC");
$query->groupBy("u.id");
$query->paginationLimit(1, 10);

Output:

SELECT
  u.id
  ,u.email
  ,u.name
FROM
  users AS u
LEFT JOIN details AS d
  ON d.userId=u.id
WHERE
  u.status=0
  AND u.id IN (1,2,3)
  AND u.age IS NULL
  AND u.gender IS NOT NULL
GROUP BY u.id
ORDER BY u.id DESC
LIMIT 0, 10

Select query from sub query

<?php

$queryInner = new \HemiFrame\Lib\SQLBuilder\Query();
$queryInner->select()->from("user");
$queryInner->andWhere("isActive", 1);

$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.name",
])->from($queryInner, "u");
$query->andWhere("status", 2, '!=');
$query->limit("1000");

Output:

SELECT
  u.id
  ,u.name
FROM
  (SELECT
  *
FROM
  user
WHERE
  isActive=1) AS u
WHERE
  `status`!=2
LIMIT 1000

Insert query

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->insertInto("users")->set([
    "name" => 'Test',
    "email" => '[email protected]',
]);
$query->onDuplicateKeyUpdate("`email`=:testVar")->setVar('testVar', '[email protected]');

Output:

INSERT INTO
  users
SET
  `name`="Test"
  ,`email`="[email protected]"
ON DUPLICATE KEY UPDATE
  `email`="[email protected]"

Insert query with VALUES

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->insertInto("users")->values([
    'name',
    'email',
    'age',
], [
    [
        'name 1',
        'email 1',
        '15',
    ],
    [
        'name 2',
        'email 2',
        '20',
    ],
]);
$query->onDuplicateKeyUpdate("email=:testVar")->setVar('testVar', '[email protected]');

Output:

INSERT INTO
  users
  (`name`,`email`,`age`)
VALUES
  ("name 1","email 1","15")
  ,("name 2","email 2","20")
ON DUPLICATE KEY UPDATE
  `email`="[email protected]"

Update query

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->update("users")->set([
    "name" => 'Test',
    "email" => '[email protected]',
]);
$query->set('totalViews = totalViews + 1');
$query->andWhere("status", 2, '!=');
$query->andWhere("id", [1, 2, 3]);
$query->andWhere("id", [10, 20, 30], '!=');

Output:

UPDATE
  users
SET
  `name`="Test"
  ,`email`="[email protected]"
  ,totalViews = totalViews + 1
WHERE
  `status`!=2
  AND `id` IN (1,2,3)
  AND `id` NOT IN (10,20,30)

Delete query

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->delete()->from("users");
$query->andWhere("status", 2, '!=');
$query->andWhere("id", [1, 2, 3]);
$query->andWhere("id", [10, 20, 30], '!=');
$query->limit("1000");

Output:

DELETE FROM
  users
WHERE
  `status`!=2
  AND `id` IN (1,2,3)
  AND `id` NOT IN (10,20,30)
LIMIT 1000

Delete query with joins

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->delete("u")->from("users", "u");
$query->leftJoin("emails", "e", "e.userId=u.id");
$query->andWhere("e.status", 2, '!=');
$query->andWhere("u.id", [1, 2, 3]);
$query->andWhere("u.id", [10, 20, 30], '!=');
$query->andWhere("e.status", 1);
$query->limit("1000");

Output:

DELETE
  u
FROM
  users AS u
LEFT JOIN emails AS e
  ON e.userId=u.id
WHERE
  e.status!=2
  AND u.id IN (1,2,3)
  AND u.id NOT IN (10,20,30)
  AND e.status=1
LIMIT 1000

Fetching data

Fetch data as array of arrays

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$rows = $query->fetchArrays();

Fetch data as array of objects

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$rows = $query->fetchObjects();

Fetch first result as array

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$row = $query->fetchFirstArray();

Fetch first result as object

<?php
$query = new \HemiFrame\Lib\SQLBuilder\Query();
$query->select([
    "u.id",
    "u.email",
    "u.name",
])->from("users", "u");
$query->orderBy("u.id DESC");
$row = $query->fetchFirstObject();

Using multiple databases

<?php
$pdo1 = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test');
$pdo2 = new \PDO('mysql:host=localhoset;dbname=test', 'test', 'test');

$query = new \HemiFrame\Lib\SQLBuilder\Query([
    'pdo' => $pdo1,
]);
$query->select([
    "u.id",
])->from("users", "u");

$queryArticles = new \HemiFrame\Lib\SQLBuilder\Query([
    'pdo' => $pdo2,
]);
$queryArticles->select([
    "a.id",
])->from("articles", "a");