11月27日_将PHP公用方法库中的数据库操作函数重写到Db类中

一、将PHP公用方法库中的数据库操作函数重写到Db类中

1. Db类

class Db{
    // 连接参数
    public $dsn;
    public $user;
    public $password;
    // 连接属性
    public $pdo;
    // 连接方法
    public function connect(){
        try{
            $this->pdo = new PDO($this->dsn,$this->user,$this->password);
        }catch (PDOException $e){
            die('数据库连接失败，错误信息：' . $e->getMessage());
        }
    }
    // 通过构造方法实现自动连接数据库
    public function __construct($dsn,$user,$password){
        $this->dsn = $dsn;
        $this->user = $user;
        $this->password = $password;
        $this->connect();
    }
    // 查询多条记录
    function select($table,$fields='*',$where='',$order='',$limit=''){
        // 创建SQL语句
        $sql = 'SELECT ';
        if (is_array($fields)) {
            foreach ($fields as $field){
                $sql .= $field . ', ';
            }
        }else{
            $sql .= $fields;
        }
        $sql = rtrim(trim($sql), ',');
        $sql .= ' FROM ' . $table;
        // 查询条件
        if (!empty($where)){
            $sql .= ' WHERE ' . $where;
        }
        // 排序方式
        if (!empty($order)){
            $sql .= ' ORDER BY ' . $order;
        }
        // 分页
        if (!empty($limit)){
            $sql .= ' LIMIT ' . $limit;
        }
        $sql .= ';';
        // 创建PDO预处理对象
        $stmt = $this->pdo->prepare($sql);
        // 执行查询操作
        if($stmt->execute()){
            if($stmt->rowCount()>0){
                $stmt -> setFetchMode(PDO::FETCH_ASSOC);
                return $stmt->fetchAll();
            }
        }
        return '查询失败';
    }
    // 查询单条记录
    function find($table,$fields,$where=''){
        $sql = 'SELECT ';
        if(is_array($fields)){
            foreach ($fields as $field){
                $sql .= $field . ', ';
            }
        }else{
            $sql .= $fields;
        }
        $sql = rtrim(trim($sql),',');
        $sql .= ' FROM ' . $table;
        if(!empty($where)){
            $sql .= ' WHERE ' . $where;
        }
        $sql .= ' LIMIT 1;';
        $stmt = $this->pdo->prepare($sql);
        if($stmt->execute()){
            if ($stmt->rowCount()>0){
                $stmt->setFetchMode(PDO::FETCH_ASSOC);
                return $stmt->fetch();
            }
        }
        return '查询失败';
    }
    // 新增记录
    function insert($table,$data=[]){
        $sql = 'INSERT INTO ' . $table . ' SET ';
        if (is_array($data)){
            foreach ($data as $k => $v){
                $sql .= "{$k}='{$v}', ";
            }
        }else{
            return '要以关联数组形式传入数据';
        }
        $sql = rtrim(trim($sql),',') . ';';
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute()){
            if($stmt->rowCount()>0){
                return '添加成功了' . $stmt->rowCount() . '条数据';
            }
        }
        return '添加失败';
    }
    // 更新数据
    function update($table,$data=[],$where=''){
        $sql = "UPDATE {$table} SET ";
        if (is_array($data)){
            foreach ($data as $k=>$v){
                $sql .= "{$k}='{$v}', ";
            }
        }
        $sql = rtrim(trim($sql),',');
        if(!empty($where)){
            $sql .= ' WHERE ' . $where;
        }
        $stmt = $this->pdo->prepare($sql);
        if ($stmt->execute()){
            if($stmt->rowCount()>0){
                return '成功修改了' . $stmt->rowCount() . '条数据';
            }
        }
        return '修改失败';
    }
    // 删除数据
    function delete($table,$where=''){
        $sql = 'DELETE FROM ' . $table;
        if(!empty($where)){
            $sql .= ' WHERE ' . $where;
        }
        $stmt = $this->pdo->prepare($sql);
        if($stmt->execute()){
            if($stmt->rowCount()>0){
                return '删除成功了' . $stmt->rowCount() . '条数据';
            }
        }
        return '删除失败';
    }
    // 统计数量
    function count_num($table,$where=''){
        $sql = 'SELECT COUNT(*) AS count_num FROM ' . $table;
        if (!empty($where)){
            $sql .= ' WHERE ' . $where;
        }
        $stmt = $this->pdo->prepare($sql);
        if($stmt->execute()){
            if($stmt->rowCount()>0){
                return $stmt->fetch(PDO::FETCH_ASSOC)['count_num'];
            }
        }
        return '输入条件不正确';
    }
    public function __destruct(){
        $this->pdo = null;
    }
}

2. 查询多条数据

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo '<pre>' . print_r($db->select('computer','computer_name,computer_money','','computer_money DESC'),true);

3. 查询单条数据

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo '<pre>' . print_r($db->find('computer','computer_name,computer_money','computer_id=3'),true);

4. 新增数据

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo $db->insert('computer',['computer_name'=>'戴尔','computer_money'=>8999]);

5. 更新数据

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo $db->update('computer',['computer_money'=>7999],'computer_id=3');

6. 删除数据

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo $db->delete('computer','computer_id=2');

7. 统计数量

$db = new Db('mysql:host=localhost;dbname=test','root','root');
echo $db->count_num('computer');

二、总结

通过将数据库操作函数重写到类中，大大的方便了代码的执行，使数据库操作更便捷。