创建动态 WHERE 子句时出错

getting errors when creating a dynamic where clause

提问人:user1881090 提问时间:1/30/2013 最后编辑:user1881090 更新时间:1/30/2013 访问量:251

问:

尝试使用 mysqli 创建动态 where 子句时,我遇到了很多错误:

警告:mysqli_stmt::bind_param():类型中的元素数 定义字符串与 ...上 318号线

警告:mysqli_stmt::execute(): (HY000/2031):未提供 预处理语句中的参数...线路 327

警告:mysqli_stmt:bind_result():(HY000/2031):未提供数据 对于预处理语句中的参数...在线 330

警告:mysqli_stmt::store_result():(HY000/2014):命令 同步;您现在无法在...在 331 行

我猜需要做一些更改来解决问题,但是发生的情况是,如果两个下拉菜单中的一个不相等,或者如果两个都不相等,那么它就会出现错误。AllAll

下面是代码,显示下拉菜单和查询(带有动态 where 子句),具体取决于所选的 n 个选项:

   function ShowAssessment()
{   

$studentactive = 1;

$currentstudentqry = "
SELECT
st.StudentId, st.StudentAlias, st.StudentForename, st.StudentSurname
FROM
Student_Session ss 
INNER JOIN
Student st ON ss.StudentId = st.StudentId
WHERE
(ss.SessionId = ? and st.Active = ?)
ORDER BY st.StudentAlias
";

global $mysqli;
$currentstudentstmt=$mysqli->prepare($currentstudentqry);
// You only need to call bind_param once
$currentstudentstmt->bind_param("ii",$_POST["session"], $studentactive);
// get result and assign variables (prefix with db)
$currentstudentstmt->execute(); 
$currentstudentstmt->bind_result($dbStudentId,$dbStudentAlias,$dbStudentForename,$dbStudentSurname);
$currentstudentstmt->store_result();
$studentnum = $currentstudentstmt->num_rows();       

if($studentnum == 0){ ?>

<div class="red">
There are no Students who have currently taken this Assessment
</div>
<?php } else { 

$questionsqry = "
SELECT
QuestionId, QuestionNo
FROM
Question
WHERE
(SessionId = ?)
ORDER BY QuestionNo
";

global $mysqli;
$questionsstmt=$mysqli->prepare($questionsqry);
// You only need to call bind_param once
$questionsstmt->bind_param("i",$_POST["session"]);
// get result and assign variables (prefix with db)
$questionsstmt->execute(); 
$questionsstmt->bind_result($dbQuestionId,$dbQuestionNo);
$questionsstmt->store_result();
$studentnum = $questionsstmt->num_rows();      

        ?>

<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">         
<p>
 <input type="hidden" name="module" value="<?php echo $_POST['module']; ?>">
<input type="hidden" name="session" value="<?php echo $_POST['session']; ?>">
<strong>Student:</strong>
<select name="student" id="studentsDrop">
<option value="All">All</option>
<?php
while ( $currentstudentstmt->fetch() ) {
$stu = $dbStudentId;
if(isset($_POST["student"]) && $stu == $_POST["student"]) 
    echo "<option selected='selected' value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL;
else
    echo "<option value='$stu'>" . $dbStudentAlias . " - " . $dbStudentForename . " " . $dbStudentSurname . "</option>" . PHP_EOL;
}
?>
</select>
</p>

<p>
<strong>Question:</strong>
<select name="question" id="questionsDrop">
<option value="All">All</option>
<?php
while ( $questionsstmt->fetch() ) {
$ques = $dbQuestionId;
if(isset($_POST["question"]) && $ques == $_POST["question"]) 
    echo "<option selected='selected' value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL;
else
    echo "<option value='$ques'>" . $dbQuestionNo . "</option>" . PHP_EOL;
}
?>
</select>
</p>

<input id="answerSubmit" type="submit" value="Get Student's Answers" name="answerSubmit" />
</form>

<?php
}
}

function StudentAnswersIsSubmitted()
{

if(!isset($_POST["answerSubmit"]))
    {
        return false;
    }
    else // All is ok
    {
        return true;
    }
    return false;

}

