PHP封装类似thinkphp连贯操作数据库Db类与简单应用示例

<?php
header("content-type:text/html;charset=utf-8");
/**
 *php操作mysql的工具类
 */
class db{
  private $_db = null;//数据库连接句柄
  private $_table = null;//表名
  private $_where = null;//where条件
  private $_order = null;//order排序
  private $_limit = null;//limit限定查询
  private $_group = null;//group分组
  private $_configs = array(
        'hostname' => 'localhost',
        'dbname'  => 'test',
        'username' => 'root',
        'password' => '1234'
      );//数据库配置
  /**
   * 构造函数，连接数据库
   */
  public function __construct(){
    $link = $this->_db;
    if(!$link){
      $db = mysqli_connect($this->_configs['hostname'],$this->_configs['username'],$this->_configs['password'],$this->_configs['dbname']);
      mysqli_query($db,"set names utf8");
      if(!$db){
        $this->showexception("错误信息".mysqli_connect_error());
      }
      $this->_db = $db;
    }
  }
  /**
   * 获取所有数据
   *
   * @param   <type>  $table the table
   *
   * @return   boolean all.
   */
  public function getall($table=null){
    $link = $this->_db;
    if(!$link)return false;
    $sql = "select * from {$table}";
    $data = mysqli_fetch_all($this->execute($sql));
    return $data;
  }
  public function table($table){
    $this->_table = $table;
    return $this;
  }
  /**
   * 实现查询操作
   *
   * @param   string  $fields the fields
   *
   * @return   boolean ( description_of_the_return_value )
   */
  public function select($fields="*"){
    $fieldsstr = '';
    $link = $this->_db;
    if(!$link)return false;
    if(is_array($fields)){
      $fieldsstr = implode(',', $fields);
    }elseif(is_string($fields)&&!empty($fields)){
      $fieldsstr = $fields;
    }
    $sql = "select {$fields} from {$this->_table} {$this->_where} {$this->_order} {$this->_limit}";
    $data = mysqli_fetch_all($this->execute($sql));
    return $data;
  }
  /**
   * order排序
   *
   * @param   string  $order the order
   *
   * @return   boolean ( description_of_the_return_value )
   */
  public function order($order=''){
    $orderstr = '';
    $link = $this->_db;
    if(!$link)return false;
    if(is_string($order)&&!empty($order)){
      $orderstr = "order by ".$order;
    }
    $this->_order = $orderstr;
    return $this;
  }
  /**
   * where条件
   *
   * @param   string $where the where
   *
   * @return   <type> ( description_of_the_return_value )
   */
  public function where($where=''){
    $wherestr = '';
    $link = $this->_db;
    if(!$link)return $link;
    if(is_array($where)){
      foreach ($where as $key => $value) {
        if($value == end($where)){
          $wherestr .= "`".$key."` = '".$value."'";
        }else{
          $wherestr .= "`".$key."` = '".$value."' and ";
        }
      }
      $wherestr = "where ".$wherestr;
    }elseif(is_string($where)&&!empty($where)){
      $wherestr = "where ".$where;
    }
    $this->_where = $wherestr;
    return $this;
  }
  /**
   * group分组
   *
   * @param   string  $group the group
   *
   * @return   boolean ( description_of_the_return_value )
   */
  public function group($group=''){
    $groupstr = '';
    $link = $this->_db;
    if(!$link)return false;
    if(is_array($group)){
      $groupstr = "group by ".implode(',',$group);
    }elseif(is_string($group)&&!empty($group)){
      $groupstr = "group by ".$group;
    }
    $this->_group = $groupstr;
    return $this;
  }
  /**
   * limit限定查询
   *
   * @param   string $limit the limit
   *
   * @return   <type> ( description_of_the_return_value )
   */
  public function limit($limit=''){
    $limitstr = '';
    $link = $this->_db;
    if(!$link)return $link;
    if(is_string($limit)||!empty($limit)){
      $limitstr = "limit ".$limit;
    }elseif(is_numeric($limit)){
      $limitstr = "limit ".$limit;
    }
    $this->_limit = $limitstr;
    return $this;
  }
  /**
   * 执行sql语句
   *
   * @param   <type>  $sql  the sql
   *
   * @return   boolean ( description_of_the_return_value )
   */
  public function execute($sql=null){
    $link = $this->_db;
    if(!$link)return false;
    $res = mysqli_query($this->_db,$sql);
    if(!$res){
      $errors = mysqli_error_list($this->_db);
      $this->showexception("报错啦！<br/>错误号：".$errors[0]['errno']."<br/>sql错误状态：".$errors[0]['sqlstate']."<br/>错误信息：".$errors[0]['error']);
      die();
    }
    return $res;
  }
  /**
   * 插入数据
   *
   * @param   <type>  $data  the data
   *
   * @return   boolean ( description_of_the_return_value )
   */
  public function insert($data){
    $link = $this->_db;
    if(!$link)return false;
    if(is_array($data)){
      $keys = '';
      $values = '';
      foreach ($data as $key => $value) {
        $keys .= "`".$key."`,";
        $values .= "'".$value."',";
      }
      $keys = rtrim($keys,',');
      $values = rtrim($values,',');
    }
    $sql = "insert into `{$this->_table}`({$keys}) values({$values})";
    mysqli_query($this->_db,$sql);
    $insertid = mysqli_insert_id($this->_db);
    return $insertid;
  }
  /**
   * 更新数据
   *
   * @param   <type> $data  the data
   *
   * @return   <type> ( description_of_the_return_value )
   */
  public function update($data){
    $link = $this->_db;
    if(!$link)return $link;
    if(is_array($data)){
      $datastr = '';
      foreach ($data as $key => $value) {
        $datastr .= "`".$key."`='".$value."',";
      }
      $datastr = rtrim($datastr,',');
    }
    $sql = "update `{$this->_table}` set {$datastr} {$this->_where} {$this->_order} {$this->_limit}";
    $res = $this->execute($sql);
    return $res;
  }
  /**
   * 删除数据
   *
   * @return   <type> ( description_of_the_return_value )
   */
  public function delete(){
    $link = $this->_db;
    if(!$link)return $link;
    $sql = "delete from `{$this->_table}` {$this->_where}";
    $res = $this->execute($sql);
    return $res;
  }
  /**
   * 异常信息输出
   *
   * @param   <type> $var  the variable
   */
  private function showexception($var){
    if(is_bool($var)){
      var_dump($var);
    }else if(is_null($var)){
      var_dump(null);
    }else{
      echo "<pre style='position:relative;z-index:1000;padding:10px;border-radius:5px;background:#f5f5f5;border:1px solid #aaa;font-size:14px;line-height:18px;opacity:0.9;'>".print_r($var,true)."</pre>";
    }
  }
}
$db = new db();
//查询操作
var_dump($db->table('user')->where('id > 2')->order('id desc')->limit('2,4')->select());
//插入操作
var_dump($db->table('user')->insert(array('username'=>'user','password'=>'pwd')));
//更新操作
var_dump($db->table('user')->where('id = 1')->update(array('username'=>'user1','password'=>'pwd1')));
//删除操作
var_dump($db->table('user')->where('id = 1')->delete());