/*----------------------------------------------------------------------
*MySQL操作类
*----------------------------------------------------------------------
*Author: justmepzy(justmepzy@gmail.com)
*----------------------------------------------------------------------
*/
class MySQL{
private $db_mysql_hostname;
private $db_mysql_username;
private $db_mysql_password;
private $db_mysql_database;
private $db_mysql_port;
private $db_mysql_charset;
private $query_list = array();
//查询次数
protected $conn;
//事务指令数
protected $transTimes = 0;
//返回或者影响记录数
protected $numRows = 0;
//错误信息
protected $error = '';
//
public $query_count = 0;
//查询开始时间
public $query_start_time;
//当前查询ID
protected $queryID;
//当前连接
public function __construct($hostname_or_conf,$username,$password,$database,$port = '3306',$char = 'utf8')
{
if(is_array($hostname_or_conf))
{
$this->db_mysql_hostname = $hostname_or_conf['hostname'];
$this->db_mysql_username = $hostname_or_conf['username'];
$this->db_mysql_password = $hostname_or_conf['password'];
$this->db_mysql_database = $hostname_or_conf['database'];
$this->db_mysql_port = isset($hostname_or_conf['port'])?$hostname_or_conf['port']:'3306';
$this->db_mysql_charset = isset($hostname_or_conf['charset'])?$hostname_or_conf['charset']:'utf8';
}
elseif(!empty($hostname_or_conf)||!empty($username)||!empty($password)||!empty($database))
{
$this->db_mysql_hostname = $hostname_or_conf;
$this->db_mysql_username = $username;
$this->db_mysql_password = $password;
$this->db_mysql_database = $database;
$this->db_mysql_port = $port;
$this->db_mysql_charset = $char;
}
else
{
die('configuration error.');
}
$this->connect();
}
private function connect()
{
$server = $this->db_mysql_hostname.':'.$this->db_mysql_port;
$this->conn = mysql_connect($server,$this->db_mysql_username,$this->db_mysql_password,true) or die('Connect MySQL DB error!');
mysql_select_db($this->db_mysql_database,$this->conn) or die('select db error!');
mysql_query("set names " . $this->db_mysql_charset, $this->conn);
}
/**
*----------------------------------------------------------
* 设置数据对象值
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
*table,where,order,limit,data,field,join,group,having
*----------------------------------------------------------
*/
public function table($table)
{
$this->query_list['table'] = $table;
return $this;
}
public function where($where)
{
$this->query_list['where'] = $where;
return $this;
}
public function order($order)
{
$this->query_list['order'] = $order;
return $this;
}
public function limit($offset,$length)
{
if(!isset($length))
{
$length = $offset;
$offset = 0;
}
$this->query_list['limit'] = 'limit '.$offset.','.$length;
return $this;
}
public function data($data)
{
/*
if(is_object($data))
{
$data = get_object_vars($data);
}
elseif (is_string($data))
{
parse_str($data,$data);
}
elseif(!is_array($data))
{
//Log:DATA_TYPE_INVALID
}
*/
$this->query_list['data'] = $data;
return $this;
}
public function field($fields)
{
$this->query_list['fields'] = $fields;
return $this;
}
public function join($join)
{
$this->query_list['join'] = $join;
return $this;
}
public function group($group)
{
$this->query_list['group'] = $group;
return $this;
}
public function having($having)
{
$this->query_list['having'] = $having;
return $this;
}
/**
*----------------------------------------------------------
* 查询
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function select()
{
$select_sql = 'select ';
$fields = isset($this->query_list['fields'])?$this->query_list['fields']:'*';
$select_sql .= $fields;
$select_sql .= ' from `'.$this->query_list['table'].'` ';
isset($this->query_list['join'])?($select_sql.=$this->query_list['join']):'';
isset($this->query_list['where'])?($select_sql.=' where '.$this->query_list['where']):'';
isset($this->query_list['group'])?($select_sql.=' group by'.$this->query_list['group']):'';
isset($this->query_list['having'])?($select_sql.=' mysql having '.$this->query_list['having']):'';
isset($this->query_list['order'])?($select_sql.=' order by '.$this->query_list['order']):'';
isset($this->query_list['limit'])?($select_sql.=' '.$this->query_list['limit']):'';
return $this->query($select_sql);
}
/**
*----------------------------------------------------------
* 增加
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function add()
{
$add_sql = 'insert into `'.$this->query_list['table'].'` (';
$data = $this->query_list['data'];
$value = $field = '';
foreach($data as $k=>$v)
{
$field .= '`'.$k.'`,';
if(is_numeric($v))
$value .= $v.',';
else
$value .= '\''.$v.'\',';
}
$add_sql .= rtrim($field,',').') values ('.rtrim($value,',').')';
return $this->execute($add_sql);
}
/**
*----------------------------------------------------------
* 删除
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function delete()
{
$del_sql = 'delete from `'.$this->query_list['table'].'` where '.$this->query_list['where'];
if(isset($this->query_list['order']))
$del_sql .= 'order by '.$this->query_list['order'];
if(isset($this->query_list['limit']))
$del_sql .= ' '.$this->query_list['limit'];
return $this->execute($del_sql);
}
/**
*----------------------------------------------------------
* 更新
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function update()
{
$update_sql = 'update `'.$this->query_list['table'].'` set ';
$data = $this->query_list['data'];
foreach($data as $k=>$v)
{
if(is_numeric($v))
$update_sql .= '`'.$k.'` ='.$v.',';
else
$update_sql .= '`'.$k.'` =\''.$v.'\',';
}
$update_sql = rtrim($update_sql,',');
if(isset($this->query_list['where']))
$update_sql .= ' where '.$this->query_list['where'];
if(isset($this->query_list['order']))
$update_sql .= ' order by '.$this->query_list['order'];
if(isset($this->query_list['limit']))
$update_sql .= ' '.$this->query_list['limit'];
return $this->execute($update_sql);
}
/**
*----------------------------------------------------------
* 执行查询 返回数据集
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param string $sql sql指令
*/
public function query($sql)
{
if ( !$this->conn )
return false;
$this->queryStr = $sql;
//释放前次的查询结果
if ( $this->queryID )
{
$this->free();
}
$this->query_start_time = microtime(true);
$this->queryID = mysql_query($sql, $this->conn);
$this->query_count**;
if ( false === $this->queryID )
{
$this->error();
return false;
} else
{
$this->numRows = mysql_num_rows($this->queryID);
return $this->getAll();
}
}
/**
*----------------------------------------------------------
* 执行语句
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param string $sql sql指令
*----------------------------------------------------------
*/
public function execute($sql)
{
if ( !$this->conn )
return false;
$this->queryStr = $sql;
//释放前次的查询结果
if ( $this->queryID )
{
$this->free();
}
$this->query_start_time = microtime(true);
$result = mysql_query($sql, $this->conn) ;
$this->query_count**;
if ( false === $result)
{
$this->error();
return false;
}
else
{
$this->numRows = mysql_affected_rows($this->conn);
return $this->numRows;
}
}
/**
*----------------------------------------------------------
* 获得所有的查询数据
*----------------------------------------------------------
* @access private
*----------------------------------------------------------
* @return array
*/
private function getAll()
{
//返回数据集
$result = array();
if($this->numRows >0)
{
while($row = mysql_fetch_assoc($this->queryID))
{
$result[] = $row;
}
mysql_data_seek($this->queryID,0);
}
return $result;
}
/**
*----------------------------------------------------------
* 取得数据表的字段信息
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
*/
public function getFields($tableName)
{
$result = $this->query('SHOW COLUMNS FROM `'.$tableName.'`');
$info = array();
if($result)
{
foreach ($result as $key => $val)
{
$info[$val['Field']] = array(
'name' => $val['Field'],
'type' => $val['Type'],
'notnull' => (bool) ($val['Null'] === ''), // not null is empty, null is yes
'default' => $val['Default'],
'primary' => (strtolower($val['Key']) == 'pri'),
'autoinc' => (strtolower($val['Extra']) == 'auto_increment'),
);
}
}
return $info;
}
/**
*----------------------------------------------------------
* 取得数据库的表信息
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
*/
public function getTables($dbName='')
{
if(!empty($dbName))
{
$sql = 'SHOW TABLES FROM '.$dbName;
}
else
{
$sql = 'SHOW TABLES ';
}
$result = $this->query($sql);
$info = array();
foreach ($result as $key => $val)
{
$info[$key] = current($val);
}
return $info;
}
/**
*----------------------------------------------------------
* 最后次操作的ID
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function last_insert_id()
{
return mysql_insert_id($this->conn);
}
/**
* 执行一条带有结果集计数的
*/
public function count($sql)
{
return $this->execute($sql);
}
/**
*----------------------------------------------------------
* 启动事务
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @return void
*----------------------------------------------------------
*/
public function startTrans()
{
if ($this->transTimes == 0)
{
mysql_query('START TRANSACTION', $this->conn);
}
$this->transTimes**;
return ;
}
/**
*----------------------------------------------------------
* 提交事务
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @return boolen
*----------------------------------------------------------
*/
public function commit()
{
if ($this->transTimes > 0)
{
$result = mysql_query('COMMIT', $this->conn);
$this->transTimes = 0;
if(!$result)
{
throw new Exception($this->error());
}
}
return true;
}
/**
*----------------------------------------------------------
* 事务回滚
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @return boolen
*----------------------------------------------------------
*/
public function rollback()
{
if ($this->transTimes > 0)
{
$result = mysql_query('ROLLBACK', $this->conn);
$this->transTimes = 0;
if(!$result)
{
throw new Exception($this->error());
}
}
return true;
}
/**
*----------------------------------------------------------
* 错误信息
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
public function error()
{
$this->error = mysql_error($this->conn);
if('' != $this->queryStr)
{
$this->error .= "\n [ SQL语句 ] : ".$this->queryStr;
}
return $this->error;
}
/**
*----------------------------------------------------------
* 释放查询结果
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
*/
public function free()
{
@mysql_free_result($this->queryID);
$this->queryID = 0;
$this->query_list = null;
}
/**
*----------------------------------------------------------
* 关闭连接
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
* @param
*----------------------------------------------------------
*/
function close()
{
if ($this->conn && !mysql_close($this->conn))
{
throw new Exception($this->error());
}
$this->conn = 0;
$this->query_count = 0;
}
/**
*----------------------------------------------------------
* 析构方法
*----------------------------------------------------------
* @access public
*----------------------------------------------------------
*/
function __destruct()
{
$this->close();
}
}