我正在尝试使用prepare语句创建最佳的全局选择查询,除了我收到警告之外,一切正常:
警告:mysqli_stmt_bind_result():绑定变量数与预准备语句中的字段数不匹配
独特的全局选择功能
function querysp($selectquery, $type_bind, $colval) { global $db; $stmt = $db->prepare($selectquery); $stmt->bind_param($type_bind,$colval); $stmt->execute(); mysqli_stmt_bind_result($stmt, $colval); $arr = array(); if ($stmt) { while ($result = mysqli_stmt_fetch($stmt)) { array_push($arr, $result); } } return $arr; }
示例使用:
$select = "SELECT * from advertising WHERE status = ?";$status = 1;$advertising = querysp($select, 'i', $status);foreach ($advertising as $ad) { $banner[] = $ad['banner']; $bannercode[] = $ad['bannercode']; $location[] = $ad['location']; $status = $ad['status'];}
我错过了什么?很抱歉没有完全准备好staments,也在SO和谷歌检查,但无法解决这个问题.
编辑2
我已经更改了选择查询,因为我将绑定参数的类型从b(我认为这意味着布尔值)更改为i,但仍然收到错误.
编辑3 – 当前版本 – 仍然得到相同的错误:
$select = "SELECT banner, bannercode, location, status from advertising WHERE status = ?";$status = 1;$advertising = querysp($select, 'i', $status);foreach ($advertising as $ad) { $banner[] = $ad['banner']; $bannercode[] = $ad['bannercode']; $location[] = $ad['location']; $status = $ad['status'];}
和功能
function querysp($selectquery, $type_bind, $colval) { global $db; $stmt = $db->prepare($selectquery); $stmt->bind_param($type_bind,$colval); $stmt->execute(); $stmt->bind_result($result); $arr = array(); while($stmt->fetch()) { $arr[] = $result; } $stmt->close(); return $arr;}
解决方法:
花了一些时间,但这里是我准备好的陈述的版本,它是一个相当的墙,但它几乎捕获了可能产生的大多数错误.我试着在这里和那里添加一些文档来解释会发生什么.只需逐步阅读,您就可以理解会发生什么.如果有任何不清楚的地方提问.
要使用下面发布的类,请执行此操作.
$query = "SELECT ? FROM ?"; // can be any query$params = array($param1, $param2); //must equal to ammount of "?" in query.//an error will occur if $param1 or 2 is not of the type string, int, //bool or double, it can however be a double like this 2,1 instead of 2.1$db = new databaseHandler();$result = $db->runQuery($query, $params);//or for short runQuery("SELECT * FROM *" , array());if($result !== false){ while($row = mysqli_fetch_array($result){ $column1 = $row['columnname']; //just do with the result what you want. }}else{ echo "error occured";}
这是能够处理数据库交互的类.不要以你想要的方式设置连接.您可以对此运行所有类型的查询.
class databaseHandler{private $x = ""; //required//$query = the query, $params = array with params can be empty.public function runQuery($query, Array $params){ if($this->checkparams($params, $query)){ //starts and returns the database connection $con = startConnection(); //<--- CHANGE THIS SO IT WORKS FOR YOU if($stmt = $con->prepare($query)){ //obtains type of params if there are any. if(count($params) < 0){ $type = ""; $i=0; foreach($params as $par){ $par = $this->checktype($par); $params[$i] = $par; $type = $this->setType($type); if($type === false){ echo "type error occured" return false; } $i ; } //sets $type on the first spot of the array. array_unshift($params, $type) //binds the params call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params)); } $stmt->execute(); $result - $stmt->get_result(); stmt->close(); return $result; // return the result of the query. }else{ echo "error occured, bad connection"; return false; }else{ echo "params dont match prepared statement"; return false; }}//checks if params are equal to prepared statement requirements.checkparams($params, $query){ //counts occurences of ? in query. $count = substr_count($q, "?"); $i = count($params); if($count == $i){ return true; }else{ return false; }}//Checks the type of a parampublic function checktype($par){ $this->x = gettype($par); if($this->x == "string"){ $npar = str_replace(",", ".", $par); if(is_numeric($npar)){ $this->x = "integer"; if(strpos($npar, ".")){ $this->x="double"; return $npar; } } } return $par;}//sets/adds to the type.public function setType($type){ //$this->x from checktype; switch($this->x){ case "string": $type = $type."s"; break; case "integer": $type = $type."i"; break; case "double": $type = $type."d"; break; case "boolean": $type = $type."b"; break; case "NULL": $type = $type."s"; default: return false; } return $type; }//This function exist to resolve a few version errors in php.//not sure what it does exactly, but it resolved some errors I had in the past. function refValues($arr){ if(strnatcmp(phpversion(),'5.3') >= 0){ $refs = array(); foreach($arr as $key => $value) $refs[$key] = &$arr[$key]; return $refs; } return $arr;}}}
所以这里发生的是一组执行查询的检查,如果出现任何问题,如果没有出错则返回false,即使结果为空,也会返回查询结果.也有可能不在课堂上完成所有这些,尽管这会使$x全局化.我认为最好修改某些东西,使它们最适合您的应用程序.像错误处理/消息变量名称等
此代码唯一不能保护您的是查询中的错误.
编辑—-我发现这个代码没有防止的东西,插入NULL值.我更改了此代码以防止插入NULL值,它们将作为类型字符串插入.数据库将看到其NULL并将其插入为NULL值.
只是不要尝试插入对象或空值,因为这无论如何都是无用的.
来源:https://www.icode9.com/content-1-426651.html联系客服