jinnguyen/puja-sqlbuilder

Puja-SqlBuilder allows users to build quickly and easily complex

v1.2.0 2017-11-12 14:22 UTC

Requires (Dev)

• codeception/aspect-mock: 0.5.3
• phpunit/phpunit: ^4.8

Apache License 2.0

• Jin Nguyen

• query builder
• querybuilder
• SQL Builder
• sqlbuilder
• puja

README

SqlBuilder allows users to build quickly and easily complex.

Install:

• With composer:

composer require jinnguyen/puja-sqlbuilder

Usage:

    require_once 'path/to/vendor/autoload.php';
    use Puja\SqlBuilder\Builder;
    $builder = new Builder('php_');

Examples
SELECT

$select = $builder->select()
    ->from(array('c' => 'content'), array('id' => 'content_id'))
    ->from(array('ln' => 'content_ln'), array('title' => 'name', 'iso2_code'))
    ->joinLeft('category', 'category.category_id=content.category_id', array('name', 'category_id'))
    ->order('content.content_id') ->order('category.category_id', Builder::ORDER_DESC) ->limit(10)
    ->having('content.content_id=%d', 10) ->groupBy('content.content_id')
    ->where('c.content_id=%d AND ln.name LIKE "%%%s%%"', 4, 'search term')
    ->orWhere('category.name IS EMPTY AND category.category_id >= %d', 5);

echo 'Query:' . $select->getQuery();
echo 'Count:' . $select->getCount();

Result:

Query:SELECT c.content_id AS id,ln.name AS title,ln.iso2_code,category.name,category.category_id FROM content AS c,content_ln AS ln LEFT JOIN category AS category ON category.category_id=content.category_id WHERE (c.content_id=4 AND ln.name LIKE "%search term%") OR (category.name IS EMPTY AND category.category_id >= 5) GROUP BY content.content_id HAVING (content.content_id=10) ORDER BY content.content_id ,category.category_id DESC LIMIT 0,10
Count:SELECT COUNT(*) AS total FROM content AS c,content_ln AS ln LEFT JOIN category AS category ON category.category_id=content.category_id WHERE (c.content_id=4 AND ln.name LIKE "%search term%") OR (category.name IS EMPTY AND category.category_id >= 5) GROUP BY content.content_id HAVING (content.content_id=10)

INSERT

$select = $builder->reset()->insert('content', array('name' => 'Jin', 'addtime__exact' => 'NOW()'));
echo $select->getQuery();

Result:

INSERT INTO content(`name`,`addtime`) VALUES ("Jin", NOW())

UPDATE

$select = $builder->reset()->update('content', array('name' => 'Jin', 'addtime__exact' => 'NOW()'))
    ->where('content_id=%d', 5);
echo $select->getQuery();

Result:

UPDATE content SET `name`="Jin",`addtime`=NOW() WHERE (content_id=5)

REPLACE

$select = $builder->reset()->replace('content', array('name' => 'Jin', 'addtime__exact' => 'NOW()'))
    ->where('content_id=%d', 5);
echo $select->getQuery();

Result:

REPLACE INTO content(`name`,`addtime`) VALUES ("Jin", NOW())

DELETE

$select = $builder->reset()->delete('content')->where('content_id=%d', 5);
echo $select->getQuery();

Result:

DELETE FROM content WHERE (content_id=5)

TRUNCATE

$select = $builder->reset()->truncate('content');
echo $select->getQuery();

Result:

TRUNCATE TABLE content