README

PHP SQL query builder using PDO. It's a quick and light library featuring a smart join builder, which automatically creates table joins for you.

Features

• Easy interface for creating robust queries
• Supports any database compatible with PDO
• Ability to build complex SELECT, INSERT, UPDATE & DELETE queries with little code
• Type hinting for magic functions with code completion in smart IDEs

Install

composer require hasan-22/query-builder

Driver:

First make the config file exactly like this Config file is not necessary config.php

<?php

return [

    'default'=>'mysql',

    'driver'=>[
        'mysql'=>[
            'host'=>'localhost',
            'username'=>'root',
            'password'=>'',
            'dbname'=>'php',
            'charset'=>'UTF8',
        ]
    ]
];

Then create a custom driver class for the connection like this

Mysql.php

<?php

class Mysql implements \App\Database\ConnectionInterface
{
    private static $instance;

    private function __construct(){}

    /**
     * This method prevents the creation of duplicate object
     * @return mixed
     */
    public static function setInstance(){
        if(!static::$instance instanceof Mysql){
            static::$instance = new Mysql();
        }
        return static::$instance;
    }

    /**
     * Get database connection information in configuration
     * @return array
     */
    public function driver(): array{
        /**
        * If you have set the database in the config file, use this code.
        */
        // Path of your config file
        $config = require __DIR__.'/Config/Database.php';
        $config = $config['driver'][$config['default']];
        return [
            'dns'=>"mysql:host=".$config['host'].";dbname=".$config['dbname'].";charset:".$config['charset'],  
            'password'=>$config['password'], 
            'username'=>$config['username']
        ];

        /**
        * Otherwise, you can send the database information directly like this
        *
        * return ['dns'=>'Your dns','username'=>'','password'=>''];
        *
        */ 
    }

    /**
     * Connect to database with PDO
     * @return \PDO|void
     */
    public function connect(){
        // The attribute parameter is optional
        $attributes = [
            \PDO::ATTR_CASE=>\PDO::CASE_NATURAL
        ];
        return \App\Database\PDO::connect($this->driver(),$attributes);
    }
}

Then create new instance of DB

$db = new \App\Builder\DB("\App\Database\Mysql");

Set table and fetch all records

$db->table('users')->get()

In the get() function, you can specify which columns to get

$db->table('users')->get('name','password','email')

Or you can use all() function to get all records

$db->table('users')->all()

Execute queries for a single or many record by ID

$db->table('users')->find()
// or
$db->table('users')->find(1,2,3)

If you want add condition follow my lead

Some Where functions

$db->table('users')->where('name','=','armia')->get();

// for `or where`
$db->table('users')->orWhere('name','=','armia')->get();

// for `where in`
$db->table('users')->whereIn('id',[1,2,3])->get();

// for `or wher in`
$db->table('users')->orWhereIn('id',[1,2,3])->get();

// for `where between`
$db->table('users')->whereBetween('crated_at',['2022-12-12','2023-01-29'])->get();

// for `or where between`
$db->table('users')->orWhereBetween('crated_at',['2022-12-12','2023-01-29'])->get();

// for `exists`
$db->table('brands')->exists('users','users.id = brands.user_id')->get();

// And you can run all the functions one after the other like the example below
$db->table('users')
->where('name','=','armia')
->orWhere('name','=','armia')
->whereIn('id',[1,2,3])
->orWhereIn('id',[1,2,3])
->whereBetween('crated_at',['2022-12-12','2023-01-29'])
->orWhereBetween('crated_at',['2022-12-12','2023-01-29'])
->get()

Some Having functions

$db->table('users')->havin('name','=','armia')->get();

// for `or where`
$db->table('users')->orHaving('name','=','armia')->get();

// for `where in`
$db->table('users')->havingIn('id',[1,2,3])->get();

// for `or wher in`
$db->table('users')->orHavingIn('id',[1,2,3])->get();

// for `where between`
$db->table('users')->havingBetween('crated_at',['2022-12-12','2023-01-29'])->get();

// for `or where between`
$db->table('users')->orHavingBetween('crated_at',['2022-12-12','2023-01-29'])->get();


// And you can run all the functions one after the other like the example below
$db->table('users')
->having('name','=','armia')
->orHaving('name','=','armia')
->havingIn('id',[1,2,3])
->orHavingIn('id',[1,2,3])
->havingBetween('crated_at',['2022-12-12','2023-01-29'])
->orHavingeBetween('crated_at',['2022-12-12','2023-01-29'])
->get()

Join functions

$db->table('users')->innerJoin('brands','brands.user_id','users.id')->get();

$db->table('users')->leftJoin('brands','brands.user_id','users.id')->get();

$db->table('users')->rightJoin('brands','brands.user_id','users.id')->get();

$db->table('users')->crossJoin('brands')->get();

You can use where or having condition with join functions

$db->table('users')
->innerJoin('brands','brands.user_id','users.id')
->where('users.id','=',14)->get();

CRUD functions

For insert data use create function

Insert single data

$data = ['name'=>'armia','email'=>'armiaevil@gmail.com','password'=>'123456'];
$db->table('users')->create($data);

Insert multiple data

$data = [
    ['name'=>'armia','email'=>'armiaevil@gmail.com','password'=>'123456'],
    ['name'=>'armia','email'=>'armiaevil@gmail.com','password'=>'123456'],
    ['name'=>'armia','email'=>'armiaevil@gmail.com','password'=>'123456'],
];
$db->table('users')->create($data);

Update record

$db->table('users')->where('id','=',1)->update(['name'=>'update name','email'=>'email@gmail.com']);
// or
$db->table('users')->update(['name'=>'update name','email'=>'email@gmail.com'],['id'=>1]);
// or
$db->table('users')->update(['name'=>'update name','email'=>'email@gmail.com'],['id'=>1,'status'=>'activated']);

Delete record

$db->table('users')->where('id',1)->delete();
// or
$db->table('users')->delete(['id',1]);
// or
$db->table('users')->delete(['id',1,'status'=>'activated']);

Transaction

$db->beginTransaction();
try{
    // TODO: code
    
    $db->commit();
}catch (Exception $e){
    $db->rollback();
    // TODO: code
}

Debug

Only works on functions whose return value is Object of class DB.

$db->table('users')
->innerJoin('brands','brands.user_id','users.id')
->where('users.id','=',14)->debug();

/**
Output:
SQL: [86] SELECT * FROM users INNER JOIN brands ON brands.user_id = users.id WHERE users.id = ?
Sent SQL: [89] SELECT * FROM users INNER JOIN brands ON brands.user_id = users.id WHERE users.id = '14'
Params:  1
Key: Position #0:
paramno=0
name=[0] ""
is_param=1
param_type=2
NULL
*/

Functions