PHP 数据库操作类

<?php
interface IMysqliUtil {
	/**
	 * 插入数据
	 *
	 * @param $tbname 表名       	
	 * @param $data 表数据       	
	 */
	function insert($tbname, $data);
	
	/**
	 * 删除数据
	 *
	 * @param $tbname 表名       	
	 * @param $data 表数据       	
	 */
	function delete($tbname, $data);
	
	/**
	 * 更新数据
	 *
	 * @param $tbname 表名       	
	 * @param $data 表数据       	
	 */
	function update($tbname, $data);
	
	/**
	 * 查找数据
	 *
	 * @param $tbname 表名       	
	 * @param $condition 条件       	
	 */
	function from($tbname, $condition);
	
	/**
	 * 查找数据，根据主键值
	 *
	 * @param $tbname 表名       	
	 * @param $id 主键值       	
	 */
	function get($tbname, $id);
	
	/**
	 * 通过原生的sql语句获取数据
	 *
	 * @param $sql 执行的sql语句       	
	 */
	function getRowsBySql($sql);
	
	/**
	 * 事务回滚
	 */
	function rollback();
	
	/**
	 * 开始事务
	 */
	function beginTransaction();
	
	/**
	 * 提交事务
	 */
	function commit();
	
	/**
	 * 释放结果集
	 */
	function free();
}
?>

<?php
class MysqliUtil implements IMysqliUtil {
	private $db_host;
	private $db_user;
	private $db_psw;
	private $db_database;
	private $coding; // 数据库编码，GBK,UTF-8,gb2312
	private $mysqli;
	private $result;
	private $show_error = true; // 是否开启显示错误
	private $bulletin = true; // 是否开启错误记录
	
	function __construct() {
		$this->db_host = 'localhost';
		$this->db_user = 'root';
		$this->db_psw = '123456';
		$this->db_database = 'test';
		$this->coding = 'utf-8';
		$this->_connect ();
	}
	
	/*
	 * 增加记录
	 */
	public function insert($tbname, $data) {
		if (is_array ( $data [0] )) { // 增加多条记录
			$this->_insert_many ( $tbname, $data );
		} else { // 增加一条记录
			$this->_insert_one ( $tbname, $data );
		}
	}
	
	/*
	 * 删除记录
	 */
	public function delete($tbname, $data) {
		if (is_array ( $data [0] )) { // 删除多条记录
			$sql = $this->_delete_many ( $tbname, $data );
		} else { // 删除一条记录
			$sql = $this->_delete_one ( $tbname, $data );
		}
	}
	
	/*
	 * 更新数据
	 */
	public function update($tbname, $data) {
		if (is_array ( $data [0] )) { // 更新多条记录
			$this->_update_many ( $tbname, $data );
		} else { // 更新一条记录
			$this->_update_one ( $tbname, $data );
		}
	}
	
	/*
	 * 查询数据
	 */
	public function from($tbname, $condition, $start = -1, $pageSize = 0) {
		$sql = "select * from $tbname ";
		if ($condition) {
			$sql .= $condition;
		}
		if ($start >= 0 && $pageSize > 0) {
			$sql .= " limit $start,$pageSize";
		}
		$rows = $this->getRowsBySql ( $sql );
		return $rows;
	}
	
	/*
	 * 根据表主键值获得单条记录
	 */
	public function get($tbname, $id) {
		$PrimaryColumn = $this->_getPRY ( $tbname );
		$sql = "select * from $tbname where $PrimaryColumn=$id";
		$rows = $this->getRowsBySql ( $sql );
		return $rows [0];
	}
	
	/*
	 * 通过原生的sql语句获取数据
	 */
	public function getRowsBySql($sql) {
		$re = $this->mysqli->query ( $sql );
		while ( $row = $re->fetch_assoc () ) {
			$rows [] = $row;
		}
		return $rows;
	}
	
	/*
	 * 事务回滚
	 */
	public function rollback() {
		$this->mysqli->rollback ();
	}
	
	/*
	 * 开始事务
	 */
	public function beginTransaction() {
		$this->mysqli->autocommit ( false );
	}
	
	/*
	 * 提交事务
	 */
	public function commit() {
		$this->mysqli->commit ();
	}
	
	/*
	 * 释放结果集
	 */
	public function free() {
		$this->result = null;
	}
	
	// ///private
	
	private function _connect() {
		$this->mysqli = new mysqli ( $this->db_host, $this->db_user, $this->db_psw, $this->db_database );
		if ($this->mysqli->connect_error) {
			die ( 'Connect Error (' . $this->mysqli->connect_errno . ') ' . $this->mysqli->connect_error );
		}
		$this->mysqli->query ( "SET NAMES $this->coding" );
	}
	
	// 执行sql语句
	private function _query($sql) {
		$this->result = $this->mysqli->query ( $sql );
		return $this->result;
	}
	
	// 获得主键列名
	function _getPRY($tbname) {
		$rows = $this->getRowsBySql ( "describe $tbname" );
		foreach ( $rows as $row => $filed ) {
			if ($filed ['Key'] == 'PRI')
				return $filed ['Field'];
		}
	}
	
	// 增加一条记录
	private function _insert_one($tbname, $data) {
		$sql = "INSERT INTO $tbname set ";
		foreach ( $data as $field => $value ) {
			$sql .= "$field='$value',";
		}
		if ($data [$this->_getPRY ( $tbname )] > 0) {
			$sql = substr ( $sql, 0, strlen ( $sql ) - 1 );
		} else {
			$sql .= $this->_getPRY ( $tbname ) . '=' . ($this->_getMaxId ( $tbname ) + 1);
		}
		$this->_query ( $sql );
		if ($this->mysqli->errno > 0) {
			$this->_show_error ( $sql, $this->mysqli );
		}
	}
	
