原生php操作mysql简单封装

有时可能会遇到用原生php写一些小脚本的场景，可以简单封装下sql语句写起来更方便

class mysql
{
    private $host = 'localhost';
    private $name = 'root';
    private $pass = 'root';
    private $database = 'test';
    private $port = 3306;
    private $mysql;

    /**------------------------------------------------
     *    构造函数 使用PHP内置的mysqli类对数据库进行连接
     *------------------------------------------------*/
    public function __construct()
    {
        $this->mysql = @new mysqli($this->host, $this->name, $this->pass, $this->database, $this->port);
        if($this->mysql->connect_errno){
            exit("数据库连接错误 {$this->mysql->connect_error}");
        }
    }

    /**------------------------------------------------
     *    根据表名, 字段名, 值 对数据表进行插入数据
     *    成功返回插入记录的ID 否则返回0
     *------------------------------------------------*/
    public function insert($table, $data)
    {
        //解析传递过来的字段 转为字串
        $field = '';
        $worth = '';
        $i = 1;
        $length = count($data);

        foreach ($data as $key => $value) {
            if($length != $i){
                $field .= $key . ',';
                if(gettype($value) == 'string'){
                    $worth .= "'$value',";
                }else{
                    $worth .= $value;
                }
            }else{
                $field .= $key;
                if(gettype($value) == 'string'){
                    $worth .= "'$value'";
                }else{
                    $worth .= $value;
                }
            }
            $i++;
        }

        $sql = "insert into {$table}({$field})values($worth)";
        $result = $this->mysql->query($sql);
        //获取返回的ID
        return $this->mysql->insert_id;
    }

    /**------------------------------------------------
     *    根据表名 where条件的键值 对数据记录进行删除
     *    成功返回 true 否则返回 false
     *------------------------------------------------*/
    public function delete($table, $key, $value)
    {
        if(gettype($value) == 'string'){
            $value = "'{$value}'";
        }
        $sql = "delete from {$table} where {$key} = {$value}";
        return $this->mysql->query($sql);
    }

    /**------------------------------------------------
     *    根据表名 where条件的键值 对数据记录进行查询
     *    成功返回数组 否则返回空数组
     *------------------------------------------------*/
    public function get($table, $condition)
    {
        $where = '';
        foreach ($condition as $key => $value) {
            if(gettype($value) == 'string'){
                $value = "'{$value}'";
            }
            $where .= " $key = $value and";
        }
        $where = "where " . mb_substr($where, 0, mb_strlen($where) - 3);
        $sql = "select * from {$table} {$where}";
        $sql = trim($sql);
        $result = $this->mysql->query($sql);
        $data = [];
        while ($res_data = $result->fetch_assoc()) {
            var_dump($res_data);
            $data[] = $res_data;
        }
        return $data;
    }

    /**------------------------------------------------
     *    根据表名 字段及值 条件 修改记录
     *    成功返回true 否则返回false 
     *------------------------------------------------*/
    public function update($table, $data, $where)
    {
        $field = '';
        foreach ($data as $key => $value) {
            if(gettype($value) == 'string'){
                $value = "'{$value}'";
            }
            $field .= "{$key} = {$value}, ";
        }
        $field = mb_substr($field, 0, mb_strlen($field) -2);

        //条件
        $worth = '';
        foreach ($where as $key => $value) {
            if(gettype($value) == 'string'){
                $value = "'{$value}'";
            }
            $worth .= "{$key} = {$value} and ";
        }
        $worth = 'where ' . mb_substr($worth, 0, mb_strlen($worth) - 5);
        $sql = "update {$table} set {$field} {$worth}";
        return $this->mysql->query($sql);
    }

    /**------------------------------------------------
     *    将传递过来的值转义并返回
     *------------------------------------------------*/
    public function escape($string)
    {
        return $this->mysql->escape_string($string);
    }

    /**------------------------------------------------
     *    析构函数 关闭数据库连接
     *------------------------------------------------*/
    public function __destruct()
    {
        $this->mysql->close();
    }
}

调用

//引入 MySql 类
include_once('mysql.php');

$mysql = new mysql();

//对数据记录进行删除操作
$res = $mysql->delete('表名', '条件字段名', '值');

//对数据表进行插入数据操作
$res = $mysql->insert('表名', [条件字段及值 如 'name' => 'root'...]);

//对数据表进行查询操作
$res = $mysql->get('表名', [条件字段及值 如 'name' => 'root'...]);

//对数据记录进行修改更新
$res = $mysql->update('表名', [条件字段及值 如 'name' => 'root'...]);