MySQL 封装DB类

MySQL 封装DB类

闲来无事，封装了一个轻量的，不用手动写SQL的类，不算太强大，满足简单的SQL语句

其他的不说，上代码：

//MySQL配置文件:

<?php

// 第一个为默认链接数据库配置
function choose_config($db_name = 'default'){
    switch ($db_name){
        case 'default':
            return ([
                'host'=>'localhost',
                'user'=>'root',
                'pass'=>'root',
                'database'=>'test',
                'ut'=>'utf8'
            ]);
            break;
        case 'admin':
            return ([
                'host'=>'地址',
                'user'=>'名称',
                'pass'=>'密码',
                'database'=>'库名',
                'ut'=>'utf8'
            ]);
            break;
    }
}

//DB类

<?php
include_once "mysql.php";	// 引入配置文件
date_default_timezone_set('PRC');


class db
{
    private $host = '' ;
    private $user = '' ;
    private $pass = '' ;
    private $database = '' ;
    private $ut = '' ;
    private $conn = null;
    private $sql = '';
    private $result = '';       /* 执行SQL语句，PDO返回结果集 */
    private $error = '';

    /* 构造函数，选择配置里面的数据库 */
    public function __construct($db_name)
    {
        $configu = choose_config($db_name);	// 选择数据库配置
        $this->host = $configu['host'];
        $this->user = $configu['user'];
        $this->pass = $configu['pass'];
        $this->database = $configu['database'];
        $this->ut = $configu['ut'];
    }
    /* 析构函数，关闭连接数据库 */
    public function __destruct()
    {
        $this->conn = null;
    }
    /* 连接数据库 */
    public function connect() {
        try {
            $this->conn = @new PDO("mysql:host=$this->host;dbname=$this->database", "$this->user", "$this->pass");
            $this->conn -> query("SET NAMES $this->ut");
        } catch (PDOException $e) {
            $this -> error_log($e -> getMessage());
        }

    }





    /* 用原生态SQL语句操作数据库 */
    public function sql_query($sql) {
        $this->sql = $sql;
        /* 打开连接 */
        if(!$this -> conn){
            /* 未打开连接，打开链接 */
            $this->connect();
        }
        $result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
        $this->result = $result;
    }


    /* 操作数据库 */
    public function query() {
        /* 打开连接 */
        if(!$this -> conn){
            /* 未打开连接，打开链接 */
            $this->connect();
        }
        /* update安全判断，当SQL语句为跟新语句时，未带条件不执行语句 */
        if(strpos($this->sql,'update') !== false){
            if(strpos($this->sql,'where') !== false){
                $result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
                $this->result = $result;
            }else{
                $this->error = '警告：更新语句，未加where条件！';
            }
        }else{
            $result = $this -> conn -> query($this->sql)or die($this->error_log2($this->conn->errorInfo(),$this->sql));
            $this->result = $result;
        }
    }




    /**
     * 查询一条数据语句 用的 | limit 1
     * @param string $table_name   表的名称
     * @param array $field_arr    字段数组 | ['id','name']
     */
    public function sel_row($table_name,$field_arr){
        $sql = 'select ';
        $sql1 = '';
        foreach ($field_arr as $value){
            $sql1 .= $value.',';
        }
        $sql1 = rtrim($sql1, ',');  // 去除最后一个逗号
        $sql = $sql.$sql1.' from `'.$table_name.'` limit 1';
        $this->sql = $sql;
    }

    /**
     * 查询语句
     * @param string $table_name   表的名称
     * @param string/array $field_arr    字段数组 | ['id','name']
     */
    public function sel($table_name,$field_arr){
        $sql = 'select ';
        if(is_array($field_arr)) {
            $sql1 = '';
            foreach ($field_arr as $value){
                $sql1 .= $value.',';
            }
            $sql1 = rtrim($sql1, ',');  // 去除最后一个逗号
            $sql = $sql.$sql1.' from `'.$table_name.'`';
        }else{
            $sql .= $field_arr.' from `'.$table_name.'`';
        }
        $this->sql = $sql;
    }

    /**
     * 添加where语句
     * @param string/array $para  |  string:'a=b and c<d' | array: [['a','=','b'],['c','<','e']]
     */
    public function add_where($para){
        $sql = $this->sql;
        if(is_array($para)) {
            $sql1 = '';
            foreach ($para as $value){
                $sql1 .= $value[0] . $value[1] . $value[2].' and ';
            }
            $sql1 = rtrim($sql1, ' and ');  // 去除最后一个'and'
            $sql .=' where '.$sql1;
        }else{
            $sql .= ' where '.$para;
        }
        $this->sql = $sql;
    }

