PHP code example of wazsmwazsm / db

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

wazsmwazsm / db example snippets

use DB/DB;

// 配置信息
$db_conf = [
	// mysql 配置样例
    'con1' => [ 
        'driver'      => 'mysql',
        'host'        => 'localhost',
        'port'        => '3306',
        'user'        => 'username',
        'password'    => 'password',
        'dbname'      => 'database',
        'charset'     => 'utf8',
        'prefix'      => '',
        'timezone'    => '+8:00',
        'collection'  => 'utf8_general_ci',
        // 'strict'      => false,
        // 'unix_socket' => '/var/run/mysqld/mysqld.sock',
        // 'options'     => [],
    ],
    
	// postgresql 配置样例
    'con2' => [
        'driver'           => 'pgsql',
        'host'             => 'localhost',
        'port'             => '5432',
        'user'             => 'username',
        'password'         => 'password',
        'dbname'           => 'database',
        'charset'          => 'utf8',
        'timezone'         => '+8:00',
        'prefix'           => '',
        // 'schema'           => '',
        // 'application_name' => '',
        // 'sslmode'          => 'disable',
        // 'options'          => [],
    ],
	// sqlite 配置样例
    'con3' => [ 
        'driver'  => 'sqlite',
        'dbname'  => 'database.db',
        'prefix'  => '',
        // 'options' => [],
    ],
    // 其他连接
    'con3' => [ 
        // ...
    ]
];

// 初始化连接
DB::init($db_conf);

use DB\DB;

$con = DB::connection('con1');

$result = $con->table('test_table')->get();

use DB\Model;

class MyModel extends Model
{
    // 指定数据库连接
    protected $connection;
	// 指定数据表
    protected $table;
    // 自定义方法
    public function getData()
    {
    	// 使用查询构造器的方法
        return $this->get();
    }
}

$m = new MyModel;

$result = $m->getData();

// SELECT * FROM test;
$data = DB::connection('con1')->table('test')->get();

// SELECT id, name, age, score FROM test WHERE id = 10;
$row = DB::connection('con1')
    ->table('test')
    ->select('id', 'name', 'age', 'score')
    ->where('id', 10)
    ->row();

// 获取 name 字段的结果集
// SELECT name FROM test;
$data = DB::connection('con1')->table('test')->getList('name');

// 获取查询结果的条数
// SELECT COUNT(*) FROM test;
$data = DB::connection('con1')->table('test')->count(*);
// 获取 score 的总和
// SELECT SUM(score) FROM test;
$data = DB::connection('con1')->table('test')->sum('score');

// SELECT * FROM test WHERE name = 'mike' AND age = 18;
$data = DB::connection('con1')
    ->table('test')
    ->where([
        'name' => 'mike', 
        'age' => 18
    ])
    ->get();

// SELECT * FROM test WHERE name = 'jack' AND age >= 18;
$data = DB::connection('con1')
    ->table('test')
    ->where('name', 'jack')
    ->where('age', '>=',  18)
    ->get();
// SELECT * FROM test WHERE name = 'jack' OR name = 'mike';
$data = DB::connection('con1')
    ->table('test')
    ->where('name', 'jack')
    ->orWhere('name', 'mike')
    ->get();

// SELECT * FROM test WHERE name IS NOT null;
$data = DB::connection('con1')
    ->table('test')
    ->whereNotNull('name')
    ->get();

// SELECT * FROM test WHERE age BETWEEN 18 AND 30;
$data = DB::connection('con1')
    ->table('test')
    ->whereBetween('age', 18, 30)
    ->get();

// SELECT * FROM test WHERE age IN (18, 19, 20);
$data = DB::connection('con1')
    ->table('test')
    ->whereIn('age', [18, 19, 20])
    ->get();

// SELECT * FROM user WHERE EXISTS ( SELECT * FROM user_group WHERE id = 3 ) AND g_id = 3;
$data = DB::connection('con1')
    ->table('user')
    ->whereExists(function($query) {
        $query->table('user_group')->where('id', 3);
    })
    ->where('g_id', 3)
    ->get();

