PHP Class -Mysqli连接数据库

测试文件

<?php
require_once 'Model.php';
$m = new Model($config);
// 查询
// $s = $m->limit('0,5')->table('user')->order('IDX DESC')->where('IDX>10')->select();
// var_dump($s);
// 插入
// $arr = ['USERID'=>'1','PASSWORD'=>'fds'];
// $i = $m->table('user')->insert($arr);
// var_dump($i);
// 删除
// $d = $m->table('user')->where('IDX=21')->delete();
// var_dump($d);
// 更新
$arr = ['USERNAME'=>'1231','LV'=>1];
$u = $m->table('user')->where('IDX=22')->update($arr);
var_dump($u);

var_dump($m->sql);

类文件

<?php

$config = [
    'DB_HOST'       =>  'localhost',
    'DB_USER'       =>  'root',
    'DB_PWD'        =>  '',
    'DB_NAME'       =>  '',
    'DB_CHARSET'    =>  'utf8',
    'DB_PREFIX'     =>  ''
];

class Model
{
    // 主机名
    protected $host;
    // 用户名
    protected $user;
    // 密码
    protected $pwd;
    // 数据库名
    protected $dbName;
    // 字符集
    protected $charSet;
    // 数据表前缀
    protected $prefix;

    // 数据库连接资源
    protected $link;
    // 数据表名
    protected $tableName;

    // sql语句
    protected $sql;
    // 操作数组  查询条件
    protected $options;

    // 构造方法
    function __construct($config)
    {
        // 初始化
        $this->host     = $config['DB_HOST'];
        $this->user     = $config['DB_USER'];
        $this->pwd      = $config['DB_PWD'];
        $this->dbName   = $config['DB_NAME'];
        $this->charSet  = $config['DB_CHARSET'];
        $this->prefix   = $config['DB_PREFIX'];

        // 连接数据库
        $this->link = $this->connect();
        // 得到数据表名  user===>UserModel
        $this->tableName = $this->getTableName();

        // 初始化options数组
        $this->initOptions();
    }

    // 数据库连接
    protected function connect()
    {
        // 连接数据库
        $link = mysqli_connect($this->host, $this->user, $this->pwd);
        if(!$link){
            die('数据库连接失败');
        }
        // 选择数据库
        mysqli_select_db($link, $this->dbName);
        // 设置字符集
        mysqli_set_charset($link, $this->charSet);

        return $link;
    }

    // 得到数据表名
    protected function getTableName()
    {
        // 设置成员变量
        if(!empty($this->tableName)){
            return $this->prefix . $this->tableName;
        }
        // 没设置成员变量，通过类名获取
            // 得到当前类名
        $className = get_class($this);
        // UserModel
        $table = strtolower(substr($className, 0, -5));

        return $this->prefix . $table;
    }

    // 初始化options数组
    protected function initOptions()
    {
        $arr = [
            'where',
            'table',
            'field',
            'order',
            'group',
            'having',
            'limit'
        ];
        foreach ($arr as $value) {
            // 将options数组中键对应的值全部清空
            $this->options[$value] = '';
            // 将table默认设置为tableName
            if($value === 'table'){
                $this->options[$value] = $this->tableName;
            }else if($value == 'field'){
                $this->options[$value] = '*';
            }
        }
    }

    // field
    public function field($field)
    {
        if(!empty($field)){
            if(is_string($field)){
                $this->options['field'] = $field;
            } else if(is_array($field)){
                $this->options['field'] = join(',', $field);
            }
        }

        return $this;
    }

    // table
    public function table($table)
    {
        if(!empty($table)){
            $this->options['table'] = $table;
        }

        return $this;
    }

    // where
    public function where($where)
    {
        if(!empty($where)){
            $this->options['where'] = 'WHERE ' . $where;
        }

        return $this;
    }

    // group
    public function group($group)
    {
        if(!empty($group)){
            $this->options['group'] = 'GROUP BY ' . $group;
        }

        return $this;
    }

    // having
    public function having($having)
    {
        if(!empty($having)){
            $this->options['having'] = 'HAVING ' . $having;
        }

        return $this;
    }

