这个主要是为了在预处理的操作上上偷懒，是当初刚学完php和mysql后写的(很臃肿，数组检查什么的完全可以不要报个错就行了，事务建议照常开启,勤提交)
一、 简要预览与说明
说明:
- 1.当开启事务,且未主动提交或回滚时,将自动回滚事务,关闭连接,并输出一条错误提示
- 2.当使用外部连接标识时,无检测是否在事务中,因此对象销毁时,不做出自动回滚,不关闭连接
- 3.因,此类多用在自动收集表单上,故经常用到筛除杂参
- 4.转换连接标识时,如果仍处在上一事务中,将不能转换,且输出一条错误提示
实例化对象
- $MM = new mysqlModule( 'localhost','root','root','ab' ); //传入连接所需参数
预处理相关
- $MM -> search( sql语句,参数数组,要查询的字段(可选,缺省为全字段),绑定参数类型(可选,缺省为s字符串类型) );
传入的参数,与要绑定的占位符以及参数类型需要依次对应
- $MM -> update( 表名,条件,参数数组,参数类型(可选,缺省为s字符串类型) );
- $MM -> insert( 表名,参数数组,参数类型(可选,缺省为s字符串类型) );
- $MM -> row; //返回上一次执行语句影响的行数
其他
- $MM -> exclude( 保留原型,要检测的参数数组 ); //筛除杂参
- $MM -> queryField( 表名,要排除的字段名(可选,缺省为查询全部),是否返回数组(缺省为返回字符串) )
- $MM -> copyTable( 需要copy的字段,来源表,目标表 );
- $MM -> useConn( 连接标识,数据库名 ); //使用外部连接标识
- $MM -> wConn( $host,$username,$password,$dbname ); //转换连接标识
- $MM -> begin_transaction(); //开启事务
- $MM -> commit(); //提交事务
- $MM -> rollback(); //事务回滚 , 对象销毁时,执行检测是否仍在事务中,如果是则自动回滚并关闭连接标识
- $MM -> query( sql语句,是否返回mysqli_result对象(可选)) ); // 查询语句,缺省为返回结果集,第二参数为true时,返回mysqli_result对象
- $MM -> idu( sql语句 ); //执行增删改语句,返回ture或false;
- $MM -> arrTest( array ); //检测数组维度
- $MM -> associativeTest( array ); //检测数组当前维度是否是纯关联数组;
二、 建表
在数据库 ab 中建 test表
CREATE TABLE `ab`.`test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NULL, `age` int(11) NULL, `sex` varchar(255) NULL, `height` double NULL, `weight` double NULL, `address` varchar(255) NULL, PRIMARY KEY (`id`) ); INSERT INTO `ab`.`test`(`name`, `age`, `sex`, `height`, `weight`, `address`) VALUES ('小明', 16, '男', 170, 72, '山东'); INSERT INTO `ab`.`test`(`name`, `age`, `sex`, `height`, `weight`, `address`) VALUES ('大明', 16, '男', 170, 72, '山东');
三、 主要函数介绍
实例化类( 本类的读写传参,默认参数的键值对应表的字段名才能成功 )
- $MM = new mysqlModule( 'localhost','root','root','ab' ); //传入连接所需参数
①预处理查询
- $MM -> search( sql语句,参数数组,要查询的字段(可选,缺省为全字段),绑定参数类型(可选,缺省为s字符串类型) );
传入的参数,与要绑定的占位符以及参数类型需要依次对应
模式一、 查询所有字段
$sql = "select * from `test` where `age`=? and `name` like ? "; $arr = array( 16,'%大%' ); $res = $MM->search( $sql,$arr );
返回:
模式二、 按照指定参数类型查询指定字段
$field = "id,name,address"; //指定要查询的字段 $sql = "select $field from `test` where `age`=? and `name` like ? "; $arr = array( 16,'%大%' ); $res = $MM->search( $sql,$arr,$field,'is' ); // i -> 整型 , d->浮点型 , s->字符型
返回:
②预处理更新
- $MM -> update( 表名,条件,参数数组,参数类型(可选,缺省为s字符串类型) );
原始数据:
$arr = array( 'sex'=>"女",'height'=>165,'weight'=>58 ); $res = $MM->update( 'test','`id`=1 and `name` like "%小%"',$arr,'sii' ); //成功返回true,失败返回false $row = $MM->row; //返回受影响的行
修改后:
③添加数据
-
$MM
->
insert(
表名,参数数组,参数类型(可选,缺省为s字符串类型)
);
模式一:
/* 模式一 */ $arr = array( 'name'=>"小丽", 'age'=>15, 'sex'=>'女' ); $res = $MM->insert( 'test',$arr ); //成功返回true,失败返回false $row = $MM->row; //返回受影响行数
执行后:
模式二:
/*模式二*/ $arr = array( 'name'=>array( "小芳", "大芳" ), 'age' =>array( "14", "16", ), 'sex'=>array( '男', '未知' ) ); $res = $MM->insert( 'test',$arr ); //成功返回true,失败返回false $row = $MM->row; //返回受影响行数
执行后:
④筛除杂参
-
$MM
->
exclude(
保留原型,要检测的参数数组
);
$test = array( 'name'=>'小华', 'age'=>15, 'tel'=>13456789999, 'height'=>171 ); $retain = 'name,age,sex'; $retain2 = array( 'name','age','sex' ); $test = $MM->exclude( $retain,$test ); // 只要不是指定的retain里的参数都将剔除
筛除后:
⑤查询表字段
- $MM -> queryField( 表名,要排除的字段名(可选,缺省为查询全部),是否返回数组(缺省为返回字符串) );
//查询test表的字段,但不查询id $queryField = $MM->queryField( 'test','id' ); var_dump( $queryField );
返回结果:
⑥复制表信息
- $MM -> copyTable( 需要copy的字段,来源表,目标表 );
// $queryField 为④中查询的结果包括 ( name,age,sex,height,weight,address ); $copyResult = $MM->copyTable( $queryField,'test','test' ); //成功返回true,失败返回false
执行成功后:
id为1-5的数据,被copy至test表
四、类本体
class mysqlModule{ 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" ); } ////////////////// 事务转移 function begin_transaction(){ $this->transactions = true; return $this->conn->begin_transaction(); } function commit(){ $this->transactions = false; return $this->conn->commit(); } function rollback(){ $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; } } ////////////////// 预处理删除 function delete(){ // 很少用到,暂时没写 } ///////////////// 筛除杂参 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<$num;$i++){ $result[$i]['sql'] = chop($sql,","); $result[$i]['param'][0]=$str; for ($j=0;$j<$count;$j++){ $name = array_keys($array)[$j]; $result[$i]['param'][$j+1]=$array[$name][$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<$num;$i++){ $result[$i]['sql'] = chop($sql,","); $result[$i]['param'][0]=$types; for ($j=0;$j<$count;$j++){ $name = array_keys($array)[$j]; $result[$i]['param'][$j+1]=$array[$name][$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(); } } }