// SELECT * FROM user WHERE g_id IN (SELECT id FROM user _group);
$data = DB::connection('con1')
    ->table('user')
    ->whereInSub('g_id', function($query) {
        $query->table('user_group')->select('id');
    })
    ->get();

// SELECT * FROM user WHERE (id < 50 OR username IS NOT NULL) AND sort_num = 20;
$data = DB::connection('con1')
    ->table('user')
    ->whereBrackets(function($query) {
        $query->where('id', '<', 50)
                ->orWhereNotNull('username');
    })
    ->where('sort_num', 20)
    ->get();
        
// SELECT * FROM user WHERE sort_num = 20 OR (id < 10 AND id > 5);
$data = DB::connection('con1')
    ->table('user')
    ->where('sort_num', 20)
    ->orWhereBrackets(function($query) {
        $query->where('id', '<', 10)
                ->where('id', '>', 5);
    })
    ->get();

// SELECT id, username, email FROM ( SELECT * FROM user WHERE id < 20 ) AS tb;
$data = DB::connection('con1')
    ->select('id', 'username', 'email')
    ->fromSub(function($query) {
        $query->table('user')->where('id', '<', '20');
    })
    ->get();

// SELECT sort_num, COUNT(sort_num) FROM user GROUP BY sort_num;
$data = DB::connection('con1')
    ->table('user')
    ->select('sort_num', 'COUNT(sort_num)')
    ->groupBy('sort_num')
    ->get();
        
// SELECT sort_num, COUNT(sort_num) FROM user GROUP BY sort_num HAVING COUNT(sort_num) < 20;
$data = DB::connection('con1')
    ->table('user')
    ->select('sort_num', 'COUNT(sort_num)')
    ->groupBy('sort_num')
    ->having('COUNT(sort_num)', '<', 20)
    ->get();

// SELECT sort_num, COUNT(sort_num) FROM user GROUP BY sort_num HAVING COUNT(sort_num) < 20;
$data = DB::connection('con1')
    ->table('user')
    ->select('sort_num', 'COUNT(sort_num)')
    ->groupBy('sort_num')
    ->havingRaw('COUNT(sort_num) < 20')
    ->get();

// SELECT * FROM user ORDER BY sort_num DESC, id ASC;
$data = DB::connection('con1')
    ->table('user')
    ->orderBy('sort_num', 'DESC')
    ->orderBy('id', 'ASC')
    ->get();

// SELECT * FROM user LIMIT 10 OFFSET 3
$data = DB::connection('con1')
    ->table('user')
    ->limit(3, 10)
    ->get();

$data = DB::connection('con1')
    ->table('user')
    ->paginate(10, 2);  // 每页 10 条数据，当前第 2 页

[
	'total'        => 35;
    'per_page'     => 10;
    'current_page' => 2;
    'next_page'    => 3;
    'prev_page'    => 1;
    'first_page'   => 1;
    'last_page'    => 4;
    'data'         => $yourdata;
]

// SELECT * FROM user INNER JOIN user_group ON user.g_id = user_group.id;
$data = DB::connection('con1')
    ->table('user')
    ->join('user_group', 'user.g_id', 'user_group.id')
    ->get();
        
        
// SELECT user.username, user_group.groupname FROM user LEFT JOIN user_group ON user.g_id = user_group.id;
$data = DB::connection('con1')
    ->table('user')
    ->select('user.username', 'user_group.groupname')
    ->leftJoin('user_group', 'user.g_id', 'user_group.id')
    ->get();

// SELECT user.username, user_group.groupname FROM user 
// LEFT JOIN user_group ON user.g_id = user_group.id 
// WHERE username = 'Jackie aa' 
// OR ( NOT EXISTS ( SELECT * FROM user WHERE username = 'Jackie aa' ) AND username = 'Jackie Conroy' );
$data = DB::connection('con1')
    ->table('user')
    ->select('user.username', 'user_group.groupname')
    ->leftJoin('user_group', 'user.g_id', 'user_group.id')
    ->where('user.username', 'Jackie aa')
    ->orWhereBrackets(function($query) {
        $query->whereNotExists(function($query) {
            $query->table('user')->where('username', 'Jackie aa');
        })->where('user.username', 'Jackie Conroy');
    })
    ->get();

