mysql操作类 php_PHP:自己写的mysql操作类

classmysqlModule{private $conn;private $dbname;private $transactions = false; //是否在事务中

public $row;private $c = true; //是否使用外部链接

/ 使用外部标识

function useConn( $conn,$dbname){$this->dbname = $dbname;$this->c =fasle;$this->conn = $conn;$this->conn->query( "set names utf8");

}//初始化连接标识

function __construct( $host,$username,$pwd,$dbname){$this->dbname = $dbname;$this->conn = new mysqli( $host,$username,$password,$this->dbname );$this->conn->query( "set names utf8");

}//转换标识

function wConn( $host,$username,$password,$dbnane){if( $transactions){throw new Exception("Is still in the previous transaction.", 1);return false;

}$this->conn = new mysqli( $host,$username,$password,$dbnane);$this->conn->query( "set names utf8");

}// 事务转移

functionbegin_transaction(){$this->transactions = true;return $this->conn->begin_transaction();

}functioncommit(){$this->transactions = false;return $this->conn->commit();

}functionrollback(){$this->transactions = false;return $this->conn->rollback();

}// 查询字段

function queryField( $table_name,$exclude_field=false,$is_arr=false){$exclude_field = $this->fieldCorrect( $exclude_field,false);$sql = "select `column_name` from information_schema.columns where table_name='{$table_name}' and TABLE_SCHEMA='{$this->dbname}'";$res = $this->conn->query( $sql);$result = array();if( $res){while( $r = $res->fetch_assoc() ){$result[] = $r['column_name'];

}

}else{return false;

}if( is_array($exclude_field) ){foreach ( $exclude_field as $k=>$v){$v = str_replace( '`','',$v);if( in_array( $v,$result) ){array_splice( $result,array_search($v,$result),1);

}

}

}elseif( is_string($exclude_field) && !is_null( $exclude_field) ){$exclude_field = str_replace( '`','',$exclude_field);$exclude_field = explode( ',',$exclude_field);foreach ( $exclude_field as $k=>$v){if( in_array( $v,$result) ){array_splice( $result,array_search($v,$result),1);

}

}

}else{return false;

}if( $is_arr){return $result;

}else{return implode( ',',$result);

}

}// 拷贝表信息

function copyTable( $field,$copy,$target){$field = $this->fieldCorrect( $field);if( is_array( $field ) )$field = implode( ',',$field);$sql = "insert into {$target} ({$field}) select {$field} from {$copy}";$res = $this->conn->query( $sql);//return $sql;

if( $res){return true;

}return false;

}// 查询

function query( $sql,$a=false){$res = $this->conn->query( $sql);if( $a){return $res;

}if( $res){$this->row = mysqli_affected_rows( $this->conn );$returnArr = array();while( $r = $res->fetch_assoc() ){$returnArr[] = $r;

}return $returnArr;

}else{return false;

}

}// 增删改

function idu( $sql){if ( $this->conn->query( $sql) ){$this->row = mysqli_affected_rows( $this->conn );return true;

}else{return false;

}

}// 预处理查询

function search( $sql,$parameter,$field=false,$types=false){$stmt = $this->conn->prepare( $sql);if( $stmt){if( $field){$param = $this->parameters_dispose( $parameter,$types,true);call_user_func_array ( array($stmt,'bind_param'),$this->param_values($param['param']) );$info_arr = $this->bind_result_return_search($stmt,$field);return $info_arr;

}else{$a = stripos( $sql,'from');$b = stripos( $sql,'where');$tableName = substr( $sql,$a+4,$b-$a-4);$tableName = str_replace( '`','',$tableName);$tableName = str_replace( ' ','',$tableName);$field = $this->queryField( $tableName);$param = $this->parameters_dispose( $parameter,$types,true);call_user_func_array ( array($stmt,'bind_param'),$this->param_values($param['param']) );$info_arr = $this->bind_result_return_search($stmt,$field);return $info_arr;

}

}else{return false;

}

}// 预处理修改

function update( $tableName,$where,$parameter,$types=false){$parameter = self::parameters_dispose($parameter,$types);$sql = " update `{$tableName}` set {$parameter['sql']} where {$where}";if( $parameter['type']===0){$stmt = $this->conn->prepare($sql);call_user_func_array ( array($stmt,'bind_param'),$this->param_values($parameter['param']) );if( $stmt->execute() ){$this->row = mysqli_affected_rows( $this->conn );return true;

}return false;

}else{return false;

}

}// 预处理增加

function insert( $table,$parameter,$types=false){$parameters = self::parameters_dispose($parameter,$types);$t = $parameters['type'];unset($parameters['type']);$affected_rows = 0;switch ($t){case 0: //一维关联数组

if(isset($parameters['sql']))$sql = "insert into `{$table}` set ".$parameters['sql'];$stmt = $this->conn->prepare($sql);call_user_func_array (array($stmt,'bind_param'),$this->param_values($parameters['param']));if($stmt->execute()){$this->row = mysqli_affected_rows( $this->conn );return true;

}break;case 1: //二维 1关联 2 数字

if(isset($parameters[0]['sql']))$sql = "insert into `{$table}` set ".$parameters[0]['sql'];foreach ($parameters as $v){$stmt = $this->conn->prepare($sql);call_user_func_array (array($stmt,'bind_param'),$this->param_values($v['param']));if($stmt->execute()){$affected_rows += mysqli_affected_rows( $this->conn );

}else{return false;

}

}$this->row = $affected_rows;return true;break;default:

return false;break;

}

}// 预处理删除

functiondelete(){//很少用到,暂时没写

}/ 筛除杂参

function exclude( $retain,$testArr){if( !is_array( $testArr) ){throw new Exception("parameter 2 is not array", 1);return false;

}if( is_string( $retain) ){$retain = str_replace( '`','',$retain);$retain = str_replace( ' ','',$retain);$retain = explode( ',',$retain);

}elseif ( is_array( $retain) ){

}else{return false;

}foreach ($testArr as $kk => $vv) {$kk = str_replace( '`','',$kk);$kk = str_replace( ' ','',$kk);$a = true;foreach ($retain as $k => $v) {$retain = str_replace( ' ','',$retain);if( $v==$kk){$a = false;break;

}

}if( $a )unset( $testArr[$kk] );

}return $testArr;

}///数组维度检测

function arrTest($array){ //维度检测

if(!is_array($array)) return false;$max_depth = 1;foreach ($array as $value) {if (is_array($value)) {$depth = $this->arrTest($value) + 1;$max_depth = max($max_depth,$depth);

}

}return $max_depth;

}//检测当前维度是否是关联数组

function associativeTest($array =array()){ //关联数组与否检测

if(empty($array)) return false;foreach ($array as $k=>$v){if( is_numeric( $k) ){return false;

}

}return true;

}// 字段纠正 传入字段参数,可以为数组或字符串 ,是否添加删除, true为添加,false删除

private function fieldCorrect( $field,$is_add=true){if( $is_add){if( is_array( $field) ){foreach ( $field as $k=>$v){if( !strstr( $v,'`') )$field[$k] = '`'.$v.'`';

}

}else{$field = explode(',',$field);foreach ( $field as $k=>$v){if( !strstr( $v,'`') )$field[$k] = '`'.$v.'`';

}$field = implode( ',',$field);

}

}else{if( is_array( $field) ){foreach ( $field as $k=>$v){$field[$k] = str_replace( '`','',$v);

}

}else{$field = str_replace( '`','',$field);

}

}return $field;

}// 生成参数类型

private function mkTypes( $arr,$type=false){$sql = "";$param = array();if( $type){$param[] = $type;

}else{$param[] = str_pad( '',count($arr),"s");

}foreach ( $arr as $k=>$v){$sql .= '`'.$k.'`=?,';$param[] = $v;

}return array('sql'=>chop($sql,","),

'param'=>$param);

}//生成bind_param 参数

private function parameters_dispose($array,$types,$s=false){$dimension = $this->arrTest($array); //检测数组维度

switch ($dimension){//一维数组

case 1:

if( $s){if( $types==false){$result = $this->mkTypes( $array);

}else{if(preg_match("/^[idsb]*$/",$types)==1 && strlen($types)==count($array)){$result = $this->mkTypes( $array,$types);

}else{throw new Exception("Invalid value for parameter 'types'");

}

}$result['type'] =0;return $result;

}elseif( $this->associativeTest($array) ){if( $types==false){$result = $this->mkTypes( $array);

}else{if(preg_match("/^[idsb]*$/",$types)==1 && strlen($types)==count($array)){$result = $this->mkTypes( $array,$types);

}else{throw new Exception("Invalid value for parameter 'types'");

}

}$result['type'] =0;return $result;

}else{return false;

}break;//二维1关联2数字数组

case 2:

try{if($this->associativeTest($array)){if( $types==false){$sql="";$num =count($array[array_keys($array)[0]]);$str='';$str = str_pad($str,count($array),"s");foreach (array_keys($array) as $v){$sql .= '`'.$v.'`=?,';}$count = count($array);for ($i=0;$i

}

}

}else{if(preg_match("/^[idsb]*$/",$types)==1 && strlen($types)==count($array) ){$sql="";$num =count($array[array_keys($array)[0]]);foreach (array_keys($array) as $v){$sql .= '`'.$v.'`=?,';}$count = count($array);for ($i=0;$i

}

}

}else{throw new Exception("Invalid value for parameter 'types'");

}

}$result['type'] =1;return $result;

}else{throw new Exception("array parameter error");

}

}catch (Exception $e){throw new Exception("array parameter error");

}break;//其他情况

default:

throw new Exception("array parameter error");

}

}//绑定传参

