PHP实现SQL生成器链式操作

namespace Database;

use PDO;
use Exception;

class DB
{
    protected $link;
    protected $options = ['table' => '', 'field' => '*', 'order' => '', 'limit' => '', 'where' => ''];

    public function __construct(array $config)
    {
        $this->connect($config);
    }

    //PDO连接
    protected function connect(array $config)
    {
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            $config['host'],
            $config['dbname'],
            $config['charset']
        );
        $this->link = new PDO($dsn, $config['user'], $config['password']);
        $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
        $this->link->setAttribute(PDO::ATTR_CASE, PDO::CASE_NATURAL);
    }

    //预准备查询
    public function query(string $sql, array $vars = [])
    {
        $sth = $this->link->prepare($sql);
        $sth->execute($vars);
        return $sth->fetchAll(PDO::FETCH_ASSOC);
    }

    //预准备执行
    public function execute(string $sql, array $vars = [])
    {
        $sth = $this->link->prepare($sql);
        return $sth->execute($vars);
    }

    //构建SQL组件
    public function table(string $table)
    {
        $this->options['table'] = $table;
        return $this;
    }

    public function field(...$fields)
    {
        $this->options['field'] = '`' . implode('`,`', $fields) . '`';
        return $this;
    }

    public function limit(...$limit)
    {
        $this->options['limit'] = " LIMIT " . implode(',', $limit);
        return $this;
    }

    public function order(string $order)
    {
        $this->options['order'] = "ORDER BY " . $order;
        return $this;
    }

    public function where(string $where)
    {
        $this->options['where'] = " WHERE " . $where;
        return $this;
    }

    //查询
    public function select()
    {
        //EX:SELECT * FROM news WHERE ORDER LIMIT 1
        $sql = "SELECT {$this->options['field']} FROM {$this->options['table']}{$this->options['where']}{$this->options['order']}{$this->options['limit']}";
        return $this->query($sql);
    }

    //增加
    public function insert(array $vars)
    {
        //EX:INSERT INTO news ('title','author') VALUES (?,?)
        $fields = '`' . implode('`,`', array_keys($vars)) . '`';
        $values = implode(',', array_fill(0, count($vars), '?'));
        $sql = "INSERT INTO {$this->options['table']} ($fields) VALUES ($values)";
        return $this->execute($sql, array_values($vars));
    }

    //更新
    public function update(array $vars)
    {
        //EX:UPDATE news SET title=?,author=? WHERE ...
        if (empty($this->options['where'])) {
            throw new Exception("更新必须设置条件");
        }
        $sql = "UPDATE {$this->options['table']} SET " . implode('=?,', array_keys($vars)) . "=? {$this->options['where']}";
        return $this->execute($sql, array_values($vars));
    }

    //删除
    public function delete()
    {
        //EX:DELETE FROM news WHERE
        if (empty($this->options['where'])) {
            throw new Exception("删除必须设置条件");
        }
        $sql = "DELETE FROM {$this->options['table']} {$this->options['where']}";
        return $this->execute($sql);
    }
}

调用：index.php

use Database\DB;
include "database.php";
header("Content-type:text/html;charset=utf8");
$config = [
    'host'=>'127.0.0.1',
    'user'=>'root',
    'password'=>'123456',
    'dbname'=>'ankium',
    'charset'=>'utf8'
];
try {
    $db = new DB($config);
    //更新操作
    $db->table('news')->where('id>20')->update(['title'=>'三年级','author'=>'郭明']);
    //添加操作
    $db->table('news')->insert(['title'=>'万里','author'=>'无云']);
    //查询操作
    $rows = $db->table('news')->limit(1)->select();
    print_r($rows);
    //删除操作
    $db->table('news')->where('id>4')->delete();
}catch(Exception $e){
    die($e->getMessage());
}

打赏

感谢支持与鼓励～