function StudentAnswers()
{

$selectedstudentanswerqry = "
SELECT
StudentAlias, StudentForename, StudentSurname, q.SessionId, QuestionNo, QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT( DISTINCT Answer
ORDER BY Answer SEPARATOR ',' ) AS Answer, r.ReplyType, QuestionMarks, 
GROUP_CONCAT(DISTINCT StudentAnswer ORDER BY StudentAnswer SEPARATOR ',') AS StudentAnswer, ResponseTime, MouseClick, StudentMark
FROM Student s
INNER JOIN Student_Answer sa ON (s.StudentId = sa.StudentId)
INNER JOIN Student_Response sr ON (sa.StudentId = sr.StudentId)
INNER JOIN Question q ON (sa.QuestionId = q.QuestionId)
INNER JOIN Answer an ON q.QuestionId = an.QuestionId
LEFT JOIN Reply r ON q.ReplyId = r.ReplyId
LEFT JOIN Option_Table o ON q.OptionId = o.OptionId
";

// Initially empty
$where = array('q.SessionId = ?');
$parameters = array($_POST["session"]);
$parameterTypes = 'i';

// Check whether a specific student was selected
if($_POST["student"] !== 'All') {
    $where[] = 'sa.StudentId = ?';
    $parameters[] = $_POST["student"];
    $parameterTypes .= 'i';
}

// Check whether a specific question was selected
// NB: This is not an else if!
if($_POST["question"] !== 'All') {
    $where[] = 'q.QuestionId = ?';
    $parameters[] = $_POST["question"];
    $parameterTypes .= 'i';
}

// If we added to $where in any of the conditionals, we need a WHERE clause in
// our query
if(!empty($where)) {
    $selectedstudentanswerqry .= ' WHERE ' . implode(' AND ', $where);
    global $mysqli;
    $selectedstudentanswerstmt=$mysqli->prepare($selectedstudentanswerqry);
    // You only need to call bind_param once
    $selectedstudentanswerstmt->bind_param($parameterTypes,implode($parameters));  //LINE 318
}

$selectedstudentanswerqry .= "
  GROUP BY sa.StudentId, q.QuestionId
  ORDER BY StudentAlias, q.SessionId, QuestionNo
";

// get result and assign variables (prefix with db)
$selectedstudentanswerstmt->execute(); //LINE 327
$selectedstudentanswerstmt->bind_result($detailsStudentAlias,$detailsStudentForename,$detailsStudentSurname,$detailsSessionId,$detailsQuestionNo, 
$detailsQuestonContent,$detailsOptionType,$detailsNoofAnswers,$detailsAnswer,$detailsReplyType,$detailsQuestionMarks,$detailsStudentAnswer,$detailsResponseTime,
$detailsMouseClick,$detailsStudentMark); //LINE 330
$selectedstudentanswerstmt->store_result(); //LINE 331
$selectedstudentanswernum = $selectedstudentanswerstmt->num_rows();     

echo "$selectedstudentanswerqry";

}

?>
PHP MySQLI

评论

0赞 andho 1/30/2013
检查要传递给bind_params的数组的输出值:var_dump(array_merge(array($parameterTypes), $parameters)));
0赞 user1881090 1/30/2013
@andho 当我选择会话(评估)时,var 转储的结果 with value , student number value , and question number value , AND WHERE CLAUSE ,我得到以下输出:314081WHERE q.SessionId = ? AND sa.StudentId = ? AND q.QuestionId = ?array(4) { [0]=> string(3) "iii" [1]=> string(2) "31" [2]=> string(2) "40" [3]=> string(2) "81" }
0赞 user1881090 1/30/2013
@andho你对此有什么想法,因为我正在挣扎
0赞 andho 1/31/2013
不知道。您应该做的是隔离违规代码并尝试对其进行测试。
0赞 andho 1/31/2013
从你能做的最简单的事情开始,然后开始建立你目前拥有的东西,在每一步都进行测试。我要做的是,静态地编写准备好的语句,并手动发送bind_params作为第一步。第二步:尝试使bind_params自动...等等。

答:

-2赞 Josef Kufner 1/30/2013 #1

看看 fluentPDO、Dibi fluent 或一些类似的查询构建器。这将使您免于很多痛苦,并且他们已经解决了您要做的事情。

无论如何,而不是:

$selectedstudentanswerstmt->bind_param($parameterTypes,implode($parameters));

用:

call_user_func_array(array($selectedstudentanswerstmt, 'bind_param'),
    array_merge(array($parameterTypes), $parameters))

评论

0赞 user1881090 1/30/2013
当我回声时,我的查询工作正常,它只是给了我错误,这意味着我只是在实践中做了一些小错误
0赞 Josef Kufner 1/30/2013
在将参数绑定到查询时遇到问题。'?' 与bind_param调用不匹配。问题出在那次内爆上。您必须使用 call_user_func_array(array($selectedstudentanswerstmt, 'bind_param'), array_merge(array($parameterTypes), $parameters))。
0赞 user1881090 1/30/2013
你能改变你的答案以包括 以便我知道它是如何正确实现的吗call_user_func_array(...
0赞 user1881090 1/30/2013
我也包括了有问题的演示
0赞 user1881090 1/30/2013
请查看更新,尝试答案时仍然出错