private function param_values($arr){if (strnatcmp(phpversion(),'5.3') >= 0)

{$values = array();foreach($arr as $key => $value) {$values[$key] = &$arr[$key];

}return $values;

}

}//绑定结果集并返回数据

private function bind_result_return_search($stmt,$str){try{$str = preg_replace('/[` ]/', '', $str);//去掉 ` 和 空格

$str_arr = explode(',', $str); //分割原始字段名

// 制作变量数组

$str_new = "$" . $str;$str_new = str_replace(',', ',$', $str_new);$str_new_arr = explode(',', $str_new);//生成 绑定参数的字符串 php 代码并运行

$evalstr = '$stmt->bind_result(' . $str_new . ');';eval($evalstr);$stmt->execute(); //提交结果集

$this->row = mysqli_affected_rows( $this->conn );//数组赋值字符串生成,将字段名与变量绑定,并写入待执行字符串代码

$string = '';foreach ($str_arr as $k => $v) {$string .= "'" . $v . "'=>" . $str_new_arr[$k] . ",";

}$string = rtrim($string, ',');$arr_eavl_str = '$return_arr[] = $start_arr = array(' . $string . ');';//创建收集器, 循环运行赋值字符串代码

$return_arr = array();while ($stmt->fetch()) {eval($arr_eavl_str);

}//返回结果集

return $return_arr;

}catch(Exception $e){return false;

}

}//关闭连接标识

function__destruct(){if( $this->transactions && $this->c ){throw new Exception("Transaction is not closed, auto rollback!", 1);$this->conn->rollback();

}if( $this->conn && $this->c ) {$this->conn->close();

}

}

}