<?php
/**
* 功能： 数据库操作类 .
* 作者: 赵铭哲
* 日期: 2016-05-23
* 时间: 9:43
*/
namespace ZHDataBase;
use Exception as Exception;
class mysql {
#region ----声明公用变量----
public $db; //数据库连接对象
public $config; //数据库配置数据
public $server; //服务器连接地址
public $username; //数据库用户名
public $password; //数据库密码
public $sql; //sql语句变量
public $defaultDatabase;//默认设置的数据库
public $limitcount; //限制的数量
public $message = array(
'charset'=>'', //返回
'execsql'=>'', //执行的sql语句
'resmsg'=>'', //结果信息
'recordmsg'=>'', //记录信息
'affected_rows'=>0, //上次执行SQL所影响的行数
'errormsg'=>'', //错误信息
'errorcode'=>'', //错误代码
'errorline'=>'', //错误行
'errorfile'=>'' //错误文件
);
#endregion
#region ----声明公用常量----
const SELECT = "SELECT ";
const INSERT = "INSERT ";
const UPDATE = "UPDATE ";
const DELETE = "DELETE ";
const FROM = "FROM ";
const WHERE = " WHERE 1=1 ";
const INTO = "INTO ";
const SET = "SET ";
const VALUES = "VALUES ";
const ORDERBY = " ORDER BY ";
const GROUPBY = " GROUP BY ";
const HAVING = "HAVING ";
const LIMIT = " LIMIT ";
const JOIN = "JOIN ";
const ON = "ON ";
const _AND = "AND ";
const _OR = "OR ";
const _LIKE = "LIKE ";
const _IN = "IN ";
const _BETWEEN = "BETWEEN ";
#endregion
/**
* 构造函数
*/
public function __construct() {
//开发环境
$this->server = "localhost";
$this->port = "3306";
$this->username = "root";
$this->password = "123456";
}
/**
* 功能:析构函数
* 作者: 赵铭哲
* 日期: 2016-06-03
*/
public function _destruct(){
$this->server = '';
$this->username = '';
$this->password = '';
$this->port = '';
$this->defaultDatabase = '';
$this->dbDisconnect();
}
#region ----数据库连接----
/**
* 功能:连接数据库
* 作者: 赵铭哲
* 日期: 2016-05-28
* @return string
*/
public function dbConnect(){
try{
//连接mysql数据库
$this->db=mysql_connect($this->server.':'.$this->port,$this->username,$this->password);
$charset = mysql_client_encoding($this->db);
if($this->db == false){
$this->message['errormsg'] = "Connected failed " . mysqli_connect_error();;
}else{
$this->message['resmsg'] = "Connected successfully.";
}
$this->message['charset'] = $charset;
}catch (Exception $ex){
$this->message['errorcode'] = "Exception code:".$ex->getCode();
$this->message['errormsg'] = "Exception msg:".$ex->getMessage();
$this->message['errorline'] = "Exception line:".$ex->getLine();
$this->message['errorfile'] = "Exception file:".$ex->getFile();
}
return $this->message;
}
/**
* 功能:断开数据库连接
* 作者: 赵铭哲
* 日期: 2016-05-28
*/
public function dbDisconnect(){
mysql_close($this->db);
}
/**
* 功能:选择数据库
* 作者: 赵铭哲
* 日期: 2016-06-12
*/
public function dbSelectDataBase($databaseName){
try{
if(isset($databaseName)){
mysql_select_db($databaseName);
}else{
mysql_select_db($this->defaultDatabase);
}
$this->message['resmsg'] = "Select Database successfully.";
}catch (Exception $ex){
$this->message['errorcode'] = "Exception code:".$ex->getCode();
$this->message['errormsg'] = "Exception msg:".$ex->getMessage();
$this->message['errorline'] = "Exception line:".$ex->getLine();
$this->message['errorfile'] = "Exception file:".$ex->getFile();
}
return $this->message;
}
#endregion
#region ----数据库查询----
/**
* 功能:通用执行
* 作者: 赵铭哲
* 日期: 2016-06-12
* @param $sql 普通的SQL语句
* @return array 返回一个信息数组，方便查看执行后的结果
*/
public function dbExec($sql){
$this->dbConnect();
$this->dbSelectDataBase($this->defaultDatabase);
try{
$isQuery = mysql_query($sql);
if($isQuery){
$this->message['resmsg'] .= "Query Successful";
}else{
$this->message['resmsg'] .= "Query Failed";
}
$this->message['affected_rows'] = mysql_affected_rows();
}catch (Exception $ex){
$this->message['errorcode'] .= "Exception code:".$ex->getCode();
$this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
$this->message['errorline'] .= "Exception line:".$ex->getLine();
$this->message['errorfile'] .= "Exception file:".$ex->getFile();
}
$this->dbDisconnect();
return $this->message;
}
/**
* 功能:通用查询获取单条数据
* 作者: 赵铭哲
* 日期: 2016-06-03
* @param $sql 普通的SQL语句
* @return resource 返回数组格式的单条数据信息
*/
public function dbQuerySingle($sql){
$this->dbConnect();
$this->dbSelectDataBase($this->defaultDatabase);
try{
$obj = mysql_query($sql);
$row = mysql_fetch_array($obj, MYSQL_BOTH);
$this->message['affected_rows'] = mysql_affected_rows();
mysql_free_result($obj);
return $row;
}catch (Exception $ex){
$this->message['errorcode'] .= "Exception code:".$ex->getCode();
$this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
$this->message['errorline'] .= "Exception line:".$ex->getLine();
$this->message['errorfile'] .= "Exception file:".$ex->getFile();
}
$this->dbDisconnect();
}
/**
* 功能:获取多条数据
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $sql 普通的SQL语句
* @return array 返回多条数据的数组
*/
public function dbQueryMutiple($sql){
$this->dbConnect();
$this->dbSelectDataBase($this->defaultDatabase);
try{
$result = array();
$obj = mysql_query($sql);
while($row = mysql_fetch_array($obj, MYSQL_BOTH)){
array_push($result,$row);
}
$this->message['affected_rows'] = mysql_affected_rows();
mysql_free_result($obj);
return $result;
}catch (Exception $ex){
$this->message['errorcode'] .= "Exception code:".$ex->getCode();
$this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
$this->message['errorline'] .= "Exception line:".$ex->getLine();
$this->message['errorfile'] .= "Exception file:".$ex->getFile();
}
$this->dbDisconnect();
}
/**
* 功能:扩展查询功能
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $table 表名
* @param $cols 列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
* @param $where Where语句：一维数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
* @param $order OrderBy语句：字符串格式(id asc|id desc)
* @param $group GroupBy语句：字符串格式(id)
* @param $limit Limit语句：字符串格式(1|1,2)
* @return array
*/
public function dbQueryExt($table,$cols='*',$where=null,$order=null,$group=null,$limit=null){
$this->dbConnect();
$this->dbSelectDataBase($this->defaultDatabase);
try{
$result = array();
$strWhere = self::WHERE;
$strCols = '';
if(is_array($cols)){
foreach($cols as $kc => $vc){
$strCols .= $vc.',';
}
$strCols = substr($strCols,0,strlen($strCols)-1);
}else if(is_string($cols)){
$strCols = $cols;
}
$this->sql = self::SELECT.$strCols.' '.self::FROM.$table;
if(is_array($where)){
foreach($where as $kw=>$vw){
$strWhere .= " and ".$kw."='".$vw."'";
}
$strWhere = substr($strWhere,0,strlen($strWhere)-5);
}else{
$strWhere .= $where;
}
$this->sql .= $strWhere;
if(isset($order)){
$this->sql .= self::ORDERBY.$order;
}
if(isset($group)){
$this->sql .= self::GROUPBY.$group;
}
if(isset($limit)){
$this->sql .= self::LIMIT.$limit;
}
$this->message['execsql'] = $this->sql;
$obj = mysql_query($this->sql);
while($row = mysql_fetch_array($obj, MYSQL_BOTH)){
array_push($result,$row);
}
$this->message['affected_rows'] = mysql_affected_rows();
mysql_free_result($obj);
return $result;
}catch (Exception $ex){
$this->message['errorcode'] .= "Exception code:".$ex->getCode();
$this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
$this->message['errorline'] .= "Exception line:".$ex->getLine();
$this->message['errorfile'] .= "Exception file:".$ex->getFile();
}
$this->dbDisconnect();
}
/**
* 功能:扩展查询功能2
* 作者: 赵铭哲
* 日期: 2016-07-20
* @param $table 表名
* @param string $cols 列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
* @param null $where Where语句：
* 一维数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
* 二维数组格式(array(array(a1=>'1',a2=>'2'),array(a3=>'3',a4=>'4'))|array(array(1,2),array(3,4)))
* @return array
*/
public function dbQueryExtTwo($table,$cols='*',$where=null,$pager = array(),$sort = array()){
try{
$result = array();
$strWhere = self::WHERE;
$strCols = '';
if(is_array($cols)){
foreach($cols as $kc => $vc){
$strCols .= $vc.',';
}
$strCols = substr($strCols,0,strlen($strCols)-1);
}else if(is_string($cols)){
$strCols = $cols;
}
$this->sql = self::SELECT.$strCols.' '.self::FROM.$table;
if(is_array($where)){
$startIndex = 0;
foreach($where as $kw=>$vw){
if(is_array($vw)){
foreach($vw as $k => $v){
if($startIndex == 0){
$strWhere .= " and ".$k.$this->formatWhere($v);
}else{
$strWhere .= $k.$this->formatWhere($v)." and ";
}
}
if($startIndex > 0){
$strWhere = substr($strWhere,0,strlen($strWhere)-5);
}
$strWhere .= " or ";
}else{
$strWhere .= " and ".$kw."='".$vw."'";
}
$startIndex = $startIndex + 1;
}
$strWhere = substr($strWhere,0,strlen($strWhere)-5);
}else{
$strWhere .= $where;
}
//增加分页的功能
if (!empty($pager)) {
if (!isset($pager['page']) || $pager['page'] < 1) {
$pager['page'] = 1;
}
$offset = ($pager['page'] - 1) * $pager['page_size'];
$limit = $pager['page_size'];
$strWhere .= sprintf(' LIMIT %d,%d', $offset, $limit);
}
$this->sql .= $strWhere;
$this->message['execsql'] = $this->sql;
$result = $this->dbQueryMutiple($this->sql);
return $result;
}catch (Exception $ex){
$this->message['errorcode'] .= "Exception code:".$ex->getCode();
$this->message['errormsg'] .= "Exception msg:".$ex->getMessage();
$this->message['errorline'] .= "Exception line:".$ex->getLine();
$this->message['errorfile'] .= "Exception file:".$ex->getFile();
}
$this->dbDisconnect();
}
#endregion
#region ----数据库插入----
/**
* 功能:通用插入单条数据
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $table 表名
* @param $cols 列名：数组格式(array(1,2,3))|字符串格式(1,2,3)
* @param $values 值：数组格式(array(1,2,3))|字符串格式(1,2,3)
*/
public function dbInsertSingle($table,$cols,$values){
//判断$cols,$values是否是数组，是则自动组合，否则直接返回值
$strCols = '';
$strValues = '';
if(is_array($cols)){
foreach($cols as $kc => $vc){
$strCols .= $vc.',';
}
$strCols = substr($strCols,0,strlen($strCols)-1);
}else if(is_string($cols)){
$strCols = $cols;
}else{
$this->message['errmsg'] = "数据类型不合法";
}
if(is_array($values)){
foreach($values as $kv => $vv){
$strValues .= "'".$vv."',";
}
$strValues = substr($strValues,0,strlen($strValues)-1);
}else if(is_string($values)){
$strValues = $this->CombinationInsert($values);
}else{
$this->message['errmsg'] = "数据类型不合法";
}
$this->sql = self::INSERT.self::INTO.$table."(".$strCols.") ".self::VALUES."(".$strValues.")";
$this->dbExec($this->sql);
}
#endregion
#region ----数据库修改----
/**
* 功能:通用更新
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $table 表名
* @param $values 更新的数据：数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
* @Param $where 参数Where:数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
*/
public function dbUpdate($table,$values,$where=null){
$strValues = '';
$strWhere = '';
if(is_array($values)){
foreach($values as $k=>$v){
$strValues .= $k."='".$v."',";
}
$strValues = substr($strValues,0,strlen($strValues)-1);
}else{
$strValues = $strValues;
}
$this->sql = self::UPDATE.$table.' '.self::SET.$strValues;
if(is_array($where)){
foreach($where as $kw=>$vw){
$strWhere .= $kw."='".$vw."' and ";
}
$strWhere = substr($strWhere,0,strlen($strWhere)-5);
}else{
$strWhere = $where;
}
$this->sql .= self::WHERE.self::_AND.$strWhere;
$this->dbExec($this->sql);
}
#endregion
#region ----数据库删除----
/**
* 功能:通用删除
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $table 表名
* @param $where Where语句：数组格式(array(a=>'1',b=>'2'))|字符串格式(a='1',b='2')
*/
public function dbDelete($table,$where){
$strWhere = '';
$this->sql = self::DELETE.self::FROM.$table.self::WHERE;
if(is_array($where)){
foreach($where as $kw=>$vw){
$strWhere .= $kw."='".$vw."' and ";
}
$strWhere = substr($strWhere,0,strlen($strWhere)-5);
}else{
$strWhere = $where;
}
$this->sql .= self::_AND.$strWhere;
$this->dbExec($this->sql);
print_r($this->sql);
}
#endregion
#region ----格式化返回数据----
/**
* 功能:格式化数据
* 作者: 赵铭哲
* 日期: 2016-07-21
* @param $data 查询出来的结果集：数组形式
* @return array
*/
public function formatData($data){
return array(
'statistic' => count($data),
'data' => $data,
);
}
/**
* 功能:格式化Where子句
* 作者: 赵铭哲
* 日期: 2016-07-21
* @param $condition 数组格式(array('operation'=>'eq','value'=>''))|字符串格式(='')
* @return string
*/
public function formatWhere($condition){
if(is_array($condition)){
if($condition['operation'] == "lt"){
return "<'".$condition['value']."'";
}else if($condition['operation'] == "eq"){
return "='".$condition['value']."'";
}else if($condition['operation'] == "gt"){
return ">'".$condition['value']."'";
}else if($condition['operation'] == "le"){
return "<='".$condition['value']."'";
}else if($condition['operation'] == "ge"){
return ">='".$condition['value']."'";
}else if($condition['operation'] == "neq"){
return "<>'".$condition['value']."'";
}else if($condition['operation'] == "like"){
return "like'%".$condition['value']."%'";
}else if($condition['operation'] == "in"){
if(is_array($condition['value'])){
return " in(".implode(',',$condition['value']).")";
}else{
return " in('".$condition['value']."')";
}
}
}else {
return "='".$condition."'";
}
}
#endregion
/**
* 功能:转换编码格式
* 作者: 赵铭哲
* 日期: 2016-06-06
*/
function ConvertEncode($value,$old_Encode,$new_Encode){
header("content-Type: text/html; charset=Utf-8");
$result = mb_convert_encoding($value, $old_Encode, $new_Encode);
return $result;
}
/**
* 功能:组合传入的字符为数据库插入时候的值
* 作者: 赵铭哲
* 日期: 2016-06-14
* @param $value
* @return string
*/
function CombinationInsert($value){
$result = '';
if(strstr($value,',') > -1){
$valTemp = explode(',',$value);
foreach($valTemp as $k => $v){
$result .= "'".$v."',";
}
$result = substr($result,0,strlen($result)-1);
}else{
$this->message['resmsg'] = "在传入的参数中未找到相关字符";
}
return $result;
}
}