README

查询构建器包，用于在核心 PHP 中轻松实现数据库查询。

需求

• PHP 7.0 或更高版本
• Composer 用于安装

安装

composer require hraw/dbqb

实现

示例：文件名 -> index.php

<?php

require_once __DIR__.'/vendor/autoload.php';

use Hraw\DBQB\DB;

//Connect to database
DB::connect([
    'name'      =>  'default',
    'driver'    =>  'mysql',
    'host'      =>  'localhost',
    'database'  =>  'test',
    'username'  =>  'username',
    'password'  =>  'password',
]);

//Fetch all the data from table
DB::all('tableName');

//Fetch data with conditions
DB::table('users')->where('id', '>', 1)->get();

//Fetch data with array of conditions
DB::table('users')->where([
                        'salary'    =>  10000,
                        'profile'   =>  'developer'   
                   ])
                   ->get();

//Fetch only single record
DB::table('users')->where('id', '>', 1)->first();

//Print sql query
DB::table('users')->where('id', '>', 1)->toSql();

//Deleting a record
DB::table('users')->where('id', 10)->delete();

//Updating a record
DB::table('users')->where('role_id', 1)
    ->update([
        'designation'   =>  'Admin'
    ]);
    
//Inserting a record
DB::table('users')
    ->insert([
        'fname' =>  'John',
        'lname' =>  'Doe'
    ]);
    
//Inserting bulk records
DB::table('users')
    ->batchInsert([
        [
            'fname' =>  'John',
            'lname' =>  'Doe'
        ],
        [
            'fname' =>  'peter',
            'lname' =>  'parker'
        ]
    ]);
    
Note:- batchInsert function does not return id of the inserted rows however insert function returns the id of the inserted row.
    
//Nested conditions
DB::table('users')->where('name', 'like', '%jo%')
                  ->where(function($query){
                    $query->where('age', '>', 18)
                          ->orWhere('state_code', 'UK');
                  })
                  ->first();
                  
//Group By, Order By and Limit
DB::table('users')->where('name', 'black tshirt')
                  ->groupBy('size', 'sku')
                  ->orderBy('id', 'desc')
                  ->limit(20)
                  ->get();
                  
//Fetch data using union and unionAll
DB::table('users')
     ->select('id')
     ->where('fname', 'john')
     ->union(
         DB::table('posts')
             ->select('id')
             ->where('id', 1)
     )
     ->get();
     
DB::table('users')
     ->select('id')
     ->where('fname', 'john')
     ->unionAll(
         DB::table('posts')
             ->select('id')
             ->where('id', 1)
     )
     ->get();
     
//Fetch data using joins
DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'orders.*')
    ->get();
    
Note:- join method uses inner join for joining the tables.
        Other supported methods:
        1) leftJoin, 2) rightJoin, 3) outerJoin
        
DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();
 
DB::table('users')
    ->rightJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();

事务

DB::beginTransaction();
try {
    DB::table('users')->where('id', '<', 10)->delete();
    DB::commit();
} catch (\Exception $e) {
    DB::rollBack();
}

多个数据库连接

DB::connection('connection1Name')->all('tableName');

DB::connection('connection2Name')->all('tableName');

支持的方法

• where('columnName', 'operator', 'value') 或 where([$keyValues]) //operator 可选
• orWhere('columnName', 'operator', 'value') //operator 可选
• whereIn('columnName', 'values') //values 必须是一个数组
• orWhereIn('columnName', 'values') //values 必须是一个数组
• whereNotIn('columnName', 'values') //values 必须是一个数组
• orWhereNotIn('columnName', 'values') //values 必须是一个数组
• whereBetween('columnName', [val1, val2])
• whereNotBetween('columnName', [val1, val2])
• orWhereBetween('columnName', [val1, val2])
• orWhereNotBetween('columnName', [val1, val2])
• whereNull('columnName')
• orWhereNull('columnName')
• whereNotNull('columnName')
• orWhereNotNull('columnName')
• orderBy('columnName', type) // type 可以是 ASC 或 DESC
• groupBy(column1, column2)
• limit(value)
• offset(value)
• raw(query, placeholders) //您可以通过此查询构建器运行原始查询，无论是否使用预处理语句，对于预处理语句，您只需传递 placeholders 参数（可选参数）的值数组。
• count('columnName') //可选
• min('columnName')
• max('columnName')
• avg('columnName')
• sum('columnName')
• insert([values]) //在表中插入单行，values 必须是一个关联数组
• batchInsert([values]) //在表中插入多条记录，values 必须是一个数组数组
• delete() //从数据库中删除记录
• update([values]) //values 必须是一个关联数组
• having (注意：所有 having 方法与 where 方法相同)

运行服务器

php -S localhost:8000