MySQLi foreach bind_param()

Question

我从表单中发送一个值数组。我想循环遍历数据库表，查找这些I。当我收到这条消息时，我明白有些地方不对劲.

致命错误:在第56行/home/d 15155/tool/pdf.php中调用非对象上的成员函数bind_param()

if (count($_POST['q']) == 0){
}
else {
  foreach($_POST['q'] as $quality){
    # Prepare statement
    $stmt = $mysqli->prepare("SELECT the_question, the_sub_questions, the_quality, the_time FROM my_questions WHERE the_category='2' AND the_headline='5' AND quality_id = ? ORDER BY the_sort_order ASC");
    $stmt->bind_param('i', $quality);
    $stmt->execute(); 
    $stmt->store_result();
    $stmt->bind_result($the_question, $the_sub_questions, $the_quality, $the_time); 
    $stmt->fetch();
    $konkretaexempel .= utf8_encode($the_question) . " <br />";
  }
}

我想将结果添加到一个长字符串中(然后在PDF中使用)。

编辑

移除foreach和数组，它仍然得到相同的错误消息。我已经检查过了，数据库连接也没问题。

    if (count($_POST['q']) == 0){

    }
    else {

$stmt = $mysqli->prepare("SELECT the_question, the_sub_questions, the_quality, the_time FROM my_questions WHERE the_category='2' AND the_headline='5' AND quality_id = ? ORDER BY the_sort_order ASC");

        $stmt->bind_param('i', '27');
        $stmt->execute(); 
        $stmt->bind_result($the_question, $the_sub_questions, $the_quality, $the_time); 
        $stmt->fetch();
        $konkretaexempel .= utf8_encode($the_question) . " <br />";

        }

Answer 1

肖恩在注释中的提示可能不仅仅是一个附带说明；它可以解决这个问题:每个连接只能有一个活动查询/语句，而在单个->fetch()之后，语句仍然是活动的(一个while循环可以修复这个问题，但这里不需要这样做)。当您按照建议重用$stmt实例时，任何旧的结果集都将被丢弃。

您的脚本当前类似于

<?php
$mysqli = setup();

if (count($_POST['q']) == 0){
    myErrorHandling();
}
else {
    foreach($_POST['q'] as $quality){       
        $stmt = $mysqli->prepare("SELECT x, y FROM soFoo WHERE id = ?");
        if ( !$stmt ) { die('prepare failed'); }
        $stmt->bind_param('i', $quality);
        $stmt->execute(); 
        $stmt->bind_result($x, $y); 
        $stmt->fetch();
        printf("x=%d,y=%s\r\n", $x, $y);
    }
}


function setup() {
    // for demonstration purposes only
    $_POST = [ 'q'=> [
        1,3,5
    ]];

    mysqli_report(MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');


    $mysqli->query('
        CREATE TEMPORARY TABLE soFoo (
            id int auto_increment,
            x int,
            y varchar(32),
            primary key(id)
        )
    ');

    $stmt = $mysqli->prepare('INSERT INTO soFoo (x,y) VALUES (?,?)');
    $stmt->bind_param('ss', $x, $y);
    foreach( range('a','z') as $x=>$y ) {
        $stmt->execute();
    }
    return $mysqli;
}

输出是

x=0,y=a
prepare failed

现在，当我将准备/bind_param的调用移到循环之前时

<?php
$mysqli = setup();

if (count($_POST['q']) == 0){
    myErrorHandling();
}
else {
    $stmt = $mysqli->prepare("SELECT x, y FROM soFoo WHERE id = ?");
    if ( !$stmt ) { die('prepare failed'); }
    $stmt->bind_param('i', $quality);
    foreach($_POST['q'] as $quality){       
        $stmt->execute(); 
        $stmt->bind_result($x, $y); 
        $stmt->fetch();
        printf("x=%d,y=%s\r\n", $x, $y);
    }
}


function setup() {
  ... same as before...
}

输出是

x=0,y=a
x=2,y=c
x=4,y=e

如预期的那样。