/php/mysql_performance/db.class.php

<?php
/**
 * phpmysql??????@oswind.com
 * ??????????
 * @version 0.1
 * @author ?????  sunli1223ATgmail.com
 * @copyright (c) 2007 http://oswind.com All rights reserved.      
 */
function C(){
  return false;
}
class BaseDb {
	private $upwd = '';
	private $uname = 'root';
	private $host = 'localhost';
	private $dbname = 'v';
	private $pconnect = 0;
	private $isConnected = false;
	private $querynum = 0;
	private $conn;
	private $charset;
	private $msg = array ();
	private $errnum = 0;
	private $sql = "";
	public function __construct($host, $uname, $upwd, $dbname, $pconnect = 0, $encoding = "utf8") {
		$this->host = $host;
		$this->uname = $uname;
		$this->upwd = $upwd;
		$this->dbname = $dbname;
		$this->pconnect = $pconnect;
		$this->charset = $encoding;
		$this->connection ();
	
	}
	/**
	 * ????????
	 *
	 */
	private function connection() {
		$this->pconnect == 0 ? $this->isConnected = mysql_connect ( $this->host, $this->uname, $this->upwd ) : $this->isConnected = mysql_pconnect ( $this->host, $this->uname, $this->upwd );
		mysql_errno () != 0 && $this->halt ( "Connect($pconnect) to MySQL failed" );
		mysql_query ( "SET character_set_connection=" . $this->charset . ", character_set_results=" . $this->charset . ", character_set_client=binary" );
		mysql_query ( "SET NAMES " . $this->charset );
		if ($this->dbname) {
			$this->select_db ( $this->dbname );
		}
	}
	/**
	 * ?????
	 *
	 * @param String $dbname ????
	 */
	public function select_db($dbname) {
		if (! @mysql_select_db ( $dbname )) {
			$this->halt ( 'Cannot use database' );
		}
	}
	/**
	 * ????????
	 *
	 * @return string ???????
	 */
	public function server_info() {
		return mysql_get_server_info ();
	}
	/**
	 * ?????
	 *
	 * @param string $sql
	 * @param string $method
	 * @return resource
	 */
	public function query($sql, $method = '') {
		$this->sql = $sql;
		$this->debug ( $sql );
		if ($method == 'U_B' && function_exists ( 'mysql_unbuffered_query' )) {
			$query = mysql_unbuffered_query ( $sql );
		} else {
			$query = mysql_query ( $sql );
		}
		
		$this->querynum ++;
		if (! $query)
			$this->halt ( 'Query Error: ' . $sql );
		return $query;
	}
	
	/**
	 * Enter description here...
	 *
	 * @param unknown_type $sql
	 * @return unknown
	 */
	public function get_one($sql) {
		$query = $this->query ( $sql, 'U_B' );
		
		$rs = & mysql_fetch_array ( $query, MYSQL_ASSOC );
		
		return $rs;
	}
	
	/**
	 * ??????
	 *
	 * @param string $sql
	 * @param int $lp
	 * @return bool
	 */
	public function updateBySql($sql, $lp = 1) {
		$this->sql = $sql;
		$this->debug ( $sql );
		if (function_exists ( 'mysql_unbuffered_query' )) {
			$query = mysql_unbuffered_query ( $sql );
		} else {
			$query = mysql_query ( $sql );
		}
		$this->query_num ++;
		if (! $query) {
			$this->halt ( 'Update Error: ' . $sql );
			$this->errnum ++;
		}
		return $query;
	}
	/**
	 * ?????
	 *
	 * @param string $table
	 * @return bool
	 */
	public function deleteTable($table) {
		return $this->updateBySql ( "DROP TABLE $table" );
	}
	public function get_array($result, $result_type = MYSQL_ASSOC) {
		return mysql_fetch_array ( $result, $result_type );
	}
	/**
	 * ????? INSERT ????? ID 
	 * ?????????? AUTO_INCREMENT ???
	 * ? mysql_insert_id() ?? 0??????????????
	 * ????????????????? lastid()? 
	 *
	 * @return int
	 */
	public function lastid() {
		return mysql_insert_id ();
	}
	public function close() {
		return mysql_close ();
	}
	public function affected_rows() {
		return mysql_affected_rows ();
	}
	function free_result($query) {
		return mysql_free_result ( $query );
	}
	