    /**
     * 添加分页查询数据语句
     * @param string $by_field  按照哪个字段分页
     * @param int $page         页数
     * @param int $num          一页多少条数据
     * @param bool $DESC        是否开启降序排序，默认不开启，默认为升序
     */
    public function add_page($by_field,$page,$num,$DESC = false){
        $num1 = $num*($page-1);
        $sql = $this->sql;
        if($DESC){
            $sql .= ' ORDER BY '.$by_field.' DESC limit '.$num1.','.$num;
        }else{
            $sql .= ' ORDER BY '.$by_field.' limit '.$num1.','.$num;
        }
        $this->sql = $sql;
    }

    /**
     * 插入一条数据语句
     * @param string $table_name    表名称
     * @param array $field_arr      字段的名称数组 | ['id','name']
     * @param array $value_arr      插入值的数组   | ['1','xcc']
     */
    public function insert_row($table_name,$field_arr,$value_arr){
        $sql = 'insert into `'.$table_name.'` (';
        $key = '';
        foreach ($field_arr as $value){
            $key .= '`'.$value.'`,';
        }
        $key = rtrim($key, ',');  // 去除最后一个逗号

        $val = '';
        foreach ($value_arr as $value){
            $val .= '\''.$value.'\',';
        }
        $val = rtrim($val, ',');  // 去除最后一个逗号

        $sql .= $key.') values ('.$val.')';
        $this->sql = $sql;
    }

    /**
     * 更新一条语句
     * @param string $table_name        表名称
     * @param string/array $field_a     字段数组或字符串 | ['id','name']  |    'id'
     * @param string/array $value_a     值数组或字符串   | ['1','xcc']    |    '1'
     */
    public function update_row($table_name,$field_a,$value_a){
        $sql = 'update `'.$table_name.'` set ';
        if(is_array($field_a)){
            $sql1 = '';
            for ($i=0;$i<count($field_a,0);$i++){
                $sql1 .= $field_a[$i].'=\''.$value_a[$i].'\',';
            }
            $sql1 = rtrim($sql1, ',');  // 去除最后一个逗号
            $sql .= $sql1;
        }else{
            $sql .= $field_a.'=\''.$value_a.'\'';
        }
        $this->sql = $sql;
    }




    /* 可返回多条数据 */
    public function ret_data() {
        $data = array();    $i = 0;
        while ($row = $this->result -> fetch()) {
            $data[$i] = $row;
            $i = $i + 1;
        }
        return $data;
    }
    /* 返回一条值 */
    public function ret_row(){
        return $this->result->rowcount();
    }


    /* 开始事务 */
    public function beginTransaction(){
        /* 打开连接 */
        if(!$this -> conn){
            /* 未打开连接，打开链接 */
            $this->connect();
        }
        $this->conn->beginTransaction();
    }
    /* 提交事务 */
    public function commit(){
        $this->conn->commit();
    }
    /* 回滚事务 */
    public function rollBack(){
        $this->conn->rollBack();
    }


    /* 写入数据库连接错误日志 */
    public function error_log($error) {
        $this->error = $error;
        file_put_contents('/var/www/html/log/'.date("Y-m-d").'.txt',date("Y-m-d H:i:s").' => '.'连接:'.$error."\r\n",FILE_APPEND);
    }
    /* 写入sql语法错误日志 */
    public function error_log2($error,$sql) {
        $error=$error[2];
        $this->error = $error;
        file_put_contents('/var/www/html/log/'.date("Y-m-d").'.txt',date("Y-m-d H:i:s").' => '.'语法:'.$error."\r\n".'sql语句:'.$sql."\r\n",FILE_APPEND);
    }


    /* 得到SQL语句 */
    public function get_sql(){return $this->sql;}
    /* 得到错误信息 */
    public function get_err(){return $this->error;}
}

/* 使用例子 */
//$test = new db('xc');	// 实例化一个链接对象，选择配置名称为'xc'的配置
//$test->sel('order',['id','name']);
//$test->add_where([['a','=','b']]);
//$test->add_page('submit_time','1','20');
//$test->query();
//$res = $test->ret_data();
//var_dump($res);

//$test->insert_row('user_amount',['taobao_user_id','short_url'],['66666666','QQQQQQ']);
//$test->query();
//$res = $test->ret_row();
//var_dump($res);


//$test->update_row('user_amount','rmb','123');
//$test->add_where([['taobao_user_id','=','1701611475']]);
//$test->query();
//echo $test->get_sql();