PHP code example of nilportugues / sql-query-builder

1. Go to this page and download the library: Download nilportugues/sql-query-builder 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/ */

nilportugues / sql-query-builder example snippets



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()->setTable('user');    

echo $builder->write($query);    



use NilPortugues\Sql\QueryBuilder\Builder\MySqlBuilder;

$builder = new MySqlBuilder(); 

$query = $builder->select()->setTable('user'); 

echo $builder->write($query);    



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()->setTable('user');  

echo $builder->writeFormatted($query);    




use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(['user_id','name','email']);
     
echo $builder->write($query);    



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(['userId' => 'user_id', 'username' => 'name', 'email' => 'email']);
       
echo $builder->write($query);    



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId' => 'user_id',
        'username' => 'name',
        'email' => 'email'
    ])
    ->where()
    ->greaterThan('user_id', 5)
    ->notLike('username', 'John')
	->end();
      
echo $builder->writeFormatted($query);    



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->where()
    ->equals('user_id', 1)
    ->equals('user_id', 2)
    ->subWhere("OR")
    ->lessThan($column, 10)
    ->greaterThan('user_id', 100)
	->end();

echo $builder->writeFormatted($query);



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
            'userId'   => 'user_id',
            'username' => 'name',
            'email'    => 'email',
            'created_at'
    ])
    ->orderBy('user_id', OrderBy::DESC)
    ->leftJoin(
        'news', //join table
        'user_id', //origin table field used to join
        'author_id', //join column
         ['newsTitle' => 'title', 'body', 'created_at', 'updated_at']
     )
    ->on()
    ->equals('author_id', 1); //enforcing a condition on the join column

$query
    ->where()
    ->greaterThan('user_id', 5)
    ->notLike('username', 'John')
	->end();

$query
    ->orderBy('created_at', OrderBy::DESC);

echo $builder->writeFormatted($query); 



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count()

echo $builder->write($query);



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count('user_id')

echo $builder->write($query);



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
        ->setTable('user')
        ->count('user_id', 'total_users')

echo $builder->write($query);



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->insert()
    ->setTable('user')
    ->setValues([
        'user_id' => 1,
        'name'    => 'Nil',
        'contact' => '[email protected]',
    ]);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();


[':v1' => 1, ':v2' => 'Nil', ':v3' => '[email protected]'];



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->update()
    ->setTable('user')
    ->setValues([
        'user_id' => 1,
        'name' => 'Nil',
        'contact' => '[email protected]'
    ])
    ->where()
    ->equals('user_id', 1)
	->end();

$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();


[':v1' => 1, ':v2' => 'Nil', ':v3' => '[email protected]', ':v4' => 1];



use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->update()
    ->setTable('user')
    ->setValues([
        'name' => 'UpdatedName',
    ]);
    
$query
    ->where()
    ->like('username', '%N')
    ->between('user_id', 1, 2000)
	->end();
        
$query
    ->orderBy('user_id', OrderBy::ASC)
    ->limit(1);            
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');
   
$sql = $builder->write($query);   



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');

$query
    ->where()
    ->equals('user_id', 100)
	->end();

$query
    ->limit(1);
   
$sql = $builder->write($query);    
$values = $builder->getValues();


[':v1' => 100, ':v2' => 1];



use NilPortugues\Sql\QueryBuilder\Syntax\OrderBy;
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->delete()
    ->setTable('user');
    
$query
    ->where()
    ->like('username', '%N')
    ->between('user_id', 1, 2000)
	->end();
        
$query
    ->orderBy('user_id', OrderBy::ASC)
    ->limit(1);            
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->intersect()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->minus($select1, $select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->union()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$select1 = $builder->select()->setTable('user');
$select2 = $builder->select()->setTable('user_emails');
   
$query = $builder->unionAll()
    ->add($select1)
    ->add($select2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();


public function subWhere($operator = 'OR');
public function equals($column, $value);        
public function notEquals($column, $value);
public function greaterThan($column, $value);
public function greaterThanOrEqual($column, $value);
public function lessThan($column, $value);
public function lessThanOrEqual($column, $value);
public function like($column, $value);
public function notLike($column, $value);
public function match(array $columns, array $values);
public function matchBoolean(array $columns, array $values);
public function matchWithQueryExpansion(array $columns, array $values);
public function in($column, array $values);
public function notIn($column, array $values);
public function between($column, $a, $b);
public function notBetween($column, $a, $b);
public function isNull($column);
public function isNotNull($column);
public function exists(Select $select);
public function notExists(Select $select);
public function addBitClause($column, $value);    
public function asLiteral($literal);



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->where('OR')
    ->equals('user_id', 1)
    ->like('name', '%N%')
	->end();       
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId'   => 'user_id',
        'username' => 'name',
        'email'    => 'email',
        'created_at'
    ])
    ->groupBy(['user_id', 'name'])
    ->having()
    ->equals('user_id', 1)
    ->equals('user_id', 2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns([
        'userId'   => 'user_id',
        'username' => 'name',
        'email'    => 'email',
        'created_at'
    ])
    ->groupBy(['user_id', 'name'])
    ->having('OR')
    ->equals('user_id', 1)
    ->equals('user_id', 2);
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$selectRole = $builder->select()
    ->setTable('role')
    ->setColumns(array('role_name'))
    ->limit(1)
    ->where()
    ->equals('role_id', 3);

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setSelectAsColumn(array('user_role' => $selectRole))
    ->setSelectAsColumn(array($selectRole))
    ->where()
    ->equals('user_id', 4)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setValueAsColumn('10', 'priority')
    ->where()
    ->equals('user_id', 1)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setFunctionAsColumn('MAX', array('user_id'), 'max_id')
    ->where()
    ->equals('user_id', 1)
	->end(); 
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setColumns(array('user_id', 'username'))
    ->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time')
    ->where()
    ->equals('user_id', 1)
	->end();
   
$sql = $builder->writeFormatted($query);    
$values = $builder->getValues();



use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;

$builder = new GenericBuilder(); 

$query = $builder->select()
    ->setTable('user')
    ->setComment('This is a comment');
    
$sql = $builder->write($query);   

sql
SELECT COUNT(user.user_id) FROM user;

sql
SELECT COUNT(user.user_id) AS 'total_users' FROM user;

sql
SELECT user.* FROM user
UNION ALL
SELECT user_email.* FROM user_email

bash
    php composer.phar install --dev
    php bin/phpunit