浅谈php封装mysq操作类

<?php
namespace db;

/**
* 数据库类
*/
class Db
{
	public $conn;
	public $tbl;

	function __construct($servername,$username,$password,$dbname,$table='')
   {
		$conn = new \mysqli($servername,$username,$password,$dbname);

		if ($conn->connect_error) {
		    die("连接失败: " . $conn->connect_error);
		}
		$conn->query('set names utf8');
		$this->tbl = $table;

		$this->conn = $conn;
	}

	public function count($where="1=1",$field="id"){
		$conn = $this->conn;
		$sql = "SELECT COUNT($field) FROM $this->tbl WHERE $where";
		// echo $sql;exit;
		$retval = $conn->query($sql);
		if(!$retval){
			return 0;
		}
		$result = $retval->fetch_row();
		return $result[0];
	}

	//根据ID查找单条
	public function selectOne($id){
		$conn = $this->conn;
		$sql = "select * from $this->tbl where id=$id";
		$retval = $conn->query($sql);
		if(!$retval){
			$conn->close();
			throw new \Exception("no data by id=$id");
		}else{
			$result = $retval->fetch_assoc();
		}
		return $result;
	}

	//查询所有方法 通用
	public function fetchAll($page,$pageindex,$field='*',$where='1=1',$order='id desc'){
		$offset = ($page-1)*$pageindex;
		$sql = "select $field from $this->tbl where $where order by $order limit $offset,$pageindex";
		$data = [];
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval){
			if($retval->num_rows==0){
				$this->close();
				throw new \Exception('查不到任何数据');
			}
			while ($row = $retval->fetch_assoc()) {
				array_push($data,$row);
			}
			return $data;
		}else{
			$this->close();
			throw new \Exception('查不到任何数据');
		}
	}

	//查询所有，但是没有数据的时候不会报错
	public function fetchAll_b($page,$pageindex,$field='*',$where='1=1',$order='id desc'){
		$offset = ($page-1)*$pageindex;
		$sql = "select $field from $this->tbl where $where order by $order limit $offset,$pageindex";
		$data = [];
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval){
			if($retval->num_rows==0){
				return [];
			}
			while ($row = $retval->fetch_assoc()) {
				array_push($data,$row);
			}
			return $data;
		}else{
			return [];
		}
	}

	//获取一条 通用
	public function fetch($field,$where,$order='id desc'){
		$sql = "select $field from $this->tbl where $where order by $order limit 0,1";
		$conn = $this->conn;
		$retval = $conn->query($sql);
		$row = $retval->fetch_assoc();
		if(count($row)==0){
			$this->close();
			throw new \Exception('没有该数据');
		}
		return $row;
	}

	//获取一条 专门用来获取不到数据也不抛出错误
	public function fetch_b($field,$where,$order='id desc'){
		$sql = "select $field from $this->tbl where $where order by $order limit 0,1";
		
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if(!$retval){
			return false;
		}
		$row = $retval->fetch_assoc();
		$row = $row?$row:[];
		if(count($row)==0){
			return false;
		}
		return $row;
	}

	//修改数据库 通用
	public function update($set,$where='1=1'){
		$conn = $this->conn;
		if(empty($set)){
			$this->close();
			throw new \Exception('update缺少 set');
		}
		$sql = "update `$this->tbl` set $set where $where";
		if($conn->query($sql)==true){
			return true;
		}else{
			$error = $conn->error;
			$this->close();
			throw new \Exception('update失败'.$error);
		}
	}

	//关联操作，不过必须自己使用 sql 语句
	public function queryJoin($sql){
		$conn = $this->conn;
		$data = [];
		$retval = $conn->query($sql);
		if(!$retval){
			$this->close();
			throw new \Exception('没有任何数据');
		}
		if($retval->num_rows==0){
			$this->close();
			throw new \Exception('没有任何数据');
		}
		while ($row = $retval->fetch_assoc()) {
			array_push($data,$row);
		}
		return $data;
	}

	//删除
	public function delete($where,$tbl){
		$sql = "delete from $tbl where $where";
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval==true){
			return true;
		}else{
			$this->close();
			throw new \Exception('删除失败');
		}
	}

	//添加 param是数组 [ key =>value ]
	public function insertInfo($param){
		$conn = $this->conn;
		$field = '';
		$val = '';
		foreach ($param as $key => $value) {
			$field .= "`$key`,";
			$val .= "'$value',";
		}
		$field = '( '.trim($field,',').' )';
		$val = '( '.trim($val,',').' )';
		$sql = "INSERT INTO `$this->tbl` $field VALUES $val";
		// var_dump($sql);die;
		if($conn->query($sql)==true){
			$insert_id = $conn->insert_id;

			return $insert_id;
		}else{
			$error = $conn->error;
			$this->close();
			throw new \Exception("插入数据失败: " . $error);
		}
	}

	  /**
     * [insertInfos 添加多条数据]
     * @param  [type] $columns   [字段名 一维数组]
     * @param  [type] $postarray [添加的数据 二维数组]
     * @return [type]            [description]
     */
	public function insertInfos($columns,$postarray){
		// var_dump($arr);die;

		$conn = $this->conn;
		$sql = 'INSERT INTO ' . $this->tbl . ' ( ';

		foreach ( $columns as $colname ) {
			$sql = $sql . $colname . ',';
		}

		$sql = substr ( $sql, 0, - 1 ) . ' ) values ';
		foreach ($postarray as $arr) {
			$sql = $sql . ' ( ';
			foreach ( $columns as $colname ) {
				$sql = $sql . "'" . $arr[$colname] . "',";
			}
			$sql = substr ( $sql, 0, - 1 ) . ' ),';
		}
		$sql =  substr ( $sql, 0, - 1 );


		// var_dump($sql);die;
		if($conn->query($sql)==true){
			// var_dump($conn);die;
			// $insert_id = $conn->insert_id;

			return true;
		}else{
			$this->close();
			throw new \Exception("插入数据失败: " . $conn->error);
		}
	}

	//自由sql 插入修改操作
	public function excel($sql){
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval==true){
			return true;
		}else{
			$this->close();
			throw new \Exception('操作失败 '.$conn->error);
		}
	}

	// 查询所有的表
	public function showTables(){
		$conn = $this->conn;
		$result = [];
		$sql = "SHOW TABLES";
		$retval = $conn->query($sql);
		if(!$retval){
			return false;
		}else{
			while ($row = $retval->fetch_row()) {
				array_push($result,$row[0]);
			}
		}
		return $result;
	}

	//查询所有方法 (不限制条数)
	public function select($table, $field='*', $where='1=1', $order='id desc'){
		$sql = "select $field from $table where $where order by $order";
		// echo($sql);exit;
		$data = [];
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval){
			if($retval->num_rows==0){
				$this->close();
				throw new \Exception('查不到任何数据');
			}
			while ($row = $retval->fetch_assoc()) {
				array_push($data,$row);
			}
			return $data;
		}else{
			$this->close();
			throw new \Exception('查不到任何数据');
		}
	}

	//查询所有方法 (不报错不限制条数)
	public function selectAll($table, $field='*', $where='1=1', $order='id desc'){
		$sql = "select $field from $table where $where order by $order";
		// echo($sql);exit;
		$data = [];
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval){
			if($retval->num_rows==0){
				return [];
			}
			while ($row = $retval->fetch_assoc()) {
				array_push($data,$row);
			}
			return $data;
		}else{
			return [];
		}
	}

	//查询所有方法 (不报错不限制条数) ---测试专用
	public function selectAlls($table, $field='*', $where='1=1', $order='id desc'){
		$sql = "select $field from $table where $where order by $order";
		// echo($sql);exit;
		$data = [];
		$conn = $this->conn;
		$retval = $conn->query($sql);
		if($retval){
			if($retval->num_rows==0){
				return [];
			}
			while ($row = $retval->fetch_assoc()) {
				array_push($data,$row);
			}
			return $data;
		}else{
			return [];
		}
	}

	function __destruct(){
		# 当业务结束时，关闭实例化的数据连接
		$conn = $this->conn;
		if($conn){
			$conn->close();
		}
	}
}

---

小伙伴，如果觉得对你有帮助，帮我点下赞呗。