php链接mysql类

php链接mysql类

<?php
 
class Db{
     
    //存储 类的实例化对象
    private  static $instance;
     
    //存储pdo类的实例化
    private  $pdo;
     
    //存储PDOStament
    private $stmt;
     
    //禁止外部 new 类
    private function __construct($config,$port,$charset){
        try{
            $this->pdo = new  PDO('mysql:host='.$config['host'].';dbname='.$config['dbname'].';port='.$port.'
                ;charset='.$charset,$config['user'],$config['password']);

            //'mysql:host=127.0.0.1;dbname=yt;port=3306;charset=utf8','root','123456'
            //$this->pdo ->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 
        }catch(PDOException $e){
            header("Content-type: text/html;charset=utf-8"); 
            echo 'mysql connect error：'.$e->getMessage();
        }
         
 
    }
     
    //对外访问的方法  实现 类的实例
     
    public static function getInstance($config,$port=3306,$charset="utf8"){
        //判断是否有实例化 没有实例化
        if(!self::$instance instanceof Db ){
            $res = self::$instance = new self($config,$port,$charset);
        }
         
        //有 直接返回实例化
        return self::$instance;
         
    }
     
    //插入数据
    public function insertData($table,$data){
         
        //拼接插入的字段名
        $columns = implode(',',array_keys($data));
        //拼接占位符
        $values = ':'.implode(',:',array_keys($data));
         
        $sql = "INSERT INTO $table ($columns) VALUES($values)";
         
        //处理插入的数据
        $newData = $this->doData($data);
         
        $this->exe($sql,$newData );
         
         
        if($this->stmt->errorCode()==00000){
            return $this->pdo->lastInsertId();
        }else{
            return $this->stmt->errorInfo()[2];
        }
         
    }
     
    //更新数据
     
    public function updateData($table,$data,$where){
        //$sql = "UPDATE user SET user_name=:name WHERE id=:id";
         
        //加个判断 判断$where是否在数据表存在 
         
         
 
        $res = $this->getOne($table,array_keys($data),$where);
         
 
        if(!$res){
            return '不存在你要更新的数据';
        }
         
        $columns = '';
        foreach($data as $k=>$v){
            $columns.=$k.'=:'.$k.',';
        }
        $columns = trim($columns,',');
         
        $sql = "UPDATE $table SET $columns ".$where;
         
 
 
        //处理插入的数据
        $newData = $this->doData($data);
         
         
        $this->exe($sql,$newData);
         
        if($this->stmt->errorCode()==00000){
            return 1;
        }else{
            return $this->stmt->errorInfo()[2];
        }
         
         
    }
     
     
    //查询一条数据
     
    public function getOne($table,$fields,$where){
        //$sql = "SELECT user_name,password FROM user WHERE id=1 /limit 1";
         
        if(count($fields)>1){
            $columns = implode(',',array_values($fields));
        }else{
           
            $columns = $fields[0];
        }
 
        $sql = "SELECT $columns FROM $table ".$where;
         
         
        $this->exe($sql);
         
           
        if($this->stmt->errorCode()==00000){
            return $this->stmt->fetch(PDO::FETCH_ASSOC);//FETCH_NUM
        }else{
            return $this->stmt->errorInfo()[2];
        }
    }
     
    //查询多条数据
     
    public function getAll($table,$fields,$where){
        

        if(count($fields)>1){
            $columns = implode(',',array_values($fields));
        }else{
           

            //$columns = $fields[0];
            $columns = '*';
        }
         
        $sql = "SELECT $columns FROM $table ".$where." order by ctime desc";
        
        $this->exe($sql);
           
        if($this->stmt->errorCode()==00000){
            return $this->stmt->fetchAll(PDO::FETCH_NUM) ;//FETCH_NUM
        }else{
            return $this->stmt->errorInfo()[2];
        }
         
    }
     
    //删除数据
     
    public function deleteData($table,$where){
         
        $sql = "DELETE FROM $table ".$where;
         
         
        $res = $this->getOne($table,['*'],$where);
         
        if(!$res){
            return '不存在你要删除的数据';
        }
         
        $this->exe($sql);
           
        if($this->stmt->errorCode()==00000){
            return 1;
        }else{
            return $this->stmt->errorInfo()[2];
        }
         
         
    }
     
     
    //处理插入的数据
     
    private function doData($data){
        //处理插入的数据
        foreach($data as $k=>$v){
            $key = ':'.$k;
            $newData[$key]=$v;
        }
        return $newData;
    }
     
    //执行sql语句
 
    private function exe($sql,$data=null){
         
 
        $this->stmt = $this->pdo->prepare($sql);
        $this->stmt->execute($data);
    }

    public function export_csv(&$data, $title_arr, $file_name = '') {

        ini_set("max_execution_time", "3600");
        $csv_data = '';

        /** 标题 */
        $nums = count($title_arr);

        for ($i = 0; $i < $nums - 1; ++$i) {
            $csv_data .= '"' . $title_arr[$i] . '",';
            
        }

        if ($nums > 0) {
         $csv_data .= '"' . $title_arr[$nums - 1] . "\"\r\n";
        }

        foreach ($data as $k => $row) {
            for ($i = 0; $i < $nums - 1; ++$i) {
                @$row[$i] = str_replace("\"", "\"\"", $row[$i]);
               
                if($i==2 || $i==4){
                     $csv_data .= '"' . $row[$i]."\t" . '",';
                }else{
                     $csv_data .= '"' . $row[$i] . '",';
                }
            }
            $csv_data .= '"' . $row[$nums - 1] . "\"\r\n";
            unset($data[$k]);
        }

        $csv_data = mb_convert_encoding($csv_data, "cp936", "UTF-8");

        $file_name = empty($file_name) ? date('Y-m-d-H-i-s', time()) : $file_name;

        if (strpos($_SERVER['HTTP_USER_AGENT'], "MSIE")) { // 解决IE浏览器输出中文名乱码的bug
         $file_name = urlencode($file_name);
         $file_name = str_replace('+', '%20', $file_name);
        }

        $file_name = $file_name . '.csv';
        header("Content-type:text/csv;");
        header("Content-Disposition:attachment;filename=" . $file_name);
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
        header('Expires:0');
        header('Pragma:public');
        echo $csv_data;
    }
     
    //禁止外部 克隆对象
    private function __clone(){}
     
}
 
//$db = new  PDO('mysql:host=127.0.0.1;dbname=yt;port=3306;charset=utf8','root','123456');
//var_dump($db);die; 
$config=['host'=>'127.0.0.1','dbname'=>'xxx','user'=>'root','password'=>'root'];
$db = Db::getInstance($config);
$table = 'data';
//$data = ['user_name'=>'asdfasdfasdasdf','password'=>'asdfasd','fee'=>12];
//echo $db->insertData($table,$data);
 
//echo $db->updateData($table,$data,' where id=7');
//$fields = ['username','did','phone','code','car','city','district','seller','address','tag','ctime','cw','lx_time','gc_time'];
$fields = ['username','cw','phone','car','code','lx_time','gc_time','pro','city','district','ctime'];
//var_dump($db->getOne($table,$fields,'WHERE id=2'));
 //echo "<pre>";
$res = $db->getAll($table,$fields,' ');

foreach($res as $kk=>$vv ){
    foreach($vv as $kkk=>$vvv){
        $res[$kk][$kkk] = urldecode($vvv);
    }
}

$title_arr = ['11','22','333','444','555','666','777','88','99','00','---'];
$db->export_csv($res,$title_arr,'信息');
//echo  $db->deleteData($table,'WHERE id=1');
 
//$sql = "SELECT user_name,password FROM user WHERE id=1 /limit 1";
 
//$sql = "INSERT INTO user (user_name,password) VALUES(:name,:password)";
 
 
 
//$sql = "UPDATE user SET user_name=:name WHERE id=:id";
 
//$stmt = $db->prepare($sql);
 
//$data = [':name'=>$user_name,':password'=>$password];
 
//$res = $stmt->execute($data);