MYSQL操作类

<span style="font-family: Arial, Helvetica, sans-serif;"><?php</span>

	class MySQL{
		private $conn;
		private $table;
		private $whereClause;
		private $distinct;
		private $orderBy;
		private $limit;
		private $groupBy;
		private $havingClause;
		private $sql;
		/** select:0,insert:1,update:2,delete:3 **/
		private $type;
		private $showSql = false;		
		/* 
		 * Connect to db, and save connection handler
		 */
		public function connect($host,$user,$password, $dbname, $encoding = 'utf8') {
			$this->conn = mysql_connect($host, $user, $password);
			mysql_select_db($dbname, $this->conn);
			// Set encoding
			$this->query('SET NAMES ' . $encoding);
			return $this;
		}

		/* Accept two arguments, the last one is set to false by default
		 * It has the forms of 'LIMIT a' and 'LIMIT a,b'
		 * Note, for update and delete operation, only the form of 'LIMIT a'
		 * will be valid
		 */
		public function limit($a, $b = false) {
			if(is_integer($a)) {
				$this->limit = 'LIMIT ' . $a;
			}
			if(is_integer($b)) {
				$this->limit .= ',' . $b;
			}
			return $this;
		}

		/*
		 * Set table name
		 */
		public function table($table) {
			$this->table = $table;
			return $this;
		}

		/*
		 * $columns could be either an array or string
		 * $descend is set to be true by default 
		 */
		public function orderBy($columns, $descend = true) {
			$temp = $this->cat($columns);
			if($temp != '') {
				$this->orderBy = 'ORDER BY '  . $temp;
				$this->orderBy .= $descend ? ' DESC' : ' ASC';
			}
			return $this;
		}

		// Only available for select operation
		public function distinct() {
			$this->distinct = 'DISTINCT';
			return $this;
		}

		// 
		public function where($whereClause) {
			if(!empty($whereClause) && is_string($whereClause)) {
				$this->whereClause = 'WHERE ' . $whereClause;
			}
			return $this;
		}

		public function having($havingClause) {
			if(is_string($havingClause)) {
				$this->havingClause = 'HAVING ' . $havingClause;
			}
			return $this;
		}
		
		public function groupBy($columns) {
			$this->groupBy = 'GROUP BY ' . $this->cat($columns); 
			return $this;
		}

		/*
		 * $columns could be either an array or string
		 */
		public function select($columns = '*') {
			$this->type = 0;

			if(!isset($this->table)) {
				// TODO: Table name is a must
			}

			$this->sql = 'SELECT';

			if(isset($this->distinct)) {
				$this->sql .= ' ' . $this->distinct;
			}

			$this->sql .= ' ' . $this->cat($columns);
			$this->sql .= ' FROM ' . $this->table;

			if(isset($this->whereClause)) {
				$this->sql .= ' ' . $this->whereClause;
			}

			if(isset($this->orderBy)) {
				$this->sql .= ' ' . $this->orderBy;
			}

			if(isset($this->limit)) {
				$this->sql .= ' ' . $this->limit;
			}
			return $this->query($this->sql);
		}

		public function delete() {
			$this->type = 3;
			if(!isset($this->table)) {
				// Table name is a must
			}


			$this->sql = 'DELETE FROM ' . $this->table;
			if(isset($this->whereClause)) {
				$this->sql .= ' ' . $this->whereClause;
			}

			if(isset($this->orderBy)) {
				$this->sql .= ' ' . $this->orderBy;
			}

			if(isset($this->limit)) {
				$this->sql .= ' ' . $this->limit;
			}

			return $this->query($this->sql);
		}

		public function update($columns) {
			$this->type = 2;

			if(!isset($this->table)) {
				// Table name is a must
			}

			$temp = '';
			if(is_string($columns)) {
				$temp = $columns;
			}else if(is_array($columns)) {
				foreach ($columns as $key => $value) {
					$temp .= $key . '=' . $this->escape($value) . ',';
				}
				$temp = substr($temp, 0, strlen($temp) - 1);
			}

			$this->sql = 'UPDATE ' . $this->table;
			$this->sql .= ' SET ' . $temp;

			if(isset($this->whereClause)) {
				$this->sql .= ' ' . $this->whereClause;
			}

			if(isset($this->orderBy)) {
				$this->sql .= ' ' . $this->orderBy;
			}

			if(isset($this->limit)) {
				$this->sql .= ' ' . $this->limit;
			}

			return $this->query($this->sql);
		}

		public function insert($columns, $values) {
			$this->type = 1;
			$c = '(' . $this->cat($columns) . ')';
			$v = '';

			// Values
			foreach ($values as $value) {
				$v .= '(';
				foreach ($value as $val) {
					$v .= $this->escape($val) . ',';
				}
				$v = substr($v, 0, strlen($v) - 1);
				$v .= '),';
			}
			$v = substr($v, 0, strlen($v) - 1);

			$this->sql = 'INSERT INTO ' . $this->table;
			$this->sql .= ' ' . $c;
			$this->sql .= ' VALUES' . $v;
			return $this->query($this->sql); 
		}

		public function query($sql){
			if($this->showSql) 
				echo $sql . '<br>';
			$resource = mysql_query($sql, $this->conn);
			$result;
			switch ($this->type) {
				case 0:
					$result = array();
					while($row = mysql_fetch_assoc($resource)) {
						$result[] = $row;
					}
					break;
				case 1:
					break;
				case 2:
					break;
				case 3:
					break;
				
				default:
					break;
			}
			return $result;
		}


		public function cat($items, $ch = ',') {
			if(is_string($items)) {
				return $items;
			}

			if(is_array($items) && !empty($items)) {
				$temp = '';
				foreach ($items as $item) {
					$temp .= $item . $ch;
				}
				// Remove last char and return
				return substr($temp, 0, strlen($temp) - 1);
			}

			return '';
		}

		public function create_database($dbname) {
			$this->query("CREATE DATABASE $dbname");
		}

		public function close() {
			mysql_close($this->conn);
		}

		public function show_sql() {
			$this->showSql = true;
		}

		public function escape($e) {
			if(is_array($e)) 
       	 		return array_map(__METHOD__, $e); 
		    if(!empty($e) && is_string($e)) { 
		        $e = "'" . str_replace(array('\\', "\0", "\n", "\r", "'", '"', "\x1a"), array('\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z'), $e) . "'"; 
		    } 
			return $e;
		}
	}

	$mysql = new MySQL;
	$mysql->connect('127.0.0.1', 'root', 'wsl880903', 'test');
	$mysql->show_sql();
	//$mysql->table('demo')->insert(array('id', 'name'), array(array(4, 'test4'), array(5, 'test5')));
	
	//$mysql->table('demo')->where('id=5')->update(array('name' => 'test-test5'));
	$mysql->table('demo')->where('id=5')->delete();
	$result = $mysql->table('demo')->limit(2,3)->where('id>0')->select(array('id', 'name'));
	$mysql->close();
	
	print_r($result);

CREATE TABLE demo (
	id int ,
       name varchar(10)
)