PHP:预处理语句[参数]生成器
该方法为万年原创https://www.cnblogs.com/wannian/
写该方法的起因:
因为经常处理字段相当多的表(有时四五十个字段),每次都要一个一个的添加变量,实在难受,因此写了这个方法以便偷懒;
为了便于理解,该方法被封装到一个类中。注:该方法适用于mysqli连接(俺是初学者&&自学没查到有其他简便的法子,也没有用过PDO,所以还不知道能不能适用PDO)
使用说明:(请查看应用举例,因为还需用到call_user_func_array()函数)
*这是必需的* 表单的name名称必须与mysql数据库的字段名相同(请注意数据库表字段的类型,以及是否为非空项等)
sql_param_produce($array,$types)方法
传入:数组(必需),自定义参数格式(可选=>可能的值为i整型,d双精度,s表示字符串,b BLOB)
目前只支持的传入值: 一维关联数组 与 二维数组: (“行为数字索引,列为关联索引” 或 “行为关联索引,列为数字索引”);
返回值
当传入参数为 一维关联数组 : 返回一维数组 array( ‘sql’=val,’param’=arr);
当传入参数为 二位数组,且符合上述两种格式 : 返回二维数组 array(0=>[‘sql’=>value,’param’=>arr],1=>[‘sql’=>value,’param’=>arr]);
param_values($arr)方法
将传给bind_param方法的数组需要转换为引用传递
适用语句:
"select * from `table` where id=?,name=?,age=?,height=?"; "insert into `table` set id=?,name=?,age=?,height=?"; "update `table` set id=?,name=?,age=?,height=? where id =?"; "delete from `table` where id=?,name=?,age=?,height=?";
https://www.cnblogs.com/wannian/p/9309506.html
应用举例:
$bookInfoTwo = array( "bookName"=>[ 0=>"MySQL排错指南", 1=>"高性能PHP应用开发", 2=>"PHP和MySQL Web开发", ], "bookPrice"=>[ 0=>"38.6", 1=>"148.5", 2=>"98" ] );// 此数组模拟获取的表单提内容 $test = new Collect(); $arr = $test->sql_param_produce($bookInfoTwo,'sd'); //传入模拟表单元素并返回处理后的数组 $conn = new mysqli('127.0.0.1','root','root','test'); foreach ($arr as $key=>$value){ $sql = "insert into `booktable` set {$value['sql']}";// 输出格式为 "insert into `booktable` set `bookName`=?,`bookPrice`=?" $stmt = $conn->prepare($sql); call_user_func_array (array($stmt,'bind_param'),$test->param_values($value['param']));// 传给bind_param方法的数组需要转换为引用传递 $stmt->execute(); } /* * 此处模拟表单方式: * <input name="bookName[0]" type="text"><input name="bookName[1]" type="text"><input name="bookName[2]" type="text"> * <input name="bookPrice[0]" type="text"><input name="bookPrice[1]" type="text"><input name="bookPrice[2]" type="text"> * sql_param_produce返回的数组格式表现为: * [ * 0=>[ * 'sql'=>"`bookName`=?,`bookPrice`=?", * 'param'=>[0=>'sd',1=>'MySQL排错指南',2=>'38.6'] * ], * 1=>[ * 'sql'=>"`bookName`=?,`bookPrice`=?", * 'param'=>[0=>'sd',1=>'高性能PHP应用开发',2=>'148.5'] * ], * 2=>[ * 'sql'=>"`bookName`=?,`bookPrice`=?", * 'param'=>[0=>'sd',1=>'PHP和MySQL Web开发',2=>'98'] * ] * ] * */
https://www.cnblogs.com/wannian/p/9309506.html
执行完毕,数据成功写入:
封装类:
class Collect{ function sql_param_produce($array,$types=''){ $result =array(); $preg = "/^[idsb]*$/"; if (self::is_assoc($array) ==='isnotarray'){ throw new Exception("this value is not an array"); } else if (self::is_assoc($array)===true){ if(self::is_arrays($array)==false){ throw new Exception("Does not support one-dimensional numeric arrays"); } else{ if(self::is_assoc($array[0])==true){ throw new Exception("Does not support two-dimensional numeric arrays"); }else{ if($types!=''){ if(preg_match("/^[idsb]*$/",$types)==1 && strlen($types)==count($array[0])){ foreach ($array as $key=>$value){ $sql='';$param=array($types);$num =1; foreach ($value as $k=>$v){ $param[$num]=$v; $num++; $sql .= '`'.$k.'`=?,'; } $result[$key]['sql']=chop($sql,","); $result[$key]['param']=$param; } }else{ throw new Exception("Invalid value for parameter 'types'"); } }else{ for($i=0;$i<count($array);$i++){ foreach ($array as $key=>$value){ $sql='';$str='';$str = str_pad($str,count($value),"s");$param=array($str);$num =1; foreach ($value as $k=>$v){ $param[$num]=$v; $num++; $sql .= '`'.$k.'`=?,'; } $result[$key]['sql']=chop($sql,","); $result[$key]['param']=$param; } } } return $result; } } } else{ if(self::is_arrays($array)==false){ if($types!=''){ if(preg_match("/^[idsb]*$/",$types)==1 && strlen($types)==count($array)){ $sql ="";$num =1;$param=array($types); foreach ($array as $key=>$value){ $sql .= '`'.$key.'`=?,'; $param[$num] = $value; $num++; } $result['sql']=chop($sql,","); $result['param']=$param; }else{ throw new Exception("Invalid value for parameter 'types'"); } }else{ $sql ="";$num =1;$str='';$str = str_pad($str,count($array),"s");$param=array($str); foreach ($array as $key=>$value){ $sql .= '`'.$key.'`=?,'; $param[$num] = $value; $num++; } $result['sql']=chop($sql,","); $result['param']=$param; } return $result; }else{ if(self::is_assoc($array[array_keys($array)[0]])==true){ if($types!=''){ 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'"); } }else{ $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]; } } } return $result; }else{ throw new Exception("multidimensional associative arrays are not supported"); } } } } protected function is_assoc($array){ $index = 0; if(is_array($array)===false) return "isnotarray"; foreach (array_keys($array) as $key) { if ($index++ != $key) return false; } return true; } protected function is_arrays($array){ if(count($array) == count($array, 1)){ return false; }else{ return true; } } function param_values($arr){ if (strnatcmp(phpversion(),'5.3') >= 0) { $values = array(); foreach($arr as $key => $value) { $values[$key] = &$arr[$key]; } return $values; } } }
此外还支持传入参数的格式举例:
$userInfo = array( 'name'=>"万年", 'age'=>"18", "blogs"=>"https://www.cnblogs.com/wannian/" ); // 这是合法的 $bookInfoOne = array( 0=>[ 'bookName'=>"MySQL排错指南", 'bookPrice'=>"38.6" ], 1=>[ 'bookName'=>"高性能PHP应用开发", 'bookPrice'=>"148.5" ] ); // 这是合法的
非法参数举例:
$arr = [ 0=>[ 0=>"李四", 1=>"王五", ], 1=>[ 0=>"18", 1=>"19", ] ]; // 这是无效的,会报出 "Does not support two-dimensional numeric arrays" 错误 $arr2 = [ 'human'=>[ 'name'=>"张三", 'love'=>"赵六" ], "book"=>[ 'bookname'=>"高性能PHP应用开发", 'bookprice'=>"148.5" ] ]; // 这是无效的,会报出 "multidimensional associative arrays are not supported" 错误 $arr3 =[ 0=>"李四", 1=>"18" ]; // 这是无效的,会报出 "Does not support one-dimensional numeric arrays" 错误 // 此外sql_param_produce 的types参数 必须是符合 且 与字段数相等的, 否则报出 " Invalid value for parameter 'types' " 错误
版权声明：本文为wannian原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。