	function insert_id() {
		$id = mysql_insert_id ();
		return $id;
	}
	public function halt($msg) {
		array_push ( $this->msg, $msg );
		$this->errnum ++;
		if (C ( "showdberr" ) == true) {
			echo "<pre>$msg</pre>";
			echo '<pre>' . mysql_errno () . ' : ' . mysql_error () . '</pre>';
			;
		}
	
	}
	/**
	 * ????????0
	 *
	 */
	public function ini_errnum() {
		$this->errnum = 0;
	}
	public function get_errnum() {
		return $this->errnum;
	}
	public function get_msg() {
		return $this->msg;
	}
	public function get_querynum() {
		return $this->querynum;
	}
	public function get_host() {
		return $this->host;
	}
	/**
	 * ???????sql??
	 *
	 * @return string  sql??
	 */
	public function getSql() {
		return $this->sql;
	}
	public function timestamp() {
		date_default_timezone_set ( "prc" );
		return date ( "Y-m-d H:i:s" );
	}
	
	public function debug($str) {
		if (C ( "DEBUG" ) == true) {
			$str = str_replace ( " FROM", " \r\n\tFROM", $str );
			echo '<pre>' . $str . '</pre>';
		}
	}
}
/**
 * ????
 *
 */
class Db extends BaseDb {
	public function beginTtransaction() {
		$this->ini_errnum (); //???????0
		$this->query ( "SET AUTOCOMMIT=0" ); //????????
		$this->query ( "START TRANSACTION" ); //????
	

	}
	/**
	 * ????
	 *
	 */
	public function rollback() {
		$this->query ( "ROLLBACK" );
		$this->query ( "SET AUTOCOMMIT=1" ); //?????????
	}
	public function endTtransaction() {
		if ($this->get_errnum () > 0) {
			$this->rollback ();
			return false;
		} else {
			$this->query ( "COMMIT" ); //????
			$this->query ( "SET AUTOCOMMIT=1" ); //?????????
			return true;
		}
	}
	/**
	 * ?????
	 *
	 * @param string $table ???
	 * @param array $array ?????????????key=>value???
	 */
	public function insert($table, $array) {
		$field = "";
		$values = "";
		foreach ( $array as $key => $value ) {
			$field .= "`" . $key . "`,";
			$values .= "'" . $value . "',";
		}
		$field = trim ( $field, "," );
		$values = trim ( $values, "," );
		$sql = "INSERT INTO " . $table . " (" . $field . ")VALUES (" . $values . ")";
		return $this->updateBySql ( $sql );
	}
	/**
	 * ??????
	 *
	 * @param string $table ??
	 * @param array $array ????
	 * @example <code>
	 * $data[0]['id']=1;
	 * $data[0]['uname']='tom';
	 * $data[1]['id']=2;
	 * $data[2]['uname']='jack';
	 * </code>
	 * @return int
	 */
	public function insertMutiRow($table, $arraydata) {
		$field = "";
		$valueSql = "";
		for($i = 0, $len = count ( $arraydata ); $i < $len; $i ++) {
			$values = "";
			$array = $arraydata [$i];
			foreach ( $array as $key => $value ) {
				if ($i == 0) {
					$field .= "`" . $key . "`,";
				}
				$values .= "'" . $value . "',";
			}
			if ($i == 0) {
				$field = trim ( $field, "," );
			}
			$values = trim ( $values, "," );
			$valueSql .= "(" . $values . "),";
		}
		$valueSql = trim ( $valueSql, "," );
		$sql = "INSERT INTO " . $table . " (" . $field . ")VALUES $valueSql";
		return $this->updateBySql ( $sql );
	}
	/**
	 * ??????update("table",array("name"=>"oswind"),array("id")=>6);
	 * update("table",array("name"=>"oswind"), "id=6 or id=4");update("table",level=level+1, "id=6 or id=4");
	 * <code>
	 * 
	 * </code>
	 * @param string $table ?????
	 * @param Mixed $array ?????????????key=>value???????sql????????,????set a=a+1??????????
	 * @param Mixed $where ????????????????key=>value???????sql????????(id=5 or uid=6)
	 * @return  bool ????true, ????false
	 */
	public function update($table, $array, $where) {
		$str = "";
		$w = "";
		if (is_array ( $array )) {
			$str = $this->listArray ( $array, "," );
		} else {
			$str = $array;
		}
		if (is_array ( $where )) {
			$w = $this->listArray ( $where, "AND" );
		} else {
			$w = $where;
		}
		unset ( $array, $where );
		$sql = "UPDATE `" . $table . "` SET " . $str . " WHERE " . $w;
		return $this->updateBySql ( $sql );
	}
	/**
	 * ??sql??????????????????????????$p?$pagesize????sql???????
	 *
	 * @param string $sql ????
	 * @param int $p [??????] ???null
	 * @param int $pagesize [???????]????null
	 * @return array
	 */
	public function find($sql, $p = null, $pagesize = null) {
		if (is_numeric ( $p ) && is_numeric ( $pagesize )) {
			$p < 1 && $p = 1;
			$pagesize < 1 && $pagesize = 10;
			$offset = $pagesize * ($p - 1);
			$sql .= " LIMIT $offset,$pagesize";
		}
		$result = $this->query ( $sql );
		while ( $rs = $this->get_array ( $result ) ) {
			$list [] = $rs;
		}
		return $list;
	}
	/**
	 * ??????????????AND???????????????????????where??
	 *
	 * @param string $table ?????
	 * @param Mixed $where ???????????
	 * @return bool
	 */
	public function delete($table, $where) {
		if (is_array ( $where )) {
			$w = $this->listArray ( $where, "AND" );
		} else {
			$w = $where;
		}
		$sql = "DELETE FROM $table WHERE $w";
		return $this->updateBySql ( $sql );
	}
	