    // order
    public function order($order)
    {
        if(!empty($order)){
            $this->options['order'] = 'ORDER BY ' . $order;
        }

        return $this;
    }

    // limit
    public function limit($limit)
    {
        if(!empty($limit)){
            if(is_string($limit)){
                $this->options['limit'] = 'LIMIT ' . $limit;
            }else if(is_array($limit)){
                $this->options['limit'] = 'LIMIT ' . $limit[0] . ',' . $limit[1];
            }
        }

        return $this;
    }

    // select
    public function select()
    {
        // 预写一个带有占位符的sql语句
        $sql = 'SELECT %FIELD% FROM %TABLE% %WHERE% %GROUP% %HAVING% %ORDER% %LIMIT%';
        // 将options中对应的值依次替换占位符
        $sql = str_replace(
            [
                '%FIELD%',
                '%TABLE%',
                '%WHERE%',
                '%GROUP%',
                '%HAVING%',
                '%ORDER%',
                '%LIMIT%'
            ],
            [
                $this->options['field'],
                $this->options['table'],
                $this->options['where'],
                $this->options['group'],
                $this->options['having'],
                $this->options['order'],
                $this->options['limit']
            ],
            $sql
        );
        // 保存sql语句
        $this->sql = $sql;
        // 执行sql语句
        return $this->query($sql);
    }

    // 获取sql语句
    public function __get($name)
    {
        if($name == 'sql'){
            return $this->$name;
        }

        return false;
    }

    // 插入方法
    public function insert($data)
    {
        // 字符串值加引号
        $data = $this->parseValue($data);
        // 提取字段
        $keys = array_keys($data);
        // 提取值
        $values = array_values($data);
        // sql语句
        $sql = 'INSERT INTO `%TABLE%` (`%FIELD%`) VALUES (%VALUES%)';
        $sql = str_replace(
            ['%TABLE%','%FIELD%','%VALUES%'],
            [$this->options['table'], implode('`,`',$keys), join(',',$values)],
            $sql
        );

        $this->sql = $sql;

        return $this->exec($sql, true);
    }

    // 删除方法
    public function delete()
    {
        $sql = 'DELETE FROM %TABLE% %WHERE%';
        $sql = str_replace(
            ['%TABLE%', '%WHERE%'],
            [$this->options['table'], $this->options['where']],
            $sql
        );
        $this->sql = $sql;

        return $this->exec($sql);
    }

    // 更新函数
    public function update($data)
    {
        $sql = 'UPDATE %TABLE% SET %VALUES% %WHERE%';
        $data = $this->parseValue($data);
        // 拼接 key=val
        $value = $this->parseUpdate($data);
        $sql = str_replace(
            ['%TABLE%', '%VALUES%', '%WHERE%'],
            [$this->options['table'], $value, $this->options['where']],
            $sql
        );
        $this->sql = $sql;

        return $this->exec($sql);
    }

    // query
    protected function query($sql)
    {
        // 初始化options
        $this->initOptions();
        // 执行sql语句
        $result = mysqli_query($this->link, $sql);
        if($result && mysqli_affected_rows($this->link)){
            while ($data = mysqli_fetch_assoc($result)) {
                $newData[] = $data;
            }
        }

        return $newData;
    }

    // exec
    protected function exec($sql, $isInsert = false)
    {
        // 初始化options
        $this->initOptions();
        // 执行sql语句
        $result = mysqli_query($this->link, $sql);
        if($result && mysqli_affected_rows($this->link)){
            // 是否为插入语句
            if($isInsert){
                // 返回自增id
                return mysqli_insert_id($this->link);
            }else{
                // 返回受影响行数
                return mysqli_affected_rows($this->link);
            }
        }
        return false;
    }

    // 将数组中值为字符串的两边加引号
    protected function parseValue($data)
    {
        foreach($data as $key=>$val){
            if(is_string($val)){
                $val = '"' . $val . '"';
            }
            $newData[$key] = $val;
        }

        return $newData;
    }

    protected function parseUpdate($data)
    {
        foreach ($data as $key => $value) {
            $newData[] = $key . '=' . $value;
        }

        return join(',', $newData);
    }

    public function __destruct()
    {
        mysqli_close($this->link);
    }
}