1010-个人博客之【数据库访问类封装】

1010-个人博客之【数据库访问类封装】

Db.php文件

<?php

// 数据库访问类

class Db{

  function __construct(){

    $this->pdo = new PDO('mysql:host=127.0.0.1;dbname=myblog','root','root');

      }

//指定查询表名方法

  public function table($table){//$table接收外部传来的表名

    $this->table = $table;

    $this->field = '*';

    $this->where = [];

    $this->order = '';

    $this->limit = 0;

    $this->insert =[];

    return $this;

    }

//查询方法

  //1.指定查询字段方法

  public function field($field){//$table接收外部传来的表名

    $this->field = $field;

    return $this;

    }

  //2.指定查询条件方法

  public function where($where){//$table接收外部传来的表名

    $this->where = $where;

    return $this;

    }

  //3.指定查询条件方法

  public function order($order){//$table接收外部传来的表名

    $this->order = $order;

    return $this;

    }

  //4.指定查询数量方法

  public function limit($limit){//$table接收外部传来的表名

    $this->limit = $limit;

    return $this;

    }

  //5.查询一条记录

  public function item(){

    //$sql = "SELECT {$this->field} FROM `{$this->table}` WHERE `id`=12 and `cid`=2 LIMIT 1";

    $sql = $this->_build_sql('select');

    $sql .= " LIMIT 1";

    $stmt = $this->pdo->prepare($sql);

    $stmt->execute();

    $item = $stmt->fetchAll(PDO::FETCH_ASSOC);

    return $item ? $item[0] : false;

  }

  //6.查询不固定多条记录

  public function lists(){

    $sql = $this->_build_sql('select');exit($sql);

    $stmt = $this->pdo->prepare($sql);

    $stmt->execute();

    return  $stmt->fetchAll(PDO::FETCH_ASSOC);

  }

  //“增加（添加）、删除、改变（更新）”数据 方法

    //1.增加数据方法

    public function insert($data){

      //$sql = "INSERT INTO {$this->table}(`cid`,`title`)VALUES(1,'insert测试')";

      $sql = $this->_build_sql('insert',$data);

      $stmt = $this->pdo->prepare($sql);

      $stmt->execute();

      return  $this->pdo->lastInsertId();

    }

  //构造sql语句

  private function _build_sql($type,$data = NULL){

    if($type == 'select'){

      $sql = "SELECT {$this->field} FROM `{$this->table}`";

      if($this->where){

        $sql .= $this->_build_where_sql();

      }

      $this->order && $sql .= " order by {$this->order}";

      $this->limit && $sql .= " LIMIT {$this->limit}";

    }

    if($type == 'insert'){

      $sql = $this->_build_insert_sql($data);

    }

    return $sql;

  }

  //构造where_sql

  private function _build_where_sql(){

    $sql = '';

    $where = '';

    foreach ($this->where as $key => $value) {

      if(is_string($value)){

      $where .= " and `{$key}`='{$value}'";

    }else{

      $where .= " and `{$key}`={$value}";

    }

    }

    $where = ltrim($where," and");//去掉多余的“空格+and”

    $sql .= " WHERE ".$where;//添加上缺的“空格+and+空格”

    }

  //构造insert_sql

  private function _build_insert_sql($data){

    $sql = "INSERT INTO {$this->table}";

    $fields = $values = [];

    foreach ($data as $key => $value) {

      $fields[] = '`'.$key.'`';

      $values[] = is_string($value) ? "'".$value."'":$value ;

    }

    $sql .= '('.implode(',',$fields).')VALUES('.implode(',',$values).')';

  }

}