	// 增加多条记录
	private function _insert_many($tbname, $datas) {
		foreach ( $datas as $data ) {
			$this->insert_one ( $tbname, $data );
		}
	}
	
	// 删除一条记录
	private function _delete_one($tbname, $data) {
		$PrimaryColumn = $this->_getPRY ( $tbname );
		$PrimaryValue = $data [$PrimaryColumn];
		$sql = "DELETE FROM $tbname WHERE $PrimaryColumn='$PrimaryValue'";
		$this->_query ( $sql );
	}
	
	// 删除多条记录
	private function _delete_many($tbname, $datas) {
		foreach ( $datas as $data ) {
			$this->delete_one ( $tbname, $data );
		}
	}
	
	// 更新一条记录
	private function _update_one($tbname, $data) {
		$PrimaryColumn = $this->_getPRY ( $tbname );
		$PrimaryValue = $data [$PrimaryColumn];
		$sql = "UPDATE $tbname SET ";
		foreach ( $data as $field => $value ) {
			$sql .= " $field='$value',";
		}
		$sql = substr ( $sql, 0, strlen ( $sql ) - 1 ) . " WHERE $PrimaryColumn='$PrimaryValue'";
		$this->_query ( $sql );
		if ($this->mysqli->errno > 0) {
			$this->_show_error ( $sql, $this->mysqli );
		}
	}
	
	// 更新多条记录
	private function _update_many($tbname, $datas) {
		foreach ( $datas as $data ) {
			$this->update_one ( $tbname, $data );
		}
	}
	
	// 获取一个表的主键列的最大值
	private function _getMaxId($tbname) {
		$PrimaryColumn = $this->_getPRY ( $tbname );
		$sql = "select max($PrimaryColumn) as maxId from $tbname";
		$rows = $this->getRowsBySql ( $sql );
		$insertId = $rows [0] ['maxId'] + 1;
		return $rows [0] ['maxId'];
	}
	
	// 析构函数，自动关闭数据库,垃圾回收机制
	public function __destruct() {
		if (! empty ( $this->result )) {
			$this->free ();
		}
		$this->mysqli->close ();
	}
	
	// 输出显示sql语句
	private function _show_error( $sql , $mysqli) {
		if (! $this->show_error) {
			return;
		}
		echo "<fieldset>";
		echo "<legend><font color='red'>错误信息提示:</font></legend><br />";
		echo "<div style='font-size:14px; clear:both; font-family:Verdana, Arial, Helvetica, sans-serif;'>";
		echo "<div>";
		echo "<font color='#999999'>错误号：" . $mysqli->errno . "</font>";
		echo "</div><br />";
		echo "<div>";
		echo "<font color='#999999'>错误原因：" . $mysqli->error . "</font><br />";
		echo "</div>";
		echo "<font color='#999999'><pre>你的sql语句:<br>" . $sql . "</pre></font>";
		if ($this->bulletin) {
			$this->_recordError ( $sql, $mysqli );
		}
		echo "<br />";
		echo "</div>";
		echo "</fieldset>";
		echo "<br />";
	}
	
	private function _recordError($sql, $mysqli) {
		$time = date ( "Y-m-d H:i:s" );
		$message = "\r\n 错误号：" . $mysqli->errno;
		$message .= "\r\n 错误原因：" . $mysqli->error;
		$message .= "\r\nsql语句:$sql";
		$message .= "\r\n时间 :$time";
		$message .= "\r\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\r\n";
		$server_date = date ( "Y-m-d" );
		$filename = $server_date . ".txt";
		$file_path = "error/" . $filename;
		$file = "error"; // 设置文件保存目录
		                 
		// 建立文件夹
		if (! file_exists ( $file )) {
			if (! mkdir ( $file, 0777 )) {
				// 默认的 mode 是 0777，意味着最大可能的访问权
				die ( "upload files directory does not exist and creation failed" );
			}
		}
		// 建立txt日期文件
		if (! file_exists ( $file_path )) {
			// echo "建立日期文件";
			fopen ( $file_path, "w+" );
			// 首先要确定文件存在并且可写
			if (is_writable ( $file_path )) {
				// 使用添加模式打开$filename，文件指针将会在文件的开头
				if (! $handle = fopen ( $file_path, 'a' )) {
					echo "不能打开文件 $filename";
					exit ();
				}
				// 将$somecontent写入到我们打开的文件中。
				if (! fwrite ( $handle, $message )) {
					echo "不能写入到文件 $filename";
					exit ();
				}
				// echo "文件 $filename 写入成功";
				echo "——错误记录被保存!";
				// 关闭文件
				fclose ( $handle );
			} else {
				echo "文件 $filename 不可写";
			}
		} else {
			// 首先要确定文件存在并且可写
			if (is_writable ( $file_path )) {
				// 使用添加模式打开$filename，文件指针将会在文件的开头
				if (! $handle = fopen ( $file_path, 'a' )) {
					echo "不能打开文件 $filename";
					exit ();
				}
				// 将$somecontent写入到我们打开的文件中。
				if (! fwrite ( $handle, $message )) {
					echo "不能写入到文件 $filename";
					exit ();
				}
				// echo "文件 $filename 写入成功";
				echo "<font color='#999999'>——错误记录被保存!</font>";
				// 关闭文件
				fclose ( $handle );
			} else {
				echo "文件 $filename 不可写";
			}
		}
	}
}
?>