// SELECT user.sort_num, COUNT(*) FROM user 
// INNER JOIN user_group ON user.g_id = user_group.id 
// WHERE user.activated <> 0 
// GROUP BY user.sort_num 
// HAVING user.sort_num = 20 OR user.sort_num = 50 ORDER BY user.sort_num DESC;
$data = DB::connection('con1')
    ->table('user')
    ->select('user.sort_num', 'COUNT(*)')
    ->join('user_group', 'user.g_id', 'user_group.id')
    ->where('user.activated', '<>', 0)
    ->groupBy('user.sort_num')
    ->having('user.sort_num', '50')
    ->orHaving('user.sort_num', '20')
    ->orderBy('user.sort_num', 'DESC')
    ->get();

// SELECT user.username, user_group.groupname, company.companyname FROM company 
// LEFT JOIN user_group ON user_group.c_id = company.id 
// LEFT JOIN user ON user.g_id = user_group.id 
// ORDER BY user.sort_num ASC, user.id DESC LIMIT 25 offset 10;
$data = DB::connection('con1')
    ->table('user')
    ->select('user.username', 'user_group.groupname', 'company.companyname')
    ->leftJoin('user_group', 'user_group.c_id', 'company.id')
    ->leftJoin('user', 'user.g_id', 'user_group.id')
    ->orderBy('user.sort_num', 'ASC')
    ->orderBy('user.id', 'DESC')
    ->limit(10, 25)
    ->get();
        
// SELECT * FROM user 
// WHERE username = 'Jackie aa' 
// OR ( NOT EXISTS ( SELECT * FROM user WHERE username = 'Jackie aa' ) AND (username = 'Jackie Conroy' OR username = 'Jammie Haag') ) 
// AND g_id IN ( SELECT id FROM user_group) ORDER BY id DESC LIMIT 1 OFFSET 0 ;
$data = DB::connection('con1')
    ->table('user')
    ->where('username', 'Jackie aa')
    ->orWhereBrackets(function($query) {
        $query->whereNotExists(function($query) {
            $query->table('user')->where('username', 'Jackie aa');
        })->WhereBrackets(function($query) {
            $query->where('username', 'Jackie Conroy')
                    ->orWhere('username', 'Jammie Haag');
        });
    })
    ->whereInSub('g_id', function($query) {
        $query->table('user_group')->select('id');
    })
    ->orderBy('id', 'DESC')
    ->limit(0, 1)
    ->get();

// INSERT INTO user (id, name, age) VALUES (5, 'jack', 18)
$insert_data = [
  'id'   => 5,
  'name' => 'jack',
  'age'  => 18,
];
// 默认返回受影响行数
$effect_row = DB::connection('con1')->table('user')->insert($insert_data);

// INSERT INTO user (id, name, age) VALUES (5, 'jack', 18)
$insert_data = [
  'id'   => 5,
  'name' => 'jack',
  'age'  => 18,
];
// 默认插入行的 id
$effect_row = DB::connection('con1')->table('user')->insertGetLastId($insert_data);

//  UPDATE user SET name = 'mike', age = 23 WHERE name = 'jack';
$update_data = [
  'name' => 'mike',
  'age'  => 23,
];
// 默认返回受影响行数
$effect_row = DB::connection('con1')
    ->table('user')
    ->where('name', 'jack')
    ->update($update_data);

// DELETE FROM user WHERE id = 1;
$effect_row = DB::connection('con1')
    ->table('user')
    ->where('id', 1)
    ->delete();

// 开始事务
DB::connection('con1')->beginTrans();
// DML 操作
DB::connection('con1')
    ->table('user')
    ->where('id', 1)
    ->delete(); 
// 回滚事务
DB::connection('con1')->rollBackTrans();

// 开始事务
DB::connection('con1')->beginTrans();
// DML 操作
DB::connection('con1')
    ->table('user')
    ->where('id', 1)
    ->delete(); 
// 提交事务
DB::connection('con1')->commitTrans();