<?php

/*数据库访问类，支持链式访问

*function table($table):表名

*function where($where):条件

*function field(...$field)：查询字段

*function order($order)：排序方式

*function limit(...$limit)：结果条数

功能函数：

*function item()：查询单条

*function lists()：查询多条

*function insert($data)：插入数据

*function delete()：删除数据

*function update($data)：更新数据

*function count()：总记录

*function my_index($arr,$index)：自定义索引

*/

/*namespace Util\data;*/

class DbUtil{

  private $field = ' * ';

  private $table = '';

  private $where = '';

  private $order = '';

  private $limit = '';

  private $conn = '';

//静态调用,免去了创造对象

  public static function getdb(){

    return $db = new self;

  }

/*  链接数据库，静态函数*/

  private static function db_conn(){

/*    创建链接

    值不要写死，通过配置文件获取*/

    $conn = mysqli_connect('118.193.141.119','sq_phpcl','fec897','sq_phpcl');

    if(!$conn){

      exit('Connection failed:'.mysqli_connect_error());

    }

    return $conn;

  }

/*  设定数据库，链式访问

  $table:'user'*/

  public function table($table){

    $this->table = ' '.$table.' ';

    return $this;

  }

/*  设定条件

  $where ：array('x>1'=>null,'name'=>'chenglin',...)*/

  public function where($where){

    $temp = ' WHERE ';

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

      if($key!=null){

        $temp.=$value.'="'.$key.'" AND ';

      }else{

        $temp.=$value.' AND ';

      }

    }

    $temp = substr($temp,0,-4);

    $this->where = $temp;

    return $this;

  }

/*  设定查询字段

  ...：以数组的形式接收多个参数

  $field:'id,sex'*/

  public function field($field){

    $temp = '';

    $temp .= " {$field} ";

    $this->field = $temp;

    return $this;

  }

/*  设置查询排序排序方式

  $order :'id desc'*/

  public function order($order){

    $order = ' ORDER BY '.$order.' ';

    $this->order = $order;

    return $this;

  }

/*  设置查询结果个数

  $limit: 0,3*/

  public function limit($limit1,$limit2 = ''){

    if($limit2==''){

      $limit = ' LIMIT '.$limit1.' ';

    }else{

      $limit = ' LIMIT '.$limit1.','.$limit2.' ';

    }

    $this->limit = $limit;

    return $this;

  }

/*  重置变量*/

  public function resetting(){

    $this->field = ' * ';

    $this->table = '';

    $this->where = '';

    $this->order = '';

    $this->limit = '';

  }

/*

  查询一个结果

  返回数组*/

  public function item(){

    $conn = self::db_conn();

    $sql = "SELECT{$this->field}FROM{$this->table}{$this->where} LIMIT 1";

    if($result = mysqli_query($conn,$sql)){

      while($row = mysqli_fetch_assoc($result)){

        $rows[]  =  $row;

      }

      //清空属性

      $this->resetting();

      return $rows[0];

    }

  }

/*  查询结果集

  返回二维数组*/

  public function lists($index=''){

    $conn = self::db_conn();

    $sql = "SELECT{$this->field}FROM{$this->table}{$this->where}{$this->order}{$this->limit}";

    if($result = mysqli_query($conn,$sql)){

      while($row = mysqli_fetch_assoc($result)){

        $rows[]  =  $row;

      }

    }

    if($index!=''){

      $rows = $this->my_index($rows,$index);

    }

    //清空属性

    $this->resetting();

    return  $rows;

  }

/*  插入数据

  返回插入的id号插入成功

  insert($data):array('name'=>'cl','sex'=>'男')*/

  public function insert($data){

    $conn = self::db_conn();

    $field = $values = [];

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

      $field[] = $value;

      $values[]  = $key;

    }

    $sql = "INSERT INTO {$this->table} (".implode(',',$field).")VALUES('".implode("','",$values)."')";

    $insert_id = 0;

    if(mysqli_query($conn,$sql)){

      //插入的id

      $insert_id = mysqli_insert_id($conn);

    }

    $this->resetting();

    echo $sql;

    return $insert_id;

  }

/*  删除数据

  返回1删除成功*/

  public function delete(){

    //保险,防止全部被更改

    if($this->where == ''){

      $this->resetting();

      return 0;

    }

    $conn = self::db_conn();

    $sql = "DELETE FROM{$this->table}{$this->where}";

    $res = mysqli_query($conn,$sql);

    $this->resetting();

    return $res;

  }

/*  更新数据

  $data:array('name'=>cl,'age'=>18)

  返回1更新成功*/

  public function update($data){

    //保险,防止全部被更改

    if($this->where == ''){

      $this->resetting();

      return 0;

    }

    $conn = self::db_conn();

    $set = ' SET ';

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

      $set.=$value."='".$key."' ,";

    }

    $set = rtrim($set,',');

    $sql = "UPDATE{$this->table}{$set}{$this->where}";

    $res = mysqli_query($conn,$sql);

    $this->resetting();

    return $res;

  }

/*  自定义索引

  $arr:查询到的lists二维数组

  $index:$key的键*/

  public function my_index($arr,$index){

    $new_arr = [];

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

      $new_arr[$key[$index]] = $key;

    }

    return $new_arr;

  }

  //查询记录总数

  public function count(){

    $conn = self::db_conn();

    $sql = "SELECT * FROM {$this->table}{$this->where}";

    $count = 0;

    if($result = mysqli_query($conn,$sql)){

      //查询总条数

      $count = mysqli_num_rows($result);

    }

    $this->resetting();

    return $count;

  }

}

?>