如何使用mysqli准备的语句绑定N个参数? [英] How to bind N number of parameters using mysqli prepared statements?
问题描述
在旧的mysql代码中,我下面有一个查询,该查询在以下情况下运行良好:
In old mysql code, I had a query below which worked perfectly which is below:
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType,
FROM Answer an
INNER JOIN Question q ON q.AnswerId = an.AnswerId
JOIN Reply r ON q.ReplyId = r.ReplyId
JOIN Option_Table o ON q.OptionId = o.OptionId
WHERE ";
foreach ($terms as $each) {
$i++;
if ($i == 1){
$questionquery .= "q.QuestionContent LIKE `%$each%` ";
} else {
$questionquery .= "OR q.QuestionContent LIKE `%$each%` ";
}
}
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {
$i++;
if ($i != 1)
$questionquery .= "+";
$questionquery .= "IF(q.QuestionContent LIKE `%$each%` ,1,0)";
}
$questionquery .= " DESC ";
但是,由于那个旧的mysql正在消失,人们说要使用PDO或mysqli(由于我目前拥有的php版本而不能使用PDO),所以我尝试将代码更改为mysqli,但这给了我问题.在下面的代码中,我省略了bind_params命令,我的问题是如何在下面的查询中绑定参数?它必须能够绑定多个$each
,因为用户可以键入多个术语,并且每个$each
都被归类为一个术语.
But since that old mysql is fading away that people are saying to use PDO or mysqli (Can't use PDO because of version of php I have currently got), I tried changing my code to mysqli, but this is giving me problems. In the code below I have left out the bind_params command, my question is that how do I bind the parameters in the query below? It needs to be able to bind multiple $each
because the user is able to type in multiple terms, and each $each
is classed as a term.
以下是同一查询上的当前mysqli代码:
Below is current mysqli code on the same query:
$questioncontent = (isset($_GET['questioncontent'])) ? $_GET['questioncontent'] : '';
$searchquestion = $questioncontent;
$terms = explode(" ", $searchquestion);
$questionquery = "
SELECT q.QuestionId, q.QuestionContent, o.OptionType, an.Answer, r.ReplyType,
FROM Answer an
INNER JOIN Question q ON q.AnswerId = an.AnswerId
JOIN Reply r ON q.ReplyId = r.ReplyId
JOIN Option_Table o ON q.OptionId = o.OptionId
WHERE ";
foreach ($terms as $each) {
$i++;
if ($i == 1){
$questionquery .= "q.QuestionContent LIKE ? ";
} else {
$questionquery .= "OR q.QuestionContent LIKE ? ";
}
}
$questionquery .= "GROUP BY q.QuestionId, q.SessionId ORDER BY "; $i = 0; foreach ($terms as $each) {
$i++;
if ($i != 1)
$questionquery .= "+";
$questionquery .= "IF(q.QuestionContent LIKE ? ,1,0)";
}
$questionquery .= " DESC ";
$stmt=$mysqli->prepare($questionquery);
$stmt->execute();
$stmt->bind_result($dbQuestionId,$dbQuestionContent,$dbOptionType,$dbAnswer,$dbReplyType);
$questionnum = $stmt->num_rows();
推荐答案
看看这个 call_user_func_array 与
Take a look at this SO Post that talks about the use of call_user_func_array with bind_param()
.
从有关mysqli_stmt_bind_param的PHP文档说以下...
注意:
与mysqli_stmt_bind_param()一起使用时必须小心 与call_user_func_array()一起使用.注意mysqli_stmt_bind_param() 要求参数通过引用传递,而 call_user_func_array()可以接受变量列表作为参数 可以代表引用或值.
Care must be taken when using mysqli_stmt_bind_param() in conjunction with call_user_func_array(). Note that mysqli_stmt_bind_param() requires parameters to be passed by reference, whereas call_user_func_array() can accept as a parameter a list of variables that can represent references or values.
您将要使用类似的东西
call_user_func_array(array($stmt, 'bind_param'), $terms);
,由您决定是否在SQL字符串$stmt
中显示正确数量的?
个字符.
and it's up to you to ensure that the correct number of ?
characters appear in your SQL string $stmt
.
这是一个可行的例子
// user entered search strings
$user_terms = array("a", "b", "c");
// append your wildcard "%" to all elements. you must use "&" reference on &$value
foreach ($user_terms as &$value) {
$value = '%'.$value.'%';
}
$types = "";
for($i = 0; $i<sizeof($user_terms); $i++) {
$types .= "s";
}
$terms = array_merge( array($types), $user_terms);
// the array $terms now contains: { "sss", "%a%", "%b%", "%c%" }
$sql = "SELECT ... ?,?,?" // edit your sql here
$stmt = $mysqli->prepare($sql)
call_user_func_array(array($stmt, 'bind_param'), $terms);
这篇关于如何使用mysqli准备的语句绑定N个参数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!