我正在嘗試使用prepare語(yǔ)句創(chuàng)建最佳的全局選擇查詢,除了我收到警告之外,一切正常：

警告：mysqli_stmt_bind_result()：綁定變量數(shù)與預(yù)準(zhǔn)備語(yǔ)句中的字段數(shù)不匹配

獨(dú)特的全局選擇功能

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'];
}

我錯(cuò)過(guò)了什么？很抱歉沒有完全準(zhǔn)備好staments,也在SO和谷歌檢查,但無(wú)法解決這個(gè)問(wèn)題.

編輯2

我已經(jīng)更改了選擇查詢,因?yàn)槲覍⒔壎▍?shù)的類型從b(我認(rèn)為這意味著布爾值)更改為i,但仍然收到錯(cuò)誤.

編輯3 – 當(dāng)前版本 – 仍然得到相同的錯(cuò)誤：

$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;
}

解決方法:

花了一些時(shí)間,但這里是我準(zhǔn)備好的陳述的版本,它是一個(gè)相當(dāng)?shù)膲?但它幾乎捕獲了可能產(chǎn)生的大多數(shù)錯(cuò)誤.我試著在這里和那里添加一些文檔來(lái)解釋會(huì)發(fā)生什么.只需逐步閱讀,您就可以理解會(huì)發(fā)生什么.如果有任何不清楚的地方提問(wèn).

要使用下面發(fā)布的類,請(qǐng)執(zhí)行此操作.

$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";
}

這是能夠處理數(shù)據(jù)庫(kù)交互的類.不要以你想要的方式設(shè)置連接.您可以對(duì)此運(yùn)行所有類型的查詢.

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 param
public 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;
}
}
}

所以這里發(fā)生的是一組執(zhí)行查詢的檢查,如果出現(xiàn)任何問(wèn)題,如果沒有出錯(cuò)則返回false,即使結(jié)果為空,也會(huì)返回查詢結(jié)果.也有可能不在課堂上完成所有這些,盡管這會(huì)使$x全局化.我認(rèn)為最好修改某些東西,使它們最適合您的應(yīng)用程序.像錯(cuò)誤處理/消息變量名稱等

此代碼唯一不能保護(hù)您的是查詢中的錯(cuò)誤.

編輯—-我發(fā)現(xiàn)這個(gè)代碼沒有防止的東西,插入NULL值.我更改了此代碼以防止插入NULL值,它們將作為類型字符串插入.數(shù)據(jù)庫(kù)將看到其NULL并將其插入為NULL值.

只是不要嘗試插入對(duì)象或空值,因?yàn)檫@無(wú)論如何都是無(wú)用的.