/lib/Mysql.sql mysqli类文件

<?php


class Mysql
{
    //私有的属性
    private static $dbCon=false;
    private $host;
    private $port;
    private $user;
    private $pass;
    private $db;
    private $charset;
    private $link;
    public $errorNo;
    public $errMsg;

    //私有的构造方法
    private function  __construct($config=array()){
        $this->host = $config['host'] ? $config['host'] : '127.0.0.1';
        $this->port = $config['port'] ? $config['port'] : '3306';
        $this->user = $config['user'] ? $config['user'] : 'root';
        $this->pass = $config['pass'] ? $config['pass'] : 'root';
        $this->db = $config['db'] ? $config['db'] : 'small2';
        $this->charset=isset($arr['$config']) ? $arr['$config'] : 'utf8sss';

        //连接数据库
        $this->connect();
        //选择数据库
        $this->useDb();
        //设置字符集
        $this->setCharset();
    }

    //连接数据库
    private function connect(){
        try {
            $this->link = @mysqli_connect($this->host . ':' . $this->port, $this->user, $this->pass);
            if (mysqli_connect_errno($this->link)) {
                $this->errorNo = mysqli_connect_errno($this->link);
                $this->errMsg = mysqli_connect_error();
                throw new Exception('数据库连接失败');
            }
        } catch (Exception $e) {
            echo $e->getMessage();
            exit;
        }
    }

    //设置字符集
    private function setCharset(){
        try {
            $bool = mysqli_query($this->link, "set names {$this->charset}");
            if (!$bool) {
                $this->errorNo = mysqli_errno($this->link);
                $this->errMsg = mysqli_error($this->link);
                throw new Exception('设置charset错误');
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }

    //选择数据库
    private function useDb(){
        try {
            $bool = mysqli_query($this->link,"use {$this->db}");
            if (!$bool) {
                $this->errorNo = mysqli_errno($this->link);
                $this->errMsg = mysqli_error($this->link);
                throw new Exception('设置charset错误');
            }
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }

    //私有的克隆
    private function __clone(){
        die('clone is not allowed');
    }

    //单例模式
    public static function getInstance($config){
        if(self::$dbCon==false){
            self::$dbCon=new self($config);
        }
        return self::$dbCon;
    }

    //执行sql语句的方法
    public function query($sql){
        try{
            $res=mysqli_query($this->link,$sql);
            if(!$res){
                $this->errorNo = mysqli_errno($this->link);
                $this->errMsg = mysqli_error($this->link);
                throw new Exception('query()方法错误');
            }
            return $res;
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }

    //获得最后一条记录id
    public function getInsertId(){
        return mysqli_insert_id($this->link);
    }

    //查询表的字段
    public function getColumns($table){
        $sql = "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE table_name = '$table' AND table_schema = '$this->db'";

        $query=$this->query($sql);
        $list=array();
        while ($r=$this->getFormSource($query)) {
            $list[]=$r['COLUMN_NAME'];
        }

        return $list;
    }

    //获取一行记录,return array 一维数组
    public function getRow($sql,$type="assoc"){
        $query=$this->query($sql);
        if(!in_array($type,array("assoc",'array',"row"))){
            die("mysqli_query error");
        }
        $funcName="mysqli_fetch_".$type;
        return $funcName($query);
    }

    //获取一条记录,前置条件通过资源获取一条记录
    public function getFormSource($query,$type="assoc"){
        if(!in_array($type,array("assoc","array","row")))
        {
            die("mysqli_query error");
        }
        $funcName="mysqli_fetch_".$type;
        return $funcName($query);
    }

    //获取多条数据，二维数组
    public function getAll($sql){
        $query=$this->query($sql);
        $list=array();
        while ($r=$this->getFormSource($query)) {
            $list[]=$r;
        }
        return $list;
    }

    //添加数据
    public function insert($table,$data){
        //遍历数组，得到每一个字段和字段的值
        $key_str='';
        $v_str='';

        foreach($data as $key=>$v){
            if(empty($v)){
                die("error");
            }
            //$key的值是每一个字段s一个字段所对应的值
            $key_str.=$key.',';
            $v_str.="'$v',";
        }
        $key_str=trim($key_str,',');
        $v_str=trim($v_str,',');

        //判断数据是否为空

        $sql="insert into $table ($key_str) values($v_str)";
        $this->query($sql);
        //返回上一次增加操做产生ID值
        return $this->getInsertid();
    }

    //删除一条数据
    public function deleteOne($table, $where){
        if(is_array($where)){
            foreach ($where as $key => $val) {
                $condition = $key.'='.$val;
            }
        } else {
            $condition = $where;
        }
        $sql = "delete from $table  where $condition";
        $this->query($sql);
        //返回受影响的行数
        return mysqli_affected_rows($this->link);
    }

    //删除多条数据方法
    public function deleteAll($table, $where){
        if(is_array($where)){
            foreach ($where as $key => $val) {
                if(is_array($val)){
                    $condition = $key.' in ('.implode(',', $val) .')';
                } else {
                    $condition = $key. '=' .$val;
                }
            }
        } else {
            $condition = $where;
        }
        $sql = "delete from $table where $condition";
        $this->query($sql);
        //返回受影响的行数
        return mysqli_affected_rows($this->link);
    }

    //更新数据
    public function update($table,$data,$where){
        //遍历数组，得到每一个字段和字段的值
        $str='';
        foreach($data as $key=>$v){
            $str.="$key='$v',";
        }
        $str=rtrim($str,',');
        //修改SQL语句
        $sql="update $table set $str where $where";
        $this->query($sql);
        //返回受影响的行数
        return mysqli_affected_rows($this->link);
    }
}

数据库脚本

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;


DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;


BEGIN;
INSERT INTO `student` VALUES (1, '张三', 23);
INSERT INTO `student` VALUES (2, '李四', 23);
INSERT INTO `student` VALUES (4, '小红', 23);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

index.php 测试

<?php
include './lib/Mysql.php';
$config = [
    'user'=> 'root',
    'pass'=>'root',
    'db'=>'php'
];


//mysqli测试
$db=Mysql::getInstance($config);
//var_dump($db);

//$data = [
//    'name' => '张三',
//    'age' => 23
//];
//$db->insert('student',$data);
//if($db->errorNo){
//    echo $db->errMsg;
//}


//echo $db->getInsertId();

//$r = $db->getColumn('student');
//echo "<pre>";
//var_dump($r);


//$sql="select * from student where id = 3";
//$r = $db->getRow($sql);
//var_dump($r);


//$sql="select * from student";
//$list=$db->getAll($sql);
//echo "<pre>";
//var_dump($list);

//$r = $db->deleteOne('student',['id'=>3]);
//var_dump($r);

$r = $db->update('student',['name'=>'小红'],"id=4");
if($db->errorNo){
    echo $db->errMsg;
}