MysqlUtil 仿thinkPHP

<?php


/**
 * 在没有thinkPHP的情况下，可以用着个类
 * 该类实现了thinkPHP里面Model的一些常用方法
 * 暂不支持：数据库切换，连接信息切换
 * 该类使用的是单例模式，不能用$db = new MysqlUtil();
 * 应该这样：$db = MysqlUtil.getInstance();
 * @author ljb
 *
 */
class MysqlUtil
{
	private $db_host;
	private $db_user;
	private $db_psw;
	private $db_database;
	private $coding = 'UTF8'; // 数据库编码，GBK,gb2312,UTF8  注意不是UTF-8
	protected $sql = '';
	protected $options = array();
	protected $mysqli = null; //操作数据库对象
	protected $error = '';
	protected $result = null;
	protected $rows = null;
	//保存类实例的静态成员变量
	private static $_instance;
	
	
	private function __construct()
	{
		$this->_connectDB();
		$this->mysqli->set_charset($this->coding);
	}
	
	//单例方法,用于访问实例的公共的静态方法
	public static function getInstance(){
		if(!(self::$_instance instanceof self)){
			self::$_instance = new self;
		}
		return self::$_instance;
	}
	
	/**
	 * 连接数据库
	 */
	protected function _connectDB()
	{
		$DBConfig = array(
				/* 数据库配置 */
				'DB_HOST'       =>  'localhost',
				'DB_USER'       =>  'root',
				'DB_PWD'        =>  '123456',
				'DB_NAME'       =>  'ljbnote',
		);
		$this->db_host = $DBConfig['DB_HOST']; //C('DB_HOST')
		$this->db_user = $DBConfig['DB_USER']; //C('DB_USER')
		$this->db_psw = $DBConfig['DB_PWD'];   //C('DB_PWD')
		$this->db_database = $DBConfig['DB_NAME']; //C('DB_NAME')
		
		$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 );
		}
	}
	
	/**
	 * 执行一条sql语句
	 * @param String $sql
	 * @return 
	 *  增删改则返回影响行数（可能为0），或者返回false;<br>
	 * 	查询则返回查询结果（数组形式）
	 */
	public function query($sql)
	{
		$this->sql = $sql;
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 指定表名，多个表用逗号隔开，不用`号
	 * @param String $tbName
	 * @return MysqlUtil
	 */
	public function table($tbName){
		$tbArr = explode(',', $tbName);
		$this->options['table'] = ' `'.join('`,`',$tbArr).'` ';
		return $this;
	}
	
	/**
	 * 指定字段，多个字段用逗号隔开，不用`号
	 * @param String $field
	 * @return MysqlUtil
	 */
	public function field($field){
		$fieldArr = explode(',', $field);
		$this->options['field'] = '`'.join('`,`',$fieldArr).'`';
		return $this;
	}
	
	/**
	 * 指定条件。参数暂只支持字符串。
	 * @param String $where
	 * @return MysqlUtil
	 */
	public function where($where){
		$this->options['where'] = ' WHERE '.$where.' ';
		return $this;
	}
	
	/**
	 * 单条数据查询
	 * @return 结果集或者空数组
	 */
	public function find(){
		$this->options['curd'] = 'find';
		$this->options['limit'] = ' LIMIT 1 ';
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 数据查询
	 * @return 结果集或者空数组
	 */
	public function select(){
		$this->options['curd'] = 'select';
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 保存数据<br>
	 * 注意：一定要加上where条件。若要所有数据都update，则条件where(1)
	 * @param Array $data
	 * @return 影响行数或者false
	 */
	public function save($data){
		$this->options['curd'] = 'save';
		$this->options['data'] = $data;
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 插入单条数据
	 * @param Array $data
	 * @return 影响行数1或者false
	 */
	public function add($data){
		$this->options['curd'] = 'add';
		$this->options['data'] = $data;
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 插入多条数据
	 * @param Array $datas
	 * @return 影响行数或者false
	 */
	public function addAll($datas){
		$this->options['curd'] = 'addAll';
		$this->options['data'] = $datas;
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	/**
	 * 删除数据
	 * @return 影响行数或者false
	 */
	public function delete(){
		$this->options['curd'] = 'delete';
		$this->_buildSql();
		$this->_query();
		return $this->_getResult();
	}
	
	public function order($order){
		$this->options['order'] = ' ORDER BY '.$order.' ';
		return $this;
	}
	
	public function group($group){
		$this->options['group'] = ' GROUP BY '.$group.' ';
		return $this;
	}
	
	public function limit($limit){
		$this->options['limit'] = ' LIMIT '.$limit.' ';
		return $this;
	}
	
	/**
	 * 左连接表
	*/
	public function join($join){
		$this->options['join'][] = ' LEFT JOIN '.$join.' ';
		return $this;
	}
	
	/**
	 * 返回最后执行的sql
	 * @return string
	*/
	public function getLastSql(){
		return $this->sql;
	}
	
	/**
	 * 开启事务
	 * @return void
	*/
	public function startTrans(){
		$this->mysqli->autocommit ( false );
	}
	
	/**
	 * 提交事务
	 * @return boolean
	*/
	public function commit(){
		$this->mysqli->commit ();
	}
	
	/**
	 * 事务回滚
	 * @return boolean
	*/
	public function rollback(){
		$this->mysqli->rollback ();
	}
	
	/**
	 * 返回模型的错误信息
	 * @return string
	*/
	public function getError(){
		$info = 'mysql error number is : '.$this->mysqli->errno;
		$info .= '<br>'.$this->mysqli->error;
		return $info;
	}
	
	//拼装sql语句
	protected function _buildSql()
	{
		$table = $this->options['table'];
		$field = $this->options['field']?$this->options['field']:'*';
		$where = $this->options['where'];
		switch ($this->options['curd'])
		{
			case 'find': //find()方法查询数据
			case 'select': //select()方法查询数据
			{
				$join = join(' ',$this->options['join']);
				$order = $this->options['order'];
				$group = $this->options['group'];
				$limit = $this->options['limit'];
				$sql = 'SELECT '.$field.' FROM '.$table.$join.$where.$group.$order.$limit;
				break;
			}
			case 'delete': //delete()方法删除数据
			{
				$sql = 'DELETE FROM '.$table.$where;
				break;
			}
			case 'save': //save()方法更新数据
			{
				$data = $this->options['data'];
				if($data && $where){
					foreach($data as $key => $value){
						$subsql[] = "`$key`='$value'";
					}
					$set = join(',',$subsql);
					$sql = 'UPDATE '.$table.' SET '.$set.$where;
				}
				break;
			}
			case 'add': //add()方法插入单条数据
			{
				$data = $this->options['data'];
				if($data){
					$fieldArr = array();
					$valueArr = array();
					foreach($data as $key => $value){
						$fieldArr[] = "`$key`";
						$valueArr[] = "'$value'";
					}
					$field = '('.join(',',$fieldArr).')';
					$val = '('.join(',',$valueArr).')';
					$sql = 'INSERT INTO '.$table.$field.' VALUES '.$val;
				}
				break;
			}
			case 'addAll': //addAll()方法批量插入数据
			{
				$datas = $this->options['data'];
				if($datas){
					$num = 1;
					foreach ($datas as $data){
						$fieldArr = array();
						$valueArr = array();
						foreach($data as $key => $value){
							$fieldArr[] = "`$key`";
							$valueArr[] = "'$value'";
						}
						if($num == 1){
							$field = '('.join(',',$fieldArr).')';
							$num++;
						}
						$valueArr2[] = '('.join(',',$valueArr).')';
					}
					$val = join(',',$valueArr2);
					$sql = 'INSERT INTO '.$table.$field.' VALUES '.$val;
				}
				break;
			}
		}
		$this->sql = $sql;
	}
	
	//查询
	protected function _query()
	{
		$this->result = $this->mysqli->query($this->sql);
	}
	
	//将结果集转成数组或者返回影响行数
	protected function _getResult()
	{
		if($this->result instanceof mysqli_result)
		{
			$rows = array();
			while ($row = $this->result->fetch_assoc())
			{
				$rows[] = $row;
			}
			if($this->options['curd'] == 'find' && $rows[0])
				return $rows[0];
			return $rows;
		}
		else if($this->mysqli->affected_rows >= 0)
		{
			return $this->mysqli->affected_rows;
		}
		else 
		{
			return false;
		}
	}
	
	function __destruct()
	{
		if (! empty ( $this->result )) {
			$this->result = null;  
		}
		$this->mysqli->close ();
	}
	
	public function __clone(){
		trigger_error('Clone is not allow!',E_USER_ERROR);
	}
}