	public function splitpage($pagesize, $num, $query, $p) {
		$pagestr = "";
		! $p && $p = 1;
		$page = ceil ( $num / $pagesize ); //????
		if ($page < 10) {
			$pagestr .= "<a href=\"" . $query . "\">[??]</a>&nbsp;";
			($p > 1) && $pagestr .= "<a href=\"" . $query . "&p=" . ($p - 1) . "\">[???]</a>&nbsp;";
			for($i = 1; $i <= $page; $i ++) {
				$i == $p ? $pagestr .= "[" . $i . "]&nbsp;" : $pagestr .= "<a href=\"" . $query . "&p=" . $i . "\">[" . $i . "]</a>&nbsp;";
			}
			$p < $page && $pagestr .= "<a href=\"" . $query . "&p=" . ($p + 1) . "\">[???]</a>&nbsp;";
		} else if ($page > 10) {
			$t = ceil ( $p / 10 );
			($mini = ($t - 1) * 10) == 0 && $mini = 1; //???????$p
			($max = $t * 10) > $page && $max = $page; //??
			$pagestr .= "<a href=\"" . $query . "&p=1\">[??]</a>&nbsp;";
			$p > 1 && $pagestr .= "<a href=\"" . $query . "&p=" . ($p - 1) . "\">[???]</a>&nbsp;";
			for($i = $mini; $i <= $max; $i ++) {
				$i == $p ? $pagestr .= "[" . $i . "]&nbsp;" : $pagestr .= "<a href=\"" . $query . "&p=" . $i . "\">[" . $i . "]</a>&nbsp;";
			}
			$p < $page && $pagestr .= "<a href=\"" . $query . "&p=" . ($p + 1) . "\">[???]</a>&nbsp;";
			$pagestr .= "<a href=\"" . $query . "&p=" . $page . "\">[??]</a>";
		}
		return $pagestr;
	}
	/**
	 * ???key,value?????????????????????????sql??
	 *
	 * @param array $array
	 * @param string $mark ????
	 * @return string
	 */
	public function listArray($array, $mark, $table = null) {
		$w = "";
		foreach ( $array as $key => $value ) {
			if (! $value)
				continue;
			$ty = gettype ( $value );
			if ($ty == 'integer' || $ty == 'boolean' || $ty == 'double') {
				if ($table != null) {
					$w .= "$table.`" . $key . "`=" . $value . " $mark";
				} else {
					
					$w .= "`" . $key . "`=" . $value . " $mark";
				}
			} else {
				if ($table != null) {
					$w .= "$table.`" . $key . "`='" . $value . "' $mark";
				} else {
					$w .= "`" . $key . "`='" . $value . "' $mark";
				}
			}
		}
		$w = trim ( $w, " $mark" );
		return $w;
